-- Financials Plugin Database Schema
-- This schema is isolated in the plugin_financials namespace

-- Chart of Accounts
CREATE TABLE IF NOT EXISTS plugin_financials.chart_of_accounts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    site_id UUID NOT NULL,
    account_code VARCHAR(20) NOT NULL,
    account_name VARCHAR(255) NOT NULL,
    account_type VARCHAR(50) NOT NULL, -- asset, liability, equity, revenue, expense
    parent_account_id UUID REFERENCES plugin_financials.chart_of_accounts(id),
    description TEXT,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by UUID NOT NULL,
    UNIQUE(site_id, account_code)
);

-- Budgets
CREATE TABLE IF NOT EXISTS plugin_financials.budgets (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    site_id UUID NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    fiscal_year INTEGER NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    total_amount DECIMAL(15,2) NOT NULL,
    status VARCHAR(20) DEFAULT 'draft', -- draft, active, closed
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by UUID NOT NULL
);

-- Budget Items
CREATE TABLE IF NOT EXISTS plugin_financials.budget_items (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    budget_id UUID NOT NULL REFERENCES plugin_financials.budgets(id) ON DELETE CASCADE,
    account_id UUID NOT NULL REFERENCES plugin_financials.chart_of_accounts(id),
    planned_amount DECIMAL(15,2) NOT NULL,
    actual_amount DECIMAL(15,2) DEFAULT 0,
    notes TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Transactions
CREATE TABLE IF NOT EXISTS plugin_financials.transactions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    site_id UUID NOT NULL,
    transaction_date DATE NOT NULL,
    reference_number VARCHAR(50),
    description TEXT NOT NULL,
    transaction_type VARCHAR(20) NOT NULL, -- income, expense, transfer, opening_balance, adjustment
    amount DECIMAL(15,2) NOT NULL,
    currency VARCHAR(3) DEFAULT 'USD',
    status VARCHAR(20) DEFAULT 'pending', -- pending, approved, rejected, posted
    category VARCHAR(100),
    vendor_id UUID,
    unit_id UUID,
    payment_method VARCHAR(50),
    notes TEXT,
    attachments JSONB,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by UUID NOT NULL,
    approved_by UUID,
    approved_at TIMESTAMP WITH TIME ZONE
);

-- Transaction Lines (Double-entry accounting)
CREATE TABLE IF NOT EXISTS plugin_financials.transaction_lines (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    transaction_id UUID NOT NULL REFERENCES plugin_financials.transactions(id) ON DELETE CASCADE,
    account_id UUID NOT NULL REFERENCES plugin_financials.chart_of_accounts(id),
    debit_amount DECIMAL(15,2) DEFAULT 0,
    credit_amount DECIMAL(15,2) DEFAULT 0,
    description TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Expenses
CREATE TABLE IF NOT EXISTS plugin_financials.expenses (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    site_id UUID NOT NULL,
    expense_date DATE NOT NULL,
    vendor_name VARCHAR(255),
    vendor_id UUID,
    category VARCHAR(100) NOT NULL,
    description TEXT NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    tax_amount DECIMAL(15,2) DEFAULT 0,
    total_amount DECIMAL(15,2) NOT NULL,
    payment_status VARCHAR(20) DEFAULT 'pending', -- pending, paid, overdue
    payment_method VARCHAR(50),
    receipt_url VARCHAR(500),
    approved_by UUID,
    approved_at TIMESTAMP WITH TIME ZONE,
    notes TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by UUID NOT NULL
);

-- Revenue/Income
CREATE TABLE IF NOT EXISTS plugin_financials.revenue (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    site_id UUID NOT NULL,
    revenue_date DATE NOT NULL,
    source VARCHAR(100) NOT NULL, -- maintenance_fees, late_fees, special_assessments, etc.
    description TEXT NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    unit_id UUID,
    resident_id UUID,
    payment_status VARCHAR(20) DEFAULT 'pending', -- pending, received, overdue
    payment_method VARCHAR(50),
    receipt_url VARCHAR(500),
    notes TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by UUID NOT NULL
);

-- Unit Balance Tracking
CREATE TABLE IF NOT EXISTS plugin_financials.unit_balances (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    unit_id UUID NOT NULL,
    site_id UUID NOT NULL,
    current_balance DECIMAL(15,2) DEFAULT 0, -- Current total balance (calculated from transactions)
    last_updated TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by UUID NOT NULL,
    UNIQUE(unit_id, site_id)
);

-- Invoices (for monthly fees, services, work orders, etc.)
CREATE TABLE IF NOT EXISTS plugin_financials.invoices (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    site_id UUID NOT NULL,
    unit_id UUID NOT NULL,
    invoice_number VARCHAR(50) NOT NULL,
    invoice_type VARCHAR(50) NOT NULL, -- monthly_fee, service, work_order, other
    description TEXT NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    due_date DATE NOT NULL,
    issue_date DATE NOT NULL DEFAULT CURRENT_DATE,
    status VARCHAR(20) DEFAULT 'pending', -- pending, paid, overdue, cancelled
    work_order_id UUID, -- Reference to work order if applicable
    payment_date DATE,
    payment_method VARCHAR(50),
    payment_reference VARCHAR(100),
    notes TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by UUID NOT NULL,
    UNIQUE(site_id, invoice_number)
);

-- Unit Monthly Fee Settings
CREATE TABLE IF NOT EXISTS plugin_financials.unit_monthly_fees (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    unit_id UUID NOT NULL,
    site_id UUID NOT NULL,
    monthly_fee_amount DECIMAL(15,2) NOT NULL,
    effective_from DATE NOT NULL DEFAULT CURRENT_DATE,
    effective_to DATE, -- NULL means currently active
    fee_description TEXT DEFAULT 'Monthly maintenance fee',
    auto_generate_invoice BOOLEAN DEFAULT true,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by UUID NOT NULL,
    UNIQUE(unit_id, effective_from)
);

-- Payments (tracks all payments made against invoices)
CREATE TABLE IF NOT EXISTS plugin_financials.payments (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    site_id UUID NOT NULL,
    unit_id UUID NOT NULL,
    invoice_id UUID REFERENCES plugin_financials.invoices(id),
    payment_amount DECIMAL(15,2) NOT NULL,
    payment_date DATE NOT NULL DEFAULT CURRENT_DATE,
    payment_method VARCHAR(50) NOT NULL, -- cash, check, bank_transfer, credit_card, etc.
    payment_reference VARCHAR(100), -- check number, transaction ID, etc.
    notes TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by UUID NOT NULL
);

-- Balance History (audit trail for balance changes)
CREATE TABLE IF NOT EXISTS plugin_financials.balance_history (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    unit_id UUID NOT NULL,
    site_id UUID NOT NULL,
    change_type VARCHAR(50) NOT NULL, -- invoice_added, payment_made, adjustment, starting_balance
    change_amount DECIMAL(15,2) NOT NULL, -- positive for charges, negative for payments
    balance_before DECIMAL(15,2) NOT NULL,
    balance_after DECIMAL(15,2) NOT NULL,
    reference_id UUID, -- invoice_id, payment_id, etc.
    reference_type VARCHAR(50), -- invoice, payment, adjustment
    description TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by UUID NOT NULL
);

-- Indexes for performance
CREATE INDEX IF NOT EXISTS idx_unit_balances_unit_id ON plugin_financials.unit_balances(unit_id);
CREATE INDEX IF NOT EXISTS idx_invoices_unit_id ON plugin_financials.invoices(unit_id);
CREATE INDEX IF NOT EXISTS idx_invoices_status ON plugin_financials.invoices(status);
CREATE INDEX IF NOT EXISTS idx_invoices_due_date ON plugin_financials.invoices(due_date);
CREATE INDEX IF NOT EXISTS idx_payments_unit_id ON plugin_financials.payments(unit_id);
CREATE INDEX IF NOT EXISTS idx_payments_invoice_id ON plugin_financials.payments(invoice_id);
CREATE INDEX IF NOT EXISTS idx_balance_history_unit_id ON plugin_financials.balance_history(unit_id);

-- Financial Reports
CREATE TABLE IF NOT EXISTS plugin_financials.reports (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    site_id UUID NOT NULL,
    report_type VARCHAR(50) NOT NULL, -- income_statement, balance_sheet, cash_flow, budget_variance
    report_name VARCHAR(255) NOT NULL,
    report_date DATE NOT NULL,
    parameters JSONB,
    generated_by UUID NOT NULL,
    generated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    file_url VARCHAR(500),
    status VARCHAR(20) DEFAULT 'generated' -- generated, sent, archived
);

-- Tax Settings
CREATE TABLE IF NOT EXISTS plugin_financials.tax_settings (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    site_id UUID NOT NULL,
    tax_name VARCHAR(100) NOT NULL,
    tax_rate DECIMAL(5,4) NOT NULL,
    tax_type VARCHAR(20) NOT NULL, -- percentage, fixed
    is_active BOOLEAN DEFAULT true,
    applies_to VARCHAR(100), -- all, expenses, specific_categories
    effective_date DATE NOT NULL,
    end_date DATE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by UUID NOT NULL
);

-- Financial Analytics
CREATE TABLE IF NOT EXISTS plugin_financials.analytics (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    site_id UUID NOT NULL,
    metric_name VARCHAR(100) NOT NULL,
    metric_value DECIMAL(15,2) NOT NULL,
    metric_date DATE NOT NULL,
    category VARCHAR(100),
    metadata JSONB,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Special Assessments
CREATE TABLE IF NOT EXISTS plugin_financials.special_assessments (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    site_id UUID NOT NULL,
    assessment_name VARCHAR(255) NOT NULL,
    description TEXT NOT NULL,
    total_amount DECIMAL(15,2) NOT NULL,
    amount_per_unit DECIMAL(15,2) NOT NULL,
    due_date DATE NOT NULL,
    unit_count INTEGER NOT NULL,
    status VARCHAR(20) DEFAULT 'pending', -- pending, invoiced, collected, cancelled
    project_type VARCHAR(100), -- renovation, repair, upgrade, emergency
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by UUID NOT NULL
);

-- Bank Accounts
CREATE TABLE IF NOT EXISTS plugin_financials.bank_accounts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    site_id UUID NOT NULL,
    account_name VARCHAR(255) NOT NULL,
    bank_name VARCHAR(255) NOT NULL,
    account_number VARCHAR(100),
    routing_number VARCHAR(50),
    account_type VARCHAR(50) DEFAULT 'checking', -- checking, savings, reserve
    ledger_account_id UUID REFERENCES plugin_financials.chart_of_accounts(id),
    current_balance DECIMAL(15,2) DEFAULT 0,
    currency VARCHAR(3) DEFAULT 'USD',
    is_active BOOLEAN DEFAULT true,
    notes TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by UUID NOT NULL
);

-- Bank Statements
CREATE TABLE IF NOT EXISTS plugin_financials.bank_statements (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    bank_account_id UUID NOT NULL REFERENCES plugin_financials.bank_accounts(id) ON DELETE CASCADE,
    site_id UUID NOT NULL,
    statement_date DATE NOT NULL,
    opening_balance DECIMAL(15,2) NOT NULL,
    closing_balance DECIMAL(15,2) NOT NULL,
    total_deposits DECIMAL(15,2) DEFAULT 0,
    total_withdrawals DECIMAL(15,2) DEFAULT 0,
    statement_file_url VARCHAR(500),
    is_reconciled BOOLEAN DEFAULT false,
    reconciled_date DATE,
    reconciled_by UUID,
    notes TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by UUID NOT NULL
);

-- Bank Statement Transactions
CREATE TABLE IF NOT EXISTS plugin_financials.bank_statement_transactions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    bank_statement_id UUID NOT NULL REFERENCES plugin_financials.bank_statements(id) ON DELETE CASCADE,
    transaction_date DATE NOT NULL,
    description TEXT NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    transaction_type VARCHAR(20) NOT NULL, -- deposit, withdrawal, fee, interest
    reference_number VARCHAR(100),
    is_matched BOOLEAN DEFAULT false,
    matched_transaction_id UUID, -- Links to plugin_financials.transactions if matched
    notes TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Bank Reconciliation Records
CREATE TABLE IF NOT EXISTS plugin_financials.bank_reconciliation_records (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    site_id UUID NOT NULL,
    bank_account_id UUID NOT NULL REFERENCES plugin_financials.bank_accounts(id) ON DELETE CASCADE,
    statement_id UUID NOT NULL REFERENCES plugin_financials.bank_statements(id),
    reconciliation_date DATE NOT NULL,
    ledger_balance DECIMAL(15,2) NOT NULL,
    bank_balance DECIMAL(15,2) NOT NULL,
    outstanding_deposits DECIMAL(15,2) DEFAULT 0,
    outstanding_withdrawals DECIMAL(15,2) DEFAULT 0,
    adjustments DECIMAL(15,2) DEFAULT 0,
    reconciled_balance DECIMAL(15,2) NOT NULL,
    notes TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_by UUID NOT NULL,
    approved_by UUID,
    approved_at TIMESTAMP WITH TIME ZONE
);

-- Create indexes for better performance
CREATE INDEX IF NOT EXISTS idx_chart_of_accounts_site_id ON plugin_financials.chart_of_accounts(site_id);
CREATE INDEX IF NOT EXISTS idx_chart_of_accounts_account_type ON plugin_financials.chart_of_accounts(account_type);
CREATE INDEX IF NOT EXISTS idx_budgets_site_id ON plugin_financials.budgets(site_id);
CREATE INDEX IF NOT EXISTS idx_budgets_fiscal_year ON plugin_financials.budgets(fiscal_year);
CREATE INDEX IF NOT EXISTS idx_transactions_site_id ON plugin_financials.transactions(site_id);
CREATE INDEX IF NOT EXISTS idx_transactions_date ON plugin_financials.transactions(transaction_date);
CREATE INDEX IF NOT EXISTS idx_transactions_type ON plugin_financials.transactions(transaction_type);
CREATE INDEX IF NOT EXISTS idx_expenses_site_id ON plugin_financials.expenses(site_id);
CREATE INDEX IF NOT EXISTS idx_expenses_date ON plugin_financials.expenses(expense_date);
CREATE INDEX IF NOT EXISTS idx_revenue_site_id ON plugin_financials.revenue(site_id);
CREATE INDEX IF NOT EXISTS idx_revenue_date ON plugin_financials.revenue(revenue_date);
CREATE INDEX IF NOT EXISTS idx_reports_site_id ON plugin_financials.reports(site_id);
CREATE INDEX IF NOT EXISTS idx_analytics_site_id ON plugin_financials.analytics(site_id);
CREATE INDEX IF NOT EXISTS idx_analytics_date ON plugin_financials.analytics(metric_date);

-- Bank Reconciliation Indexes
CREATE INDEX IF NOT EXISTS idx_bank_accounts_site_id ON plugin_financials.bank_accounts(site_id);
CREATE INDEX IF NOT EXISTS idx_bank_accounts_ledger_account ON plugin_financials.bank_accounts(ledger_account_id);
CREATE INDEX IF NOT EXISTS idx_bank_statements_account ON plugin_financials.bank_statements(bank_account_id);
CREATE INDEX IF NOT EXISTS idx_bank_statements_site_id ON plugin_financials.bank_statements(site_id);
CREATE INDEX IF NOT EXISTS idx_bank_statement_transactions_statement ON plugin_financials.bank_statement_transactions(bank_statement_id);
CREATE INDEX IF NOT EXISTS idx_bank_reconciliation_account ON plugin_financials.bank_reconciliation_records(bank_account_id);
CREATE INDEX IF NOT EXISTS idx_bank_reconciliation_site_id ON plugin_financials.bank_reconciliation_records(site_id);