<?php
require_once __DIR__ . '/../../config/init.php';

$db = getDB();

try {
    $db->exec('BEGIN TRANSACTION');
    
    // Disable foreign key constraints
    $db->exec('PRAGMA foreign_keys = OFF');
    
    // 1. Drop views first
    $db->exec('DROP VIEW IF EXISTS view_unpaid_invoices');
    $db->exec('DROP VIEW IF EXISTS view_financial_summary');
    $db->exec('DROP VIEW IF EXISTS view_consumption_summary');
    
    // 2. Backup all tables that reference subscribers
    $db->exec('CREATE TABLE consumption_backup AS SELECT * FROM consumption');
    $db->exec('CREATE TABLE invoices_backup AS SELECT * FROM invoices');
    $db->exec('CREATE TABLE payments_backup AS SELECT * FROM payments');
    $db->exec('CREATE TABLE new_subscriber_payments_backup AS SELECT * FROM new_subscriber_payments');
    
    // 3. Create new subscribers table with updated constraint
    $db->exec('CREATE TABLE subscribers_new (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        meter_number TEXT UNIQUE NOT NULL CHECK(length(meter_number) > 0),
        full_name TEXT NOT NULL CHECK(length(full_name) > 0),
        national_id TEXT NOT NULL CHECK(length(national_id) > 0),
        address TEXT,
        phone TEXT,
        email TEXT,
        family_members INTEGER NOT NULL DEFAULT 0 CHECK(family_members >= 0),
        status INTEGER DEFAULT 1 CHECK(status IN (0, 1)),
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )');
    
    // 4. Copy data to new table, ensuring family_members is never negative
    $db->exec('INSERT INTO subscribers_new 
               SELECT 
                   id,
                   meter_number,
                   full_name,
                   national_id,
                   address,
                   phone,
                   email,
                   CASE WHEN family_members < 0 THEN 0 ELSE family_members END,
                   status,
                   created_at,
                   updated_at
               FROM subscribers');
    
    // 5. Drop original tables in correct order
    $db->exec('DROP TABLE payments');
    $db->exec('DROP TABLE invoices');
    $db->exec('DROP TABLE consumption');
    $db->exec('DROP TABLE new_subscriber_payments');
    $db->exec('DROP TABLE subscribers');
    
    // 6. Rename new subscribers table
    $db->exec('ALTER TABLE subscribers_new RENAME TO subscribers');
    
    // 7. Recreate indexes on subscribers
    $db->exec('CREATE INDEX idx_subscribers_meter ON subscribers(meter_number)');
    $db->exec('CREATE INDEX idx_subscribers_national_id ON subscribers(national_id)');
    $db->exec('CREATE INDEX idx_subscribers_status ON subscribers(status)');
    
    // 8. Recreate other tables with their foreign keys
    $db->exec('CREATE TABLE consumption (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        subscriber_id INTEGER NOT NULL,
        previous_reading DECIMAL(10,2) NOT NULL CHECK(previous_reading >= 0),
        current_reading DECIMAL(10,2) NOT NULL CHECK(current_reading >= previous_reading),
        reading_date DATE NOT NULL,
        consumption_period TEXT NOT NULL CHECK(consumption_period IN ("monthly", "bimonthly", "quarterly")),
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (subscriber_id) REFERENCES subscribers(id) ON DELETE RESTRICT
    )');
    
    $db->exec('CREATE TABLE invoices (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        subscriber_id INTEGER NOT NULL,
        consumption_id INTEGER NOT NULL UNIQUE,
        fixed_fees DECIMAL(10,2) NOT NULL CHECK(fixed_fees >= 0),
        collective_entitlement_amount DECIMAL(10,2) NOT NULL CHECK(collective_entitlement_amount >= 0),
        consumption_amount DECIMAL(10,2) NOT NULL CHECK(consumption_amount >= 0),
        penalties DECIMAL(10,2) DEFAULT 0 CHECK(penalties >= 0),
        total_amount DECIMAL(10,2) NOT NULL CHECK(total_amount = fixed_fees + collective_entitlement_amount + consumption_amount + penalties),
        status TEXT CHECK(status IN ("paid", "unpaid", "cancelled")) NOT NULL DEFAULT "unpaid",
        due_date DATE NOT NULL,
        payment_date DATE,
        payment_method TEXT CHECK(payment_method IN ("cash", "check", "transfer") OR payment_method IS NULL),
        invoice_number TEXT,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (subscriber_id) REFERENCES subscribers(id) ON DELETE RESTRICT,
        FOREIGN KEY (consumption_id) REFERENCES consumption(id) ON DELETE RESTRICT
    )');
    
    $db->exec('CREATE TABLE payments (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        invoice_id INTEGER NOT NULL,
        amount DECIMAL(10,2) NOT NULL CHECK(amount > 0),
        payment_date DATE NOT NULL,
        payment_method TEXT CHECK(payment_method IN ("cash", "check", "transfer", "invoice")),
        notes TEXT,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE RESTRICT
    )');
    
    $db->exec('CREATE TABLE 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
    )');
    
    // 9. Restore data in correct order
    $db->exec('INSERT INTO consumption SELECT * FROM consumption_backup');
    $db->exec('INSERT INTO invoices SELECT * FROM invoices_backup');
    $db->exec('INSERT INTO payments SELECT * FROM payments_backup');
    $db->exec('INSERT INTO new_subscriber_payments SELECT * FROM new_subscriber_payments_backup');
    
    // 10. Recreate indexes
    $db->exec('CREATE INDEX idx_consumption_subscriber ON consumption(subscriber_id)');
    $db->exec('CREATE INDEX idx_consumption_date ON consumption(reading_date)');
    $db->exec('CREATE INDEX idx_invoices_subscriber ON invoices(subscriber_id)');
    $db->exec('CREATE INDEX idx_invoices_status ON invoices(status)');
    $db->exec('CREATE INDEX idx_invoices_due_date ON invoices(due_date)');
    $db->exec('CREATE INDEX idx_payments_invoice ON payments(invoice_id)');
    $db->exec('CREATE INDEX idx_payments_date ON payments(payment_date)');
    $db->exec('CREATE INDEX idx_new_subscriber_payments_subscriber ON new_subscriber_payments(subscriber_id)');
    $db->exec('CREATE INDEX idx_new_subscriber_payments_date ON new_subscriber_payments(payment_date)');
    
    // 11. Recreate views
    $db->exec('CREATE VIEW view_unpaid_invoices AS 
        SELECT i.*, s.meter_number, s.full_name, s.address 
        FROM invoices i 
        JOIN subscribers s ON i.subscriber_id = s.id 
        WHERE i.status = "unpaid"');
        
    $db->exec('CREATE VIEW view_financial_summary AS 
        SELECT 
            COUNT(*) as total_invoices,
            SUM(total_amount) as total_billed,
            SUM(CASE WHEN status = "paid" THEN total_amount ELSE 0 END) as total_collected,
            SUM(CASE WHEN status = "unpaid" THEN total_amount ELSE 0 END) as total_pending
        FROM invoices');
        
    $db->exec('CREATE VIEW view_consumption_summary AS 
        SELECT c.*, s.meter_number, s.full_name, i.total_amount, i.status as payment_status
        FROM consumption c 
        JOIN subscribers s ON c.subscriber_id = s.id 
        LEFT JOIN invoices i ON c.id = i.consumption_id');
    
    // 12. Drop backup tables
    $db->exec('DROP TABLE consumption_backup');
    $db->exec('DROP TABLE invoices_backup');
    $db->exec('DROP TABLE payments_backup');
    $db->exec('DROP TABLE new_subscriber_payments_backup');
    
    // 13. Re-enable foreign key constraints
    $db->exec('PRAGMA foreign_keys = ON');
    
    $db->exec('COMMIT');
    echo "Successfully updated family_members constraint and restored all relationships.\n";
    
} catch (Exception $e) {
    $db->exec('ROLLBACK');
    // Make sure to re-enable foreign keys even if there's an error
    $db->exec('PRAGMA foreign_keys = ON');
    echo "Error: " . $e->getMessage() . "\n";
}
