<?php

namespace App\Controllers;

use App\Config\Database;
use App\Utils\Auth;
use App\Utils\Flash;
use PDO;

class SuperadminController {

    public function __construct() {
        // Enforce superadmin role check on all controller actions
        Auth::requireSuperAdmin();
    }

    public function dashboard() {
        $db = Database::getInstance()->getConnection();

        // Fetch counts for statistics
        $userCount = $db->query("SELECT COUNT(*) FROM users")->fetchColumn();
        $privilegeCount = $db->query("SELECT COUNT(*) FROM privileges")->fetchColumn();
        $uomCount = $db->query("SELECT COUNT(*) FROM units_of_measure")->fetchColumn();
        $smtpCount = $db->query("SELECT COUNT(*) FROM smtp_settings")->fetchColumn();

        // Fetch NotifyAgent Run Time setting
        $stmtS = $db->prepare("SELECT setting_value FROM settings WHERE setting_key = 'notify_agent_run_time'");
        $stmtS->execute();
        $notifyRunTime = $stmtS->fetchColumn() ?: '08:00 AM';

        $runHour = '08';
        $runMinute = '00';
        $runPeriod = 'AM';
        
        if (preg_match('/^(\d{2}):(\d{2}) (AM|PM)$/', $notifyRunTime, $matches)) {
            $runHour = $matches[1];
            $runMinute = $matches[2];
            $runPeriod = $matches[3];
        }

        require_once __DIR__ . '/../../views/superadmin/dashboard.php';
    }

    // ==========================================
    // PRIVILEGES CRUD
    // ==========================================
    public function privileges() {
        $db = Database::getInstance()->getConnection();
        $records = $db->query("SELECT * FROM privileges ORDER BY name ASC")->fetchAll();
        require_once __DIR__ . '/../../views/superadmin/privileges/index.php';
    }

    public function privilegeCreate() {
        $actionUrl = '/superadmin/privilegeStore';
        $title = 'Create Privilege';
        require_once __DIR__ . '/../../views/superadmin/privileges/form.php';
    }

    public function privilegeStore() {
        if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
            header("Location: /superadmin/privileges");
            exit;
        }

        $name = trim($_POST['name'] ?? '');
        $slug = trim($_POST['slug'] ?? '');

        if (empty($name) || empty($slug)) {
            Flash::set('error', 'All fields are required.');
            header("Location: /superadmin/privilegeCreate");
            exit;
        }

        $db = Database::getInstance()->getConnection();
        $stmt = $db->prepare("INSERT INTO privileges (name, slug) VALUES (?, ?)");
        $stmt->execute([$name, $slug]);

        Flash::set('success', 'Privilege created successfully.');
        header("Location: /superadmin/privileges");
        exit;
    }

    public function privilegeEdit($id) {
        $db = Database::getInstance()->getConnection();
        $stmt = $db->prepare("SELECT * FROM privileges WHERE id = ?");
        $stmt->execute([$id]);
        $record = $stmt->fetch();

        if (!$record) {
            Flash::set('error', 'Privilege not found.');
            header("Location: /superadmin/privileges");
            exit;
        }

        $actionUrl = "/superadmin/privilegeUpdate/{$id}";
        $title = 'Edit Privilege';
        require_once __DIR__ . '/../../views/superadmin/privileges/form.php';
    }

    public function privilegeUpdate($id) {
        if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
            header("Location: /superadmin/privileges");
            exit;
        }

        $name = trim($_POST['name'] ?? '');
        $slug = trim($_POST['slug'] ?? '');

        if (empty($name) || empty($slug)) {
            Flash::set('error', 'All fields are required.');
            header("Location: /superadmin/privilegeEdit/{$id}");
            exit;
        }

        $db = Database::getInstance()->getConnection();
        $stmt = $db->prepare("UPDATE privileges SET name = ?, slug = ? WHERE id = ?");
        $stmt->execute([$name, $slug, $id]);

        Flash::set('success', 'Privilege updated successfully.');
        header("Location: /superadmin/privileges");
        exit;
    }

    public function privilegeDelete($id) {
        $db = Database::getInstance()->getConnection();
        
        // Remove mappings in user_privileges first
        $stmt = $db->prepare("DELETE FROM user_privileges WHERE privilege_id = ?");
        $stmt->execute([$id]);

        $stmt = $db->prepare("DELETE FROM privileges WHERE id = ?");
        $stmt->execute([$id]);

        Flash::set('success', 'Privilege deleted successfully.');
        header("Location: /superadmin/privileges");
        exit;
    }

    // ==========================================
    // UNITS OF MEASURE CRUD
    // ==========================================
    public function uoms() {
        $db = Database::getInstance()->getConnection();
        $records = $db->query("SELECT * FROM units_of_measure ORDER BY name ASC")->fetchAll();
        require_once __DIR__ . '/../../views/superadmin/uoms/index.php';
    }

    public function uomCreate() {
        $actionUrl = '/superadmin/uomStore';
        $title = 'Create Unit of Measure';
        require_once __DIR__ . '/../../views/superadmin/uoms/form.php';
    }

    public function uomStore() {
        if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
            header("Location: /superadmin/uoms");
            exit;
        }

        $name = trim($_POST['name'] ?? '');

        if (empty($name)) {
            Flash::set('error', 'Name is required.');
            header("Location: /superadmin/uomCreate");
            exit;
        }

        $db = Database::getInstance()->getConnection();
        $stmt = $db->prepare("INSERT INTO units_of_measure (name) VALUES (?)");
        $stmt->execute([$name]);

        Flash::set('success', 'Unit of Measure created successfully.');
        header("Location: /superadmin/uoms");
        exit;
    }

    public function uomEdit($id) {
        $db = Database::getInstance()->getConnection();
        $stmt = $db->prepare("SELECT * FROM units_of_measure WHERE id = ?");
        $stmt->execute([$id]);
        $record = $stmt->fetch();

        if (!$record) {
            Flash::set('error', 'Unit of Measure not found.');
            header("Location: /superadmin/uoms");
            exit;
        }

        $actionUrl = "/superadmin/uomUpdate/{$id}";
        $title = 'Edit Unit of Measure';
        require_once __DIR__ . '/../../views/superadmin/uoms/form.php';
    }

    public function uomUpdate($id) {
        if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
            header("Location: /superadmin/uoms");
            exit;
        }

        $name = trim($_POST['name'] ?? '');

        if (empty($name)) {
            Flash::set('error', 'Name is required.');
            header("Location: /superadmin/uomEdit/{$id}");
            exit;
        }

        $db = Database::getInstance()->getConnection();
        $stmt = $db->prepare("UPDATE units_of_measure SET name = ? WHERE id = ?");
        $stmt->execute([$name, $id]);

        Flash::set('success', 'Unit of Measure updated successfully.');
        header("Location: /superadmin/uoms");
        exit;
    }

    public function uomDelete($id) {
        $db = Database::getInstance()->getConnection();
        
        // Dissociate from medicines
        $stmt = $db->prepare("UPDATE medicines SET uom_id = NULL WHERE uom_id = ?");
        $stmt->execute([$id]);

        $stmt = $db->prepare("DELETE FROM units_of_measure WHERE id = ?");
        $stmt->execute([$id]);

        Flash::set('success', 'Unit of Measure deleted successfully.');
        header("Location: /superadmin/uoms");
        exit;
    }

    // ==========================================
    // SMTP SETTINGS CRUD
    // ==========================================
    public function smtp() {
        $db = Database::getInstance()->getConnection();
        $records = $db->query("SELECT * FROM smtp_settings ORDER BY id ASC")->fetchAll();
        require_once __DIR__ . '/../../views/superadmin/smtp/index.php';
    }

    public function smtpCreate() {
        $actionUrl = '/superadmin/smtpStore';
        $title = 'Create SMTP Settings';
        require_once __DIR__ . '/../../views/superadmin/smtp/form.php';
    }

    public function smtpStore() {
        if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
            header("Location: /superadmin/smtp");
            exit;
        }

        $host = trim($_POST['smtp_host'] ?? '');
        $port = intval($_POST['smtp_port'] ?? 0);
        $username = trim($_POST['smtp_username'] ?? '');
        $password = trim($_POST['smtp_password'] ?? '');
        $fromEmail = trim($_POST['from_email'] ?? '');
        $fromName = trim($_POST['from_name'] ?? '');

        if (empty($host) || empty($port) || empty($username) || empty($password) || empty($fromName)) {
            Flash::set('error', 'All fields except From Email are required.');
            header("Location: /superadmin/smtpCreate");
            exit;
        }

        if (empty($fromEmail)) $fromEmail = null;

        $db = Database::getInstance()->getConnection();
        $stmt = $db->prepare("INSERT INTO smtp_settings (smtp_host, smtp_port, smtp_username, smtp_password, from_email, from_name) VALUES (?, ?, ?, ?, ?, ?)");
        $stmt->execute([$host, $port, $username, $password, $fromEmail, $fromName]);

        Flash::set('success', 'SMTP Settings created successfully.');
        header("Location: /superadmin/smtp");
        exit;
    }

    public function smtpEdit($id) {
        $db = Database::getInstance()->getConnection();
        $stmt = $db->prepare("SELECT * FROM smtp_settings WHERE id = ?");
        $stmt->execute([$id]);
        $record = $stmt->fetch();

        if (!$record) {
            Flash::set('error', 'SMTP Settings not found.');
            header("Location: /superadmin/smtp");
            exit;
        }

        $actionUrl = "/superadmin/smtpUpdate/{$id}";
        $title = 'Edit SMTP Settings';
        require_once __DIR__ . '/../../views/superadmin/smtp/form.php';
    }

    public function smtpUpdate($id) {
        if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
            header("Location: /superadmin/smtp");
            exit;
        }

        $host = trim($_POST['smtp_host'] ?? '');
        $port = intval($_POST['smtp_port'] ?? 0);
        $username = trim($_POST['smtp_username'] ?? '');
        $password = trim($_POST['smtp_password'] ?? '');
        $fromEmail = trim($_POST['from_email'] ?? '');
        $fromName = trim($_POST['from_name'] ?? '');

        if (empty($host) || empty($port) || empty($username) || empty($password) || empty($fromName)) {
            Flash::set('error', 'All fields except From Email are required.');
            header("Location: /superadmin/smtpEdit/{$id}");
            exit;
        }

        if (empty($fromEmail)) $fromEmail = null;

        $db = Database::getInstance()->getConnection();
        $stmt = $db->prepare("UPDATE smtp_settings SET smtp_host = ?, smtp_port = ?, smtp_username = ?, smtp_password = ?, from_email = ?, from_name = ? WHERE id = ?");
        $stmt->execute([$host, $port, $username, $password, $fromEmail, $fromName, $id]);

        Flash::set('success', 'SMTP Settings updated successfully.');
        header("Location: /superadmin/smtp");
        exit;
    }

    public function smtpDelete($id) {
        $db = Database::getInstance()->getConnection();
        $stmt = $db->prepare("DELETE FROM smtp_settings WHERE id = ?");
        $stmt->execute([$id]);

        Flash::set('success', 'SMTP Settings deleted successfully.');
        header("Location: /superadmin/smtp");
        exit;
    }

    // ==========================================
    // USERS CRUD
    // ==========================================
    public function users() {
        $db = Database::getInstance()->getConnection();
        
        $records = $db->query("
            SELECT u.*, t.name as tenant_name, b.name as branch_name 
            FROM users u
            LEFT JOIN tenants t ON u.tenant_id = t.id
            LEFT JOIN branches b ON u.branch_id = b.id
            ORDER BY u.created_at DESC
        ")->fetchAll();
        
        require_once __DIR__ . '/../../views/superadmin/users/index.php';
    }

    public function userCreate() {
        $db = Database::getInstance()->getConnection();
        $tenants = $db->query("SELECT id, name FROM tenants ORDER BY name ASC")->fetchAll();
        $branches = $db->query("SELECT id, name, tenant_id FROM branches ORDER BY name ASC")->fetchAll();

        $actionUrl = '/superadmin/userStore';
        $title = 'Create User';
        require_once __DIR__ . '/../../views/superadmin/users/form.php';
    }

    public function userStore() {
        if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
            header("Location: /superadmin/users");
            exit;
        }

        $tenantId = intval($_POST['tenant_id'] ?? 0);
        $branchId = !empty($_POST['branch_id']) ? intval($_POST['branch_id']) : null;
        $name = trim($_POST['name'] ?? '');
        $email = trim($_POST['email'] ?? '');
        $password = $_POST['password'] ?? '';
        $role = $_POST['role'] ?? 'cashier';
        $status = $_POST['status'] ?? 'active';

        if (empty($name) || empty($email) || empty($password)) {
            Flash::set('error', 'Name, Email, and Password are required.');
            header("Location: /superadmin/userCreate");
            exit;
        }

        $passwordHash = password_hash($password, PASSWORD_DEFAULT);

        $db = Database::getInstance()->getConnection();
        
        // Verify unique email
        $stmtCheck = $db->prepare("SELECT COUNT(*) FROM users WHERE email = ?");
        $stmtCheck->execute([$email]);
        if ($stmtCheck->fetchColumn() > 0) {
            Flash::set('error', 'Email or Username is already registered.');
            header("Location: /superadmin/userCreate");
            exit;
        }

        $stmt = $db->prepare("INSERT INTO users (tenant_id, branch_id, name, email, password_hash, role, status) VALUES (?, ?, ?, ?, ?, ?, ?)");
        $stmt->execute([$tenantId, $branchId, $name, $email, $passwordHash, $role, $status]);

        Flash::set('success', 'User created successfully.');
        header("Location: /superadmin/users");
        exit;
    }

    public function userEdit($id) {
        $db = Database::getInstance()->getConnection();
        
        $stmt = $db->prepare("SELECT * FROM users WHERE id = ?");
        $stmt->execute([$id]);
        $record = $stmt->fetch();

        if (!$record) {
            Flash::set('error', 'User not found.');
            header("Location: /superadmin/users");
            exit;
        }

        $tenants = $db->query("SELECT id, name FROM tenants ORDER BY name ASC")->fetchAll();
        $branches = $db->query("SELECT id, name, tenant_id FROM branches ORDER BY name ASC")->fetchAll();

        $actionUrl = "/superadmin/userUpdate/{$id}";
        $title = 'Edit User';
        require_once __DIR__ . '/../../views/superadmin/users/form.php';
    }

    public function userUpdate($id) {
        if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
            header("Location: /superadmin/users");
            exit;
        }

        $tenantId = intval($_POST['tenant_id'] ?? 0);
        $branchId = !empty($_POST['branch_id']) ? intval($_POST['branch_id']) : null;
        $name = trim($_POST['name'] ?? '');
        $email = trim($_POST['email'] ?? '');
        $password = $_POST['password'] ?? '';
        $role = $_POST['role'] ?? 'cashier';
        $status = $_POST['status'] ?? 'active';

        if (empty($name) || empty($email)) {
            Flash::set('error', 'Name and Email are required.');
            header("Location: /superadmin/userEdit/{$id}");
            exit;
        }

        $db = Database::getInstance()->getConnection();

        // Verify unique email excluding current user
        $stmtCheck = $db->prepare("SELECT COUNT(*) FROM users WHERE email = ? AND id != ?");
        $stmtCheck->execute([$email, $id]);
        if ($stmtCheck->fetchColumn() > 0) {
            Flash::set('error', 'Email or Username is already registered.');
            header("Location: /superadmin/userEdit/{$id}");
            exit;
        }

        if (!empty($password)) {
            $passwordHash = password_hash($password, PASSWORD_DEFAULT);
            $stmt = $db->prepare("UPDATE users SET tenant_id = ?, branch_id = ?, name = ?, email = ?, password_hash = ?, role = ?, status = ? WHERE id = ?");
            $stmt->execute([$tenantId, $branchId, $name, $email, $passwordHash, $role, $status, $id]);
        } else {
            $stmt = $db->prepare("UPDATE users SET tenant_id = ?, branch_id = ?, name = ?, email = ?, role = ?, status = ? WHERE id = ?");
            $stmt->execute([$tenantId, $branchId, $name, $email, $role, $status, $id]);
        }

        Flash::set('success', 'User updated successfully.');
        header("Location: /superadmin/users");
        exit;
    }

    public function userDelete($id) {
        $db = Database::getInstance()->getConnection();
        
        // Remove mappings in user_privileges first
        $stmt = $db->prepare("DELETE FROM user_privileges WHERE user_id = ?");
        $stmt->execute([$id]);

        $stmt = $db->prepare("DELETE FROM users WHERE id = ?");
        $stmt->execute([$id]);

        Flash::set('success', 'User deleted successfully.');
        header("Location: /superadmin/users");
        exit;
    }

    private function getDaemonStatus() {
        if (strncasecmp(PHP_OS, 'WIN', 3) === 0) {
            $cmd = 'wmic process where "CommandLine like \'%expiry_scheduler.php%\'" get ProcessId,CommandLine /value';
            $output = [];
            @exec($cmd, $output);
            
            $isRunning = false;
            $pid = null;
            
            foreach ($output as $line) {
                $line = trim($line);
                if (strpos($line, 'CommandLine=') === 0) {
                    $cmdLine = substr($line, 12);
                    if (strpos($cmdLine, 'wmic') === false && 
                        strpos($cmdLine, 'powershell') === false && 
                        strpos($cmdLine, 'cmd.exe') === false &&
                        strpos($cmdLine, '--daemon') !== false) {
                        $isRunning = true;
                    }
                }
                if ($isRunning && strpos($line, 'ProcessId=') === 0) {
                    $pid = trim(substr($line, 10));
                    break;
                }
            }
            return ['running' => $isRunning, 'pid' => $pid];
        } else {
            $cmd = 'pgrep -f "expiry_scheduler.php --daemon"';
            $output = [];
            @exec($cmd, $output);
            if (!empty($output) && is_numeric(trim($output[0]))) {
                return ['running' => true, 'pid' => trim($output[0])];
            }
            return ['running' => false, 'pid' => null];
        }
    }

    private function startBackgroundDaemon() {
        if (strncasecmp(PHP_OS, 'WIN', 3) === 0) {
            $phpExe = 'C:\tools\php83\php.exe';
            $workingDir = dirname(dirname(__DIR__));
            $cmd = "powershell -Command \"Start-Process '{$phpExe}' -ArgumentList 'bin/expiry_scheduler.php', '--daemon' -WorkingDirectory '{$workingDir}' -WindowStyle Hidden\"";
            @pclose(@popen($cmd, "r"));
        } else {
            $scriptPath = realpath(__DIR__ . '/../../bin/expiry_scheduler.php');
            $cmd = "php \"{$scriptPath}\" --daemon > /dev/null 2>&1 &";
            @exec($cmd);
        }
    }

    private function stopBackgroundDaemon($pid) {
        if (empty($pid)) return;
        if (strncasecmp(PHP_OS, 'WIN', 3) === 0) {
            @exec("taskkill /F /PID {$pid}");
        } else {
            @exec("kill -9 {$pid}");
        }
    }

    public function settings() {
        $db = Database::getInstance()->getConnection();

        // 1. Fetch scheduler status
        $stmtActive = $db->prepare("SELECT setting_value FROM settings WHERE setting_key = 'scheduler_active'");
        $stmtActive->execute();
        $schedulerActive = $stmtActive->fetchColumn() ?: '0';

        // 2. Fetch last heartbeat
        $stmtHb = $db->prepare("SELECT setting_value FROM settings WHERE setting_key = 'scheduler_last_heartbeat'");
        $stmtHb->execute();
        $lastHeartbeatVal = $stmtHb->fetchColumn();
        
        $status = $this->getDaemonStatus();
        $heartbeatActive = $status['running'];
        $lastHeartbeat = $lastHeartbeatVal ? date('Y-m-d H:i:s', strtotime($lastHeartbeatVal)) : 'Never';

        // 3. Fetch NotifyAgent Run Time setting
        $stmtS = $db->prepare("SELECT setting_value FROM settings WHERE setting_key = 'notify_agent_run_time'");
        $stmtS->execute();
        $notifyRunTime = $stmtS->fetchColumn() ?: '08:00 AM';

        $runHour = '08';
        $runMinute = '00';
        $runPeriod = 'AM';
        
        if (preg_match('/^(\d{2}):(\d{2}) (AM|PM)$/', $notifyRunTime, $matches)) {
            $runHour = $matches[1];
            $runMinute = $matches[2];
            $runPeriod = $matches[3];
        }

        // 4. Fetch Twilio API Config
        $twilioStmt = $db->query("SELECT setting_key, setting_value FROM settings WHERE setting_key IN ('twilio_sid', 'twilio_token', 'twilio_whatsapp_number', 'scheduler_downtime_emails')");
        $twilioSettings = $twilioStmt->fetchAll(PDO::FETCH_KEY_PAIR);
        $twilioSid = $twilioSettings['twilio_sid'] ?? '';
        $twilioToken = $twilioSettings['twilio_token'] ?? '';
        $twilioNumber = $twilioSettings['twilio_whatsapp_number'] ?? '';
        $downtimeEmails = $twilioSettings['scheduler_downtime_emails'] ?? '';

        // Fetch application url
        $appUrlStmt = $db->query("SELECT setting_value FROM settings WHERE setting_key = 'application_url'");
        $applicationUrl = $appUrlStmt->fetchColumn() ?: '';

        // 5. Fetch execution logs
        $logs = $db->query("
            SELECT l.*, t.name as tenant_name 
            FROM notifyalertlog l
            LEFT JOIN tenants t ON l.tenant_id = t.id
            ORDER BY l.created_at DESC
            LIMIT 15
        ")->fetchAll();

        require_once __DIR__ . '/../../views/superadmin/settings.php';
    }

    public function startScheduler() {
        $db = Database::getInstance()->getConnection();
        
        $stmt = $db->prepare("
            INSERT INTO settings (setting_key, setting_value) 
            VALUES ('scheduler_active', '1') 
            ON DUPLICATE KEY UPDATE setting_value = '1'
        ");
        $stmt->execute();

        // Check if background daemon is running. If not, spin it up!
        $status = $this->getDaemonStatus();
        if (!$status['running']) {
            $this->startBackgroundDaemon();
            Flash::set('success', 'Scheduler started successfully and background daemon process launched.');
        } else {
            Flash::set('success', 'Scheduler started (background process already running).');
        }

        header("Location: /superadmin/settings");
        exit;
    }

    public function stopScheduler() {
        $db = Database::getInstance()->getConnection();
        
        $stmt = $db->prepare("
            INSERT INTO settings (setting_key, setting_value) 
            VALUES ('scheduler_active', '0') 
            ON DUPLICATE KEY UPDATE setting_value = '0'
        ");
        $stmt->execute();

        // Kill the background daemon process if it is running
        $status = $this->getDaemonStatus();
        if ($status['running']) {
            $this->stopBackgroundDaemon($status['pid']);
            Flash::set('success', 'Scheduler stopped successfully and background process terminated.');
        } else {
            Flash::set('success', 'Scheduler stopped.');
        }

        header("Location: /superadmin/settings");
        exit;
    }

    public function updateSettings() {
        if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
            header("Location: /superadmin/settings");
            exit;
        }

        $hour = intval($_POST['run_hour'] ?? 8);
        $minute = intval($_POST['run_minute'] ?? 0);
        $runPeriod = $_POST['run_period'] ?? 'AM';
        
        $runTimeStr = sprintf("%02d:%02d %s", intval($hour), intval($minute), $runPeriod);

        $db = Database::getInstance()->getConnection();
        
        $stmt = $db->prepare("
            INSERT INTO settings (setting_key, setting_value) 
            VALUES ('notify_agent_run_time', ?) 
            ON DUPLICATE KEY UPDATE setting_value = ?
        ");
        $stmt->execute([$runTimeStr, $runTimeStr]);
        
        if (isset($_POST['scheduler_downtime_emails'])) {
            $dtEmails = trim($_POST['scheduler_downtime_emails']);
            $stmt = $db->prepare("
                INSERT INTO settings (setting_key, setting_value) 
                VALUES ('scheduler_downtime_emails', ?) 
                ON DUPLICATE KEY UPDATE setting_value = ?
            ");
            $stmt->execute([$dtEmails, $dtEmails]);
            
            // Reset the sent flag so if it's currently down, the new emails will get it
            $stmt = $db->prepare("
                INSERT INTO settings (setting_key, setting_value) 
                VALUES ('scheduler_downtime_alert_sent', '0') 
                ON DUPLICATE KEY UPDATE setting_value = '0'
            ");
            $stmt->execute();
        }

        if (isset($_POST['application_url'])) {
            $appUrl = trim($_POST['application_url']);
            $stmt = $db->prepare("
                INSERT INTO settings (setting_key, setting_value) 
                VALUES ('application_url', ?) 
                ON DUPLICATE KEY UPDATE setting_value = ?
            ");
            $stmt->execute([$appUrl, $appUrl]);
        }

        Flash::set('success', "System settings updated successfully. NotifyAgent Run Time set to: {$runTimeStr}");
        header("Location: /superadmin/settings");
        exit;
    }

    public function whatsapp() {
        $db = Database::getInstance()->getConnection();
        
        $twilioStmt = $db->query("SELECT setting_key, setting_value FROM settings WHERE setting_key IN ('twilio_sid', 'twilio_token', 'twilio_whatsapp_number', 'twilio_whatsapp_template_sid')");
        $twilioSettings = $twilioStmt->fetchAll(PDO::FETCH_KEY_PAIR);
        
        $twilioSid = $twilioSettings['twilio_sid'] ?? '';
        $twilioToken = $twilioSettings['twilio_token'] ?? '';
        $twilioNumber = $twilioSettings['twilio_whatsapp_number'] ?? '';
        $twilioTemplateSid = $twilioSettings['twilio_whatsapp_template_sid'] ?? '';

        require_once __DIR__ . '/../../views/superadmin/whatsapp.php';
    }

    public function updateTwilioSettings() {
        if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
            header("Location: /superadmin/whatsapp");
            exit;
        }

        $sid = trim($_POST['twilio_sid'] ?? '');
        $token = trim($_POST['twilio_token'] ?? '');
        $number = trim($_POST['twilio_whatsapp_number'] ?? '');
        $templateSid = trim($_POST['twilio_whatsapp_template_sid'] ?? '');

        $db = Database::getInstance()->getConnection();
        
        $stmt = $db->prepare("
            INSERT INTO settings (setting_key, setting_value) 
            VALUES (?, ?) 
            ON DUPLICATE KEY UPDATE setting_value = ?
        ");

        $stmt->execute(['twilio_sid', $sid, $sid]);
        $stmt->execute(['twilio_token', $token, $token]);
        $stmt->execute(['twilio_whatsapp_number', $number, $number]);
        $stmt->execute(['twilio_whatsapp_template_sid', $templateSid, $templateSid]);

        Flash::set('success', "Twilio WhatsApp configuration updated successfully.");
        header("Location: /superadmin/whatsapp");
        exit;
    }

    // ==========================================
    // COMPANY SETTINGS (FEATURES)
    // ==========================================
    public function companies() {
        $db = Database::getInstance()->getConnection();
        $records = $db->query("SELECT * FROM tenants ORDER BY created_at DESC")->fetchAll();
        require_once __DIR__ . '/../../views/superadmin/companies.php';
    }

    public function updateCompanyFeatures() {
        if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
            header("Location: /superadmin/companies");
            exit;
        }

        $tenantId = intval($_POST['tenant_id'] ?? 0);
        $featureEmail = isset($_POST['feature_email_alerts']) ? 1 : 0;
        $featureWhatsapp = isset($_POST['feature_whatsapp_alerts']) ? 1 : 0;
        $maxWhatsappRecipients = isset($_POST['max_whatsapp_recipients']) ? max(1, intval($_POST['max_whatsapp_recipients'])) : 1;

        if ($tenantId === 0) {
            Flash::set('error', 'Invalid tenant specified.');
            header("Location: /superadmin/companies");
            exit;
        }

        $db = Database::getInstance()->getConnection();
        $stmt = $db->prepare("UPDATE tenants SET feature_email_alerts = ?, feature_whatsapp_alerts = ?, max_whatsapp_recipients = ? WHERE id = ?");
        $stmt->execute([$featureEmail, $featureWhatsapp, $maxWhatsappRecipients, $tenantId]);

        Flash::set('success', 'Company features updated successfully.');
        header("Location: /superadmin/companies");
        exit;
    }

    public function logEdit($id) {
        $db = Database::getInstance()->getConnection();
        
        $stmt = $db->prepare("
            SELECT l.*, t.name as tenant_name 
            FROM notifyalertlog l 
            LEFT JOIN tenants t ON l.tenant_id = t.id 
            WHERE l.id = ?
        ");
        $stmt->execute([$id]);
        $record = $stmt->fetch();

        if (!$record) {
            Flash::set('error', 'Log record not found.');
            header("Location: /superadmin/settings");
            exit;
        }

        require_once __DIR__ . '/../../views/superadmin/logs/edit.php';
    }

    public function logUpdate($id) {
        if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
            header("Location: /superadmin/settings");
            exit;
        }

        $recipients = trim($_POST['recipients'] ?? '');
        $itemsCount = intval($_POST['items_count'] ?? 0);
        $status = $_POST['status'] ?? 'success';
        $failureReason = trim($_POST['failure_reason'] ?? '');

        if (empty($recipients)) {
            Flash::set('error', 'Recipients email list is required.');
            header("Location: /superadmin/logEdit/{$id}");
            exit;
        }

        if (empty($failureReason)) $failureReason = null;

        $db = Database::getInstance()->getConnection();
        $stmt = $db->prepare("
            UPDATE notifyalertlog 
            SET recipients = ?, items_count = ?, status = ?, failure_reason = ? 
            WHERE id = ?
        ");
        $stmt->execute([$recipients, $itemsCount, $status, $failureReason, $id]);

        Flash::set('success', 'Log record updated successfully.');
        header("Location: /superadmin/settings");
        exit;
    }

    public function logDelete($id) {
        $db = Database::getInstance()->getConnection();
        
        $stmt = $db->prepare("DELETE FROM notifyalertlog WHERE id = ?");
        $stmt->execute([$id]);

        Flash::set('success', 'Log record deleted successfully.');
        header("Location: /superadmin/settings");
        exit;
    }
    public function registrations() {
        $db = Database::getInstance()->getConnection();
        
        // Fetch all inactive tenants and their corresponding admin user
        $stmt = $db->query("
            SELECT t.*, u.name as admin_name, u.email as admin_email 
            FROM tenants t
            LEFT JOIN users u ON u.tenant_id = t.id AND u.role = 'admin'
            WHERE t.status = 'inactive'
            ORDER BY t.created_at DESC
        ");
        $registrations = $stmt->fetchAll();

        require_once __DIR__ . '/../../views/superadmin/registrations.php';
    }

    public function approveRegistration($tenantId) {
        if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
            header("Location: /superadmin/registrations");
            exit;
        }

        $db = Database::getInstance()->getConnection();
        
        try {
            $db->beginTransaction();
            
            // Activate tenant
            $stmt1 = $db->prepare("UPDATE tenants SET status = 'active' WHERE id = ?");
            $stmt1->execute([$tenantId]);
            
            // Activate associated users
            $stmt2 = $db->prepare("UPDATE users SET status = 'active' WHERE tenant_id = ?");
            $stmt2->execute([$tenantId]);
            
            $db->commit();
            Flash::set('success', 'Registration approved successfully! The pharmacy can now log in.');
        } catch (\Exception $e) {
            $db->rollBack();
            Flash::set('error', 'Failed to approve registration: ' . $e->getMessage());
        }

        header("Location: /superadmin/registrations");
        exit;
    }

    public function rejectRegistration($tenantId) {
        if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
            header("Location: /superadmin/registrations");
            exit;
        }

        $db = Database::getInstance()->getConnection();
        
        try {
            $db->beginTransaction();
            
            // Suspend tenant and users
            $stmt1 = $db->prepare("UPDATE tenants SET status = 'suspended' WHERE id = ?");
            $stmt1->execute([$tenantId]);
            
            $stmt2 = $db->prepare("UPDATE users SET status = 'inactive' WHERE tenant_id = ?");
            $stmt2->execute([$tenantId]);
            
            $db->commit();
            Flash::set('success', 'Registration rejected and account suspended.');
        } catch (\Exception $e) {
            $db->rollBack();
            Flash::set('error', 'Failed to reject registration: ' . $e->getMessage());
        }

        header("Location: /superadmin/registrations");
        exit;
    }

    // ==========================================
    // DATABASE MANAGEMENT
    // ==========================================
    public function database() {
        $db = Database::getInstance()->getConnection();
        $schema = $db->query('SELECT DATABASE()')->fetchColumn();
        
        require_once __DIR__ . '/../../views/superadmin/database.php';
    }

    public function databaseExport() {
        $db = Database::getInstance()->getConnection();
        $schema = $db->query('SELECT DATABASE()')->fetchColumn();
        
        $tables = [];
        $stmt = $db->query('SHOW TABLES');
        while ($row = $stmt->fetch(PDO::FETCH_NUM)) {
            $tables[] = $row[0];
        }

        $sql = "-- Database Backup\n";
        $sql .= "-- Schema: {$schema}\n";
        $sql .= "-- Date: " . date('Y-m-d H:i:s') . "\n\n";
        $sql .= "SET FOREIGN_KEY_CHECKS=0;\n\n";

        foreach ($tables as $table) {
            $sql .= "DROP TABLE IF EXISTS `{$table}`;\n";
            $row2 = $db->query("SHOW CREATE TABLE `{$table}`")->fetch(PDO::FETCH_NUM);
            $sql .= "\n\n" . $row2[1] . ";\n\n";

            $stmt2 = $db->query("SELECT * FROM `{$table}`");
            $num_fields = $stmt2->columnCount();

            while ($row = $stmt2->fetch(PDO::FETCH_NUM)) {
                $sql .= "INSERT INTO `{$table}` VALUES(";
                for ($j = 0; $j < $num_fields; $j++) {
                    if (isset($row[$j])) {
                        $row[$j] = addslashes($row[$j]);
                        $row[$j] = str_replace("\n", "\\n", $row[$j]);
                        $sql .= '"' . $row[$j] . '"';
                    } else {
                        $sql .= 'NULL';
                    }
                    if ($j < ($num_fields - 1)) {
                        $sql .= ',';
                    }
                }
                $sql .= ");\n";
            }
            $sql .= "\n\n\n";
        }
        $sql .= "SET FOREIGN_KEY_CHECKS=1;\n";

        header('Content-Type: application/octet-stream');
        header('Content-Disposition: attachment; filename="backup_' . $schema . '_' . date('Y-m-d_H-i-s') . '.sql"');
        echo $sql;
        exit;
    }

    public function databaseTruncate() {
        if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
            header("Location: /superadmin/database");
            exit;
        }

        $db = Database::getInstance()->getConnection();
        
        $tables = [];
        $stmt = $db->query('SHOW TABLES');
        while ($row = $stmt->fetch(PDO::FETCH_NUM)) {
            $tables[] = $row[0];
        }

        // Tables to exclude from truncation
        $excludedTables = ['privileges', 'settings', 'smtp_settings', 'units_of_measure', 'users'];

        try {
            $db->exec('SET FOREIGN_KEY_CHECKS=0');
            foreach ($tables as $table) {
                if (!in_array($table, $excludedTables)) {
                    $db->exec("TRUNCATE TABLE `{$table}`");
                }
            }
            $db->exec('SET FOREIGN_KEY_CHECKS=1');

            Flash::set('success', 'Database tables have been truncated successfully (excluding core configuration tables).');
        } catch (\Exception $e) {
            Flash::set('error', 'Error truncating database: ' . $e->getMessage());
        }

        header("Location: /superadmin/database");
        exit;
    }
}
