-- Invoice Management System Database Schema

-- Tax Brackets (different tax rates for different purposes)
CREATE TABLE IF NOT EXISTS tax_brackets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    rate DECIMAL(5,4) NOT NULL, -- e.g., 0.0875 for 8.75%
    description TEXT,
    is_compound BOOLEAN DEFAULT FALSE, -- For compound tax (tax on tax)
    is_active BOOLEAN DEFAULT TRUE,
    jurisdiction VARCHAR(100), -- e.g., "State", "Federal", "Local"
    tax_id VARCHAR(50), -- Tax ID number for this bracket
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Invoice Items (products/services that can be billed)
CREATE TABLE IF NOT EXISTS invoice_items (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    unit_price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    sku VARCHAR(100) UNIQUE,
    category VARCHAR(100),
    is_taxable BOOLEAN DEFAULT TRUE,
    default_tax_bracket_id INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (default_tax_bracket_id) REFERENCES tax_brackets(id) ON DELETE SET NULL
);

-- Invoice Settings (company-wide settings)
CREATE TABLE IF NOT EXISTS invoice_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_name VARCHAR(255) NOT NULL,
    company_legal_name VARCHAR(255),
    tax_id VARCHAR(50), -- Company Tax ID
    license_id VARCHAR(50), -- Business License ID
    address TEXT,
    phone VARCHAR(50),
    email VARCHAR(255),
    website VARCHAR(255),
    company_logo_path VARCHAR(500),
    currency VARCHAR(3) DEFAULT 'USD',
    tax_inclusive BOOLEAN DEFAULT FALSE, -- Whether prices include tax by default
    default_payment_terms VARCHAR(100) DEFAULT 'Net 30',
    bank_name VARCHAR(255),
    bank_account_name VARCHAR(255),
    bank_account_number VARCHAR(50),
    bank_routing_number VARCHAR(50),
    bank_swift VARCHAR(50),
    payment_instructions TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Invoices
CREATE TABLE IF NOT EXISTS invoices (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    invoice_number VARCHAR(50) NOT NULL UNIQUE,
    client_id INT UNSIGNED NOT NULL,
    proposal_id INT UNSIGNED NULL, -- Optional link to proposal
    contract_id INT UNSIGNED NULL, -- Optional link to contract
    invoice_date DATE NOT NULL,
    due_date DATE NOT NULL,
    status ENUM('draft', 'sent', 'paid', 'overdue', 'cancelled') DEFAULT 'draft',
    subtotal DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    tax_total DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    total DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    paid_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    currency VARCHAR(3) DEFAULT 'USD',
    payment_terms VARCHAR(100),
    notes TEXT,
    company_name VARCHAR(255), -- Override for this invoice
    company_address TEXT, -- Override for this invoice
    tax_exempt BOOLEAN DEFAULT FALSE,
    tax_exemption_reason VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE RESTRICT
    -- Note: proposal_id and contract_id foreign keys will be added when those tables are created
);

-- Invoice Line Items
CREATE TABLE IF NOT EXISTS invoice_items_line (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    invoice_id INT UNSIGNED NOT NULL,
    item_id INT UNSIGNED NULL, -- Can be null for custom items
    description VARCHAR(255) NOT NULL,
    quantity DECIMAL(10,2) NOT NULL DEFAULT 1.00,
    unit_price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    discount_percent DECIMAL(5,2) DEFAULT 0.00,
    tax_bracket_id INT NULL, -- Override default tax
    line_total DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    tax_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    sort_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE 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 INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    invoice_id INT UNSIGNED NOT NULL,
    tax_bracket_id INT NOT NULL,
    taxable_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    tax_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    is_compound BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE,
    FOREIGN KEY (tax_bracket_id) REFERENCES tax_brackets(id) ON DELETE RESTRICT
);

-- Invoice Payments
CREATE TABLE IF NOT EXISTS invoice_payments (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    invoice_id INT UNSIGNED NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    payment_date DATE NOT NULL,
    payment_method VARCHAR(50), -- e.g., 'cash', 'check', 'bank_transfer', 'credit_card'
    reference_number VARCHAR(100),
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE
);

-- Insert default tax brackets
INSERT INTO tax_brackets (name, rate, description, jurisdiction) VALUES
('No Tax', 0.0000, 'No tax applied', 'None'),
('Standard Sales Tax', 0.0875, 'Standard state sales tax', 'State'),
('Reduced Rate', 0.0500, 'Reduced rate for essential items', 'State'),
('Zero Rated', 0.0000, 'Zero-rated items (tax exempt but claimable)', 'Federal')
ON DUPLICATE KEY UPDATE rate = VALUES(rate);

-- Insert default invoice settings
INSERT INTO invoice_settings (company_name, currency) VALUES
('Your Company', 'USD')
ON DUPLICATE KEY UPDATE company_name = VALUES(company_name);

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