<?php
namespace Core;

/**
 * Advanced Base Model
 * Incorporates Shared DB logic, Soft Deletes, Action Logging, and Notifications.
 */
abstract class Model {
    protected $db;
    protected $table = ''; // Must remain defined by child models
    protected $useSoftDeletes = true;
    protected $logActions = true;
    protected $moduleName = 'System';

    public function __construct() {
        $this->db = Database::getInstance();
    }

    public function all() {
        $where = $this->useSoftDeletes ? " WHERE deleted_at IS NULL" : "";
        $stmt = $this->db->query("SELECT * FROM `{$this->table}`" . $where);
        return $stmt->fetchAll();
    }

    public function find($id) {
        $where = $this->useSoftDeletes ? " AND deleted_at IS NULL" : "";
        $stmt = $this->db->query("SELECT * FROM `{$this->table}` WHERE id = ?" . $where . " LIMIT 1", [$id]);
        return $stmt->fetch();
    }

    public function delete($id, $userId = 1) {
        if ($this->useSoftDeletes) {
            $stmt = $this->db->query("UPDATE `{$this->table}` SET deleted_at = CURRENT_TIMESTAMP WHERE id = ?", [$id]);
        } else {
            $stmt = $this->db->query("DELETE FROM `{$this->table}` WHERE id = ?", [$id]);
        }
        $success = $stmt->rowCount() > 0;
        if ($success && $this->logActions) {
            $this->logAction($userId, 'deleted', "تم حذف (أرشفة) سجل من جدول {$this->table} برقم {$id}", $id);
        }
        return $success;
    }

    public function restore($id, $userId = 1) {
        if ($this->useSoftDeletes) {
            $stmt = $this->db->query("UPDATE `{$this->table}` SET deleted_at = NULL WHERE id = ?", [$id]);
            $success = $stmt->rowCount() > 0;
            if ($success && $this->logActions) {
                $this->logAction($userId, 'restored', "تم استعادة سجل في جدول {$this->table} برقم {$id}", $id);
            }
            return $success;
        }
        return false;
    }

    protected function logAction($userId, $action, $description, $recordId = null) {
        $sql = "INSERT INTO action_logs (user_id, module, action, description, record_id) VALUES (?, ?, ?, ?, ?)";
        $this->db->query($sql, [$userId, $this->moduleName, $action, $description, $recordId]);
    }

    public function notifyUser($userId, $title, $message, $type = 'info') {
        $sql = "INSERT INTO notifications (user_id, title, message, type) VALUES (?, ?, ?, ?)";
        $this->db->query($sql, [$userId, $title, $message, $type]);

        // === Auto-Cleanup: prevent unbounded table growth ===
        // 1. Delete read notifications older than 7 days
        $this->db->query(
            "DELETE FROM notifications WHERE user_id = ? AND is_read = 1 AND created_at < (NOW() - INTERVAL 7 DAY)",
            [$userId]
        );
        // 2. Keep only latest 100 notifications total per user  
        $this->db->query(
            "DELETE FROM notifications WHERE user_id = ? AND id NOT IN (
                SELECT id FROM (
                    SELECT id FROM notifications WHERE user_id = ? ORDER BY created_at DESC LIMIT 100
                ) AS keep
            )",
            [$userId, $userId]
        );
    }

    public function paginate($sql, $params = [], $page = 1, $perPage = 20) {
        $page = max(1, (int)$page);
        $offset = ($page - 1) * $perPage;

        // 1. Get total record count (naive approach: wrap the original query)
        $countSql = "SELECT COUNT(*) as total FROM ($sql) as sub";
        $total = $this->query($countSql, $params)->fetch()->total ?? 0;

        // 2. Fetch the actual page data
        $pagedSql = $sql . " LIMIT " . (int)$perPage . " OFFSET " . (int)$offset;
        $data = $this->query($pagedSql, $params)->fetchAll();

        return [
            'data' => $data,
            'pagination' => [
                'total_records' => (int)$total,
                'total_pages'   => ceil($total / $perPage),
                'current_page'  => $page,
                'per_page'      => $perPage
            ]
        ];
    }

    protected function query($sql, $params = []) {
        return $this->db->query($sql, $params);
    }
}
