-- Invoice System Database Schema for PixelVise

-- Drop existing tables if they exist
DROP TABLE IF EXISTS invoice_taxes;
DROP TABLE IF EXISTS invoice_payments;
DROP TABLE IF EXISTS invoice_items_line;
DROP TABLE IF EXISTS invoices;
DROP TABLE IF EXISTS invoice_items;
DROP TABLE IF EXISTS tax_brackets;
DROP TABLE IF EXISTS invoice_settings;

-- Invoice Settings (company-wide settings)
CREATE TABLE IF NOT EXISTS invoice_settings (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    company_name TEXT NOT NULL,
    company_legal_name TEXT,
    tax_id TEXT,
    tax_name TEXT, -- GSTIN, VAT, etc.
    company_id TEXT,
    custom_meta TEXT, -- JSON field for custom meta data
    company_logo_path TEXT,
    address_line_1 TEXT,
    address_line_2 TEXT,
    city TEXT,
    state TEXT,
    zip_code TEXT,
    country TEXT,
    bank_name TEXT,
    bank_account_name TEXT,
    bank_account_number TEXT,
    bank_routing_number TEXT,
    bank_swift TEXT,
    bank_details TEXT, -- Additional bank details
    currency TEXT DEFAULT 'USD',
    tax_inclusive INTEGER DEFAULT 0,
    default_payment_terms TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Tax Brackets
CREATE TABLE IF NOT EXISTS tax_brackets (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    rate REAL NOT NULL, -- Stored as decimal (e.g., 0.0875 for 8.75%)
    description TEXT,
    is_compound INTEGER DEFAULT 0,
    is_active INTEGER DEFAULT 1,
    jurisdiction TEXT,
    tax_id TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Invoice Items (products/services that can be billed)
CREATE TABLE IF NOT EXISTS invoice_items (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    description TEXT,
    unit_price REAL NOT NULL DEFAULT 0.00,
    sku TEXT UNIQUE,
    is_taxable INTEGER DEFAULT 1,
    default_tax_bracket_id INTEGER,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (default_tax_bracket_id) REFERENCES tax_brackets(id) ON DELETE SET NULL
);

-- Invoices
CREATE TABLE IF NOT EXISTS invoices (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    invoice_number TEXT NOT NULL UNIQUE,
    client_id INTEGER NOT NULL,
    proposal_id INTEGER, -- Optional link to proposal
    contract_id INTEGER, -- Optional link to contract
    invoice_date TEXT NOT NULL,
    due_date TEXT NOT NULL,
    status TEXT DEFAULT 'draft' CHECK(status IN ('draft', 'sent', 'paid', 'overdue', 'cancelled')),
    subtotal REAL NOT NULL DEFAULT 0.00,
    tax_total REAL NOT NULL DEFAULT 0.00,
    total REAL NOT NULL DEFAULT 0.00,
    paid_amount REAL NOT NULL DEFAULT 0.00,
    currency TEXT DEFAULT 'USD',
    payment_terms TEXT,
    notes TEXT,
    company_name TEXT, -- Override for this invoice
    company_address TEXT, -- Override for this invoice
    tax_exempt INTEGER DEFAULT 0,
    tax_exemption_reason TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE RESTRICT
);

-- Invoice Line Items
CREATE TABLE IF NOT EXISTS invoice_items_line (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    invoice_id INTEGER NOT NULL,
    item_id INTEGER, -- Can be null for custom items
    description TEXT NOT NULL,
    quantity REAL NOT NULL DEFAULT 1.00,
    unit_price REAL NOT NULL DEFAULT 0.00,
    discount_percent REAL DEFAULT 0.00,
    tax_bracket_id INTEGER, -- Override default tax
    line_total REAL NOT NULL DEFAULT 0.00,
    tax_amount REAL NOT NULL DEFAULT 0.00,
    sort_order INTEGER DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE,
    FOREIGN KEY (item_id) REFERENCES invoice_items(id) ON DELETE SET NULL,
    FOREIGN KEY (tax_bracket_id) REFERENCES tax_brackets(id) ON DELETE SET NULL
);

-- Invoice Tax Calculations (for tracking tax per bracket)
CREATE TABLE IF NOT EXISTS invoice_taxes (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    invoice_id INTEGER NOT NULL,
    tax_bracket_id INTEGER NOT NULL,
    taxable_amount REAL NOT NULL DEFAULT 0.00,
    tax_amount REAL NOT NULL DEFAULT 0.00,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE,
    FOREIGN KEY (tax_bracket_id) REFERENCES tax_brackets(id) ON DELETE CASCADE
);

-- Invoice Payments
CREATE TABLE IF NOT EXISTS invoice_payments (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    invoice_id INTEGER NOT NULL,
    amount REAL NOT NULL,
    payment_date TEXT NOT NULL,
    payment_method TEXT,
    reference_number TEXT,
    notes TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE
);

-- Insert default invoice settings
INSERT OR IGNORE INTO invoice_settings (id, company_name, currency, default_payment_terms) VALUES
(1, 'Your Company Name', 'USD', 'Due on Receipt');

-- Create indexes for better performance
CREATE INDEX IF NOT EXISTS idx_invoices_client_id ON invoices(client_id);
CREATE INDEX IF NOT EXISTS idx_invoices_status ON invoices(status);
CREATE INDEX IF NOT EXISTS idx_invoices_invoice_date ON invoices(invoice_date);
CREATE INDEX IF NOT EXISTS idx_invoice_items_line_invoice_id ON invoice_items_line(invoice_id);
CREATE INDEX IF NOT EXISTS idx_invoice_items_line_item_id ON invoice_items_line(item_id);
CREATE INDEX IF NOT EXISTS idx_invoice_payments_invoice_id ON invoice_payments(invoice_id);
CREATE INDEX IF NOT EXISTS idx_invoice_taxes_invoice_id ON invoice_taxes(invoice_id);
