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

$db = getDB();

try {
    // SQLite doesn't support ALTER TABLE to modify constraints
    // So we'll use a transaction to ensure data consistency
    $db->exec('BEGIN TRANSACTION');
    
    // Create new table with desired 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
    )');
    
    // Copy all data
    $db->exec('INSERT INTO subscribers_new 
               SELECT * FROM subscribers');
    
    // Drop old table
    $db->exec('DROP TABLE subscribers');
    
    // Rename new table
    $db->exec('ALTER TABLE subscribers_new RENAME TO subscribers');
    
    // Recreate indexes
    $db->exec('CREATE INDEX IF NOT EXISTS idx_subscribers_meter ON subscribers(meter_number)');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_subscribers_national_id ON subscribers(national_id)');
    $db->exec('CREATE INDEX IF NOT EXISTS idx_subscribers_status ON subscribers(status)');
    
    $db->exec('COMMIT');
    echo "Successfully updated family_members constraint.\n";
    
} catch (Exception $e) {
    $db->exec('ROLLBACK');
    echo "Error: " . $e->getMessage() . "\n";
}
