-- New subscribers payments table
CREATE TABLE IF NOT EXISTS new_subscriber_payments (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    subscriber_id INTEGER NOT NULL,
    meter_number TEXT NOT NULL,
    amount DECIMAL(10,2) NOT NULL CHECK(amount >= 0),
    payment_date DATE NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (subscriber_id) REFERENCES subscribers(id) ON DELETE RESTRICT
);

CREATE INDEX IF NOT EXISTS idx_new_subscriber_payments_subscriber ON new_subscriber_payments(subscriber_id);
CREATE INDEX IF NOT EXISTS idx_new_subscriber_payments_date ON new_subscriber_payments(payment_date);

-- Expenses table
CREATE TABLE IF NOT EXISTS expenses (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    reason TEXT NOT NULL,
    amount DECIMAL(10,2) NOT NULL CHECK(amount >= 0),
    expense_date DATE NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_expenses_date ON expenses(expense_date);

-- Triggers for timestamp updates
CREATE TRIGGER IF NOT EXISTS update_new_subscriber_payments_timestamp
   AFTER UPDATE ON new_subscriber_payments
BEGIN
   UPDATE new_subscriber_payments SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
END;

CREATE TRIGGER IF NOT EXISTS update_expenses_timestamp
   AFTER UPDATE ON expenses
BEGIN
   UPDATE expenses SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
END;

-- View for financial summary
CREATE VIEW IF NOT EXISTS view_financial_summary AS
SELECT 
    'invoice_payment' as type,
    i.payment_date as date,
    i.total_amount as amount,
    s.meter_number,
    s.full_name as details
FROM invoices i
JOIN subscribers s ON i.subscriber_id = s.id
WHERE i.status = 'paid'
UNION ALL
SELECT 
    'new_subscriber' as type,
    nsp.payment_date as date,
    nsp.amount,
    nsp.meter_number,
    s.full_name as details
FROM new_subscriber_payments nsp
JOIN subscribers s ON nsp.subscriber_id = s.id
UNION ALL
SELECT 
    'expense' as type,
    e.expense_date as date,
    -e.amount as amount,
    NULL as meter_number,
    e.reason as details
FROM expenses e
ORDER BY date DESC;
