<?php
require_once __DIR__ . '/Logger.php';
require_once __DIR__ . '/Notifications.php';

class RenewalReminder {
    private $conn;
    private $logger;
    private $notifications;

    public function __construct($conn, $logger = null) {
        $this->conn = $conn;
        $this->logger = $logger;
        $this->notifications = new Notifications($conn);
        $this->ensureTables();
    }

    private function ensureTables() {
        $this->conn->exec("
            CREATE TABLE IF NOT EXISTS renewal_reminders (
                reminder_id INT AUTO_INCREMENT PRIMARY KEY,
                source_type ENUM('subscription','warranty','software_license') NOT NULL,
                title VARCHAR(180) NOT NULL,
                customer_name VARCHAR(150) DEFAULT NULL,
                customer_phone VARCHAR(50) DEFAULT NULL,
                related_item_id INT DEFAULT NULL,
                related_sale_id INT DEFAULT NULL,
                branch_id INT DEFAULT NULL,
                due_date DATE NOT NULL,
                reminder_days INT NOT NULL DEFAULT 30,
                status ENUM('active','sent','renewed','expired','cancelled') NOT NULL DEFAULT 'active',
                notes TEXT DEFAULT NULL,
                notification_sent_at DATETIME DEFAULT NULL,
                created_by INT DEFAULT NULL,
                created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                UNIQUE KEY uniq_reminder_source (source_type, related_item_id, related_sale_id, title, due_date),
                INDEX idx_renewal_due_date (due_date),
                INDEX idx_renewal_status (status),
                INDEX idx_renewal_branch (branch_id)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ");
    }

    public function createManual($data) {
        $sql = "INSERT INTO renewal_reminders
                (source_type, title, customer_name, customer_phone, branch_id, due_date, reminder_days, notes, created_by)
                VALUES
                (:source_type, :title, :customer_name, :customer_phone, :branch_id, :due_date, :reminder_days, :notes, :created_by)";
        $stmt = $this->conn->prepare($sql);
        $result = $stmt->execute([
            ':source_type' => $data['source_type'],
            ':title' => $data['title'],
            ':customer_name' => $data['customer_name'],
            ':customer_phone' => $data['customer_phone'],
            ':branch_id' => $data['branch_id'] !== null ? (int)$data['branch_id'] : null,
            ':due_date' => $data['due_date'],
            ':reminder_days' => (int)$data['reminder_days'],
            ':notes' => $data['notes'],
            ':created_by' => (int)$data['created_by']
        ]);

        if ($result && $this->logger) {
            $this->logger->log('CREATE_RENEWAL_REMINDER', 'reminders', "Created {$data['source_type']} reminder: {$data['title']}", $this->conn->lastInsertId(), 'renewal_reminders', null, $data);
        }

        return $result;
    }

    public function importWarrantyReminders($createdBy = null) {
        $sql = "INSERT IGNORE INTO renewal_reminders
                (source_type, title, customer_name, customer_phone, related_item_id, related_sale_id, branch_id, due_date, reminder_days, notes, created_by)
                SELECT
                    'warranty',
                    CONCAT('Warranty expiry: ', i.item_name, ' ', COALESCE(i.unique_code, '')),
                    s.customer_name,
                    s.customer_phone,
                    i.item_id,
                    s.sale_id,
                    s.branch_id,
                    DATE_ADD(DATE(s.sold_at), INTERVAL i.warranty_months MONTH),
                    14,
                    CONCAT('Auto-created from sale ', s.sale_id, ' and item ', i.unique_code),
                    :created_by
                FROM sold_items s
                INNER JOIN items i ON i.item_id = s.item_id
                WHERE i.warranty_months > 0
                  AND s.sold_at IS NOT NULL
                  AND s.sold_at <> '0000-00-00 00:00:00'
                  AND DATE_ADD(DATE(s.sold_at), INTERVAL i.warranty_months MONTH) >= CURDATE()";
        $stmt = $this->conn->prepare($sql);
        $stmt->execute([':created_by' => $createdBy]);
        $count = $stmt->rowCount();

        if ($this->logger) {
            $this->logger->log('IMPORT_WARRANTY_REMINDERS', 'reminders', "Imported {$count} warranty reminders from sold items.");
        }

        return $count;
    }

    public function sendDueNotifications() {
        $sql = "SELECT *
                FROM renewal_reminders
                WHERE status = 'active'
                  AND notification_sent_at IS NULL
                  AND due_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL reminder_days DAY)
                ORDER BY due_date ASC";
        $stmt = $this->conn->prepare($sql);
        $stmt->execute();
        $reminders = $stmt->fetchAll(PDO::FETCH_ASSOC);

        foreach ($reminders as $reminder) {
            $branchId = (int)($reminder['branch_id'] ?: 7);
            $message = ucfirst(str_replace('_', ' ', $reminder['source_type'])) . " reminder: {$reminder['title']} is due on {$reminder['due_date']}.";
            $this->notifications->createNotification($branchId, $message, 'Admin', 0, false);
            if (!empty($reminder['branch_id'])) {
                $this->notifications->createNotification($branchId, $message, 'branch', 0, false);
            }

            $update = $this->conn->prepare("UPDATE renewal_reminders SET status = 'sent', notification_sent_at = NOW() WHERE reminder_id = :reminder_id");
            $update->execute([':reminder_id' => (int)$reminder['reminder_id']]);
        }

        if ($this->logger) {
            $this->logger->log('SEND_RENEWAL_REMINDERS', 'reminders', "Sent " . count($reminders) . " renewal reminder notifications.");
        }

        return count($reminders);
    }

    public function getExpiringContracts($daysAhead = 30) {
        $sql = "SELECT
                    e.employee_id,
                    e.employee_fullname,
                    e.employee_position,
                    e.employment_type,
                    e.contract_start,
                    e.contract_ends,
                    e.employee_phone,
                    e.email,
                    e.branch AS branch_id,
                    b.branch_name,
                    DATEDIFF(e.contract_ends, CURDATE()) AS days_remaining
                FROM employee e
                LEFT JOIN branches b ON b.branch_id = e.branch
                WHERE (e.deleted IS NULL OR e.deleted = 0)
                AND e.contract_ends IS NOT NULL
                AND e.contract_ends <> '0000-00-00'
                AND e.contract_ends BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL :days DAY)
                ORDER BY e.contract_ends ASC";
        $stmt = $this->conn->prepare($sql);
        $stmt->bindValue(':days', (int)$daysAhead, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function getExpiredContracts($limit = 50) {
        $sql = "SELECT
                    e.employee_id,
                    e.employee_fullname,
                    e.employee_position,
                    e.employment_type,
                    e.contract_start,
                    e.contract_ends,
                    e.employee_phone,
                    e.email,
                    b.branch_name,
                    DATEDIFF(CURDATE(), e.contract_ends) AS days_overdue
                FROM employee e
                LEFT JOIN branches b ON b.branch_id = e.branch
                WHERE (e.deleted IS NULL OR e.deleted = 0)
                AND e.contract_ends IS NOT NULL
                AND e.contract_ends <> '0000-00-00'
                AND e.contract_ends < CURDATE()
                ORDER BY e.contract_ends DESC
                LIMIT :lim";
        $stmt = $this->conn->prepare($sql);
        $stmt->bindValue(':lim', (int)$limit, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function sendContractExpiryAlerts($daysAhead = 30) {
        $expiring = $this->getExpiringContracts($daysAhead);
        $count = 0;
        foreach ($expiring as $emp) {
            $name    = $emp['employee_fullname'];
            $pos     = $emp['employee_position'] ?? '';
            $ends    = $emp['contract_ends'];
            $days    = (int)$emp['days_remaining'];
            $label   = $days === 0 ? 'today' : "in {$days} day(s)";
            $message = "Contract expiry: {$name}" . ($pos ? " ({$pos})" : '') . " — contract ends on {$ends} ({$label}).";
            $branchId = (int)($emp['branch_id'] ?: 7);
            $this->notifications->createNotification($branchId, $message, 'Admin', 0, true);
            $count++;
        }
        if ($this->logger) {
            $this->logger->log('CONTRACT_EXPIRY_ALERTS', 'employees', "Sent {$count} contract expiry alert(s).");
        }
        return $count;
    }

    public function updateStatus($reminderId, $status) {
        $allowed = ['active', 'sent', 'renewed', 'expired', 'cancelled'];
        if (!in_array($status, $allowed, true)) {
            return false;
        }

        $stmt = $this->conn->prepare("UPDATE renewal_reminders SET status = :status WHERE reminder_id = :reminder_id");
        return $stmt->execute([
            ':status' => $status,
            ':reminder_id' => (int)$reminderId
        ]);
    }

    public function getReminders($branchId = null) {
        $sql = "SELECT r.*, b.branch_name, i.unique_code, i.item_name, i.item_details
                FROM renewal_reminders r
                LEFT JOIN branches b ON b.branch_id = r.branch_id
                LEFT JOIN items i ON i.item_id = r.related_item_id";
        $params = [];
        if ($branchId !== null) {
            $sql .= " WHERE r.branch_id = :branch_id";
            $params[':branch_id'] = (int)$branchId;
        }
        $sql .= " ORDER BY r.due_date ASC, r.status ASC";
        $stmt = $this->conn->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
}
