-- Create enum for invoice status
CREATE TYPE invoice_status AS ENUM ('DRAFT', 'SENT', 'PAID', 'PARTIALLY_PAID', 'OVERDUE', 'VOID', 'CANCELLED');

-- Create invoices table
CREATE TABLE IF NOT EXISTS invoices (
    id SERIAL PRIMARY KEY,
    invoice_number VARCHAR(50) UNIQUE NOT NULL,
    project_id INTEGER REFERENCES projects(id) ON DELETE SET NULL,
    client_id INTEGER NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    amount DECIMAL(15, 2) NOT NULL,
    currency VARCHAR(10) NOT NULL DEFAULT 'USD',
    status invoice_status NOT NULL DEFAULT 'DRAFT',
    due_date DATE,
    issued_date DATE,
    paid_date DATE,
    blockchain_tx_hash VARCHAR(255), -- For on-chain payment proof
    ipfs_document_cid VARCHAR(255), -- CID for invoice PDF on IPFS
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create indexes for invoices
CREATE INDEX idx_invoices_invoice_number ON invoices(invoice_number);
CREATE INDEX idx_invoices_project_id ON invoices(project_id);
CREATE INDEX idx_invoices_client_id ON invoices(client_id);
CREATE INDEX idx_invoices_status ON invoices(status);
CREATE INDEX idx_invoices_due_date ON invoices(due_date) WHERE due_date IS NOT NULL;
CREATE INDEX idx_invoices_blockchain_tx ON invoices(blockchain_tx_hash) WHERE blockchain_tx_hash IS NOT NULL;

-- Create enum for payment method
CREATE TYPE payment_method AS ENUM ('CREDIT_CARD', 'DEBIT_CARD', 'CRYPTO_BTC', 'CRYPTO_ETH', 'CRYPTO_OTHER', 'BLOCKCHAIN_TOKEN', 'BANK_TRANSFER', 'CASH', 'OTHER');

-- Create enum for payment status
CREATE TYPE payment_status AS ENUM ('PENDING', 'PROCESSING', 'COMPLETED', 'FAILED', 'REFUNDED', 'CANCELLED');

-- Create payments table
CREATE TABLE IF NOT EXISTS payments (
    id SERIAL PRIMARY KEY,
    invoice_id INTEGER NOT NULL REFERENCES invoices(id) ON DELETE CASCADE,
    amount DECIMAL(15, 2) NOT NULL,
    currency VARCHAR(10) NOT NULL DEFAULT 'USD',
    payment_method payment_method NOT NULL,
    status payment_status NOT NULL DEFAULT 'PENDING',
    transaction_id VARCHAR(255), -- Stripe payment intent ID, BTCPay invoice ID, or blockchain tx hash
    blockchain_tx_hash VARCHAR(255), -- For crypto/blockchain payments
    blockchain_network VARCHAR(50), -- e.g., 'ethereum', 'bitcoin', 'polygon'
    payment_processor VARCHAR(50), -- e.g., 'stripe', 'btcpay', 'direct_blockchain'
    processor_fee DECIMAL(15, 2), -- Fee charged by payment processor
    metadata JSONB, -- Store processor-specific data
    processed_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create indexes for payments
CREATE INDEX idx_payments_invoice_id ON payments(invoice_id);
CREATE INDEX idx_payments_transaction_id ON payments(transaction_id) WHERE transaction_id IS NOT NULL;
CREATE INDEX idx_payments_blockchain_tx ON payments(blockchain_tx_hash) WHERE blockchain_tx_hash IS NOT NULL;
CREATE INDEX idx_payments_status ON payments(status);
CREATE INDEX idx_payments_payment_method ON payments(payment_method);

-- Create invoice_items table for line items
CREATE TABLE IF NOT EXISTS invoice_items (
    id SERIAL PRIMARY KEY,
    invoice_id INTEGER NOT NULL REFERENCES invoices(id) ON DELETE CASCADE,
    description VARCHAR(500) NOT NULL,
    quantity DECIMAL(10, 2) NOT NULL DEFAULT 1,
    unit_price DECIMAL(15, 2) NOT NULL,
    tax_rate DECIMAL(5, 2) DEFAULT 0, -- Percentage
    total DECIMAL(15, 2) NOT NULL, -- Calculated: quantity * unit_price * (1 + tax_rate/100)
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create index for invoice_items
CREATE INDEX idx_invoice_items_invoice_id ON invoice_items(invoice_id);

-- Create triggers for updated_at
CREATE TRIGGER update_invoices_updated_at BEFORE UPDATE ON invoices
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_payments_updated_at BEFORE UPDATE ON payments
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_invoice_items_updated_at BEFORE UPDATE ON invoice_items
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- Create function to automatically update invoice status based on payments
CREATE OR REPLACE FUNCTION update_invoice_status()
RETURNS TRIGGER AS $$
DECLARE
    total_paid DECIMAL(15, 2);
    invoice_amount DECIMAL(15, 2);
BEGIN
    -- Get total paid amount for this invoice
    SELECT COALESCE(SUM(amount), 0) INTO total_paid
    FROM payments
    WHERE invoice_id = NEW.invoice_id AND status = 'COMPLETED';

    -- Get invoice amount
    SELECT amount INTO invoice_amount
    FROM invoices
    WHERE id = NEW.invoice_id;

    -- Update invoice status based on payment
    IF total_paid >= invoice_amount THEN
        UPDATE invoices SET status = 'PAID', paid_date = CURRENT_TIMESTAMP
        WHERE id = NEW.invoice_id AND status != 'PAID';
    ELSIF total_paid > 0 THEN
        UPDATE invoices SET status = 'PARTIALLY_PAID'
        WHERE id = NEW.invoice_id AND status NOT IN ('PAID', 'PARTIALLY_PAID');
    END IF;

    RETURN NEW;
END;
$$ language 'plpgsql';

-- Create trigger to update invoice status when payment is completed
CREATE TRIGGER update_invoice_on_payment AFTER INSERT OR UPDATE ON payments
    FOR EACH ROW
    WHEN (NEW.status = 'COMPLETED')
    EXECUTE FUNCTION update_invoice_status();