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

$db = getDB();

try {
    $db->exec('BEGIN TRANSACTION');
    
    // Disable foreign key constraints temporarily
    $db->exec('PRAGMA foreign_keys = OFF');
    
    // Create a temporary table with the new constraint
    $db->exec('CREATE TABLE subscribers_temp (
        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 1 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
    )');
    
    // Copy data from the old table to the new one, ensuring family_members is at least 0
    $db->exec('INSERT INTO subscribers_temp 
               SELECT id, meter_number, full_name, national_id, address, phone, email,
                      MAX(0, family_members) as family_members,
                      status, created_at, updated_at 
               FROM subscribers');
    
    // Drop the old table
    $db->exec('DROP TABLE subscribers');
    
    // Rename the new table to the original name
    $db->exec('ALTER TABLE subscribers_temp RENAME TO subscribers');
    
    // Recreate the indexes
    $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)');
    
    // Re-enable foreign key constraints
    $db->exec('PRAGMA foreign_keys = ON');
    
    $db->exec('COMMIT');
    echo "Migration completed successfully.\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 during migration: " . $e->getMessage() . "\n";
}
