Cloud Setup Guide

-- Enable UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Transactions table
CREATE TABLE transactions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    date DATE NOT NULL,
    description TEXT NOT NULL,
    amount NUMERIC(15, 2) NOT NULL CHECK (amount > 0),
    type TEXT NOT NULL CHECK (type IN ('income', 'expense')),
    status TEXT NOT NULL CHECK (status IN ('--', 'pending', 'approved', 'rejected', 'planned')),
    sheet TEXT NOT NULL,
    category TEXT,
    party TEXT,
    notes TEXT,
    reference TEXT,
    activity TEXT,
    version INTEGER NOT NULL DEFAULT 1,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    modified_at TIMESTAMPTZ,
    modified_by TEXT
);

CREATE INDEX idx_transactions_date ON transactions(date DESC);
CREATE INDEX idx_transactions_sheet ON transactions(sheet);
CREATE INDEX idx_transactions_type ON transactions(type);
CREATE INDEX idx_transactions_status ON transactions(status);

-- Planned templates table
CREATE TABLE planned_templates (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    start_date DATE NOT NULL,
    description TEXT NOT NULL,
    amount NUMERIC(15, 2) NOT NULL CHECK (amount > 0),
    type TEXT NOT NULL CHECK (type IN ('income', 'expense')),
    frequency TEXT NOT NULL CHECK (frequency IN ('once', 'weekly', 'biweekly', 'monthly', 'quarterly', 'yearly')),
    target_sheet TEXT NOT NULL,
    category TEXT,
    party TEXT,
    activity TEXT,
    end_date DATE,
    occurrence_count INTEGER,
    skipped_dates JSONB DEFAULT '[]'::jsonb,
    fulfilled_dates JSONB DEFAULT '[]'::jsonb,
    version INTEGER NOT NULL DEFAULT 1,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_planned_start ON planned_templates(start_date);
CREATE INDEX idx_planned_target ON planned_templates(target_sheet);

-- Sheets table
CREATE TABLE sheets (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name TEXT UNIQUE NOT NULL,
    is_virtual BOOLEAN DEFAULT FALSE,
    is_planned BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_sheets_name ON sheets(name);

-- Attachments table (metadata only - files in Supabase Storage)
CREATE TABLE attachments (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    transaction_id UUID NOT NULL REFERENCES transactions(id) ON DELETE CASCADE,
    filename TEXT NOT NULL,
    stored_name TEXT NOT NULL,
    mime_type TEXT,
    file_size BIGINT DEFAULT 0,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_attachments_transaction ON attachments(transaction_id);

-- Audit log table
CREATE TABLE audit_log (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    timestamp TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    action TEXT NOT NULL CHECK (action IN ('create', 'update', 'delete')),
    entity_type TEXT NOT NULL,
    entity_id UUID NOT NULL,
    "user" TEXT NOT NULL,
    summary TEXT NOT NULL,
    details TEXT
);

CREATE INDEX idx_audit_timestamp ON audit_log(timestamp DESC);
CREATE INDEX idx_audit_entity ON audit_log(entity_type, entity_id);

-- Categories table
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    type TEXT NOT NULL CHECK (type IN ('income', 'expense')),
    name TEXT NOT NULL,
    sort_order INTEGER DEFAULT 0,
    UNIQUE(type, name)
);

CREATE INDEX idx_categories_type ON categories(type);

-- Row Level Security (enable for multi-user)
ALTER TABLE transactions ENABLE ROW LEVEL SECURITY;
ALTER TABLE planned_templates ENABLE ROW LEVEL SECURITY;
ALTER TABLE sheets ENABLE ROW LEVEL SECURITY;
ALTER TABLE attachments ENABLE ROW LEVEL SECURITY;
ALTER TABLE audit_log ENABLE ROW LEVEL SECURITY;
ALTER TABLE categories ENABLE ROW LEVEL SECURITY;

-- Policies: Allow all authenticated users full access
CREATE POLICY "Full access" ON transactions FOR ALL USING (true);
CREATE POLICY "Full access" ON planned_templates FOR ALL USING (true);
CREATE POLICY "Full access" ON sheets FOR ALL USING (true);
CREATE POLICY "Full access" ON attachments FOR ALL USING (true);
CREATE POLICY "Full access" ON audit_log FOR ALL USING (true);
CREATE POLICY "Full access" ON categories FOR ALL USING (true);