<?php

namespace Core\App\Models;

use PDO;

class OrderLifecycleEventModel
{
    protected PDO $db;

    public function __construct()
    {
        $this->db = \db();
        $this->ensureTable();
    }

    protected function ensureTable(): void
    {
        static $done = false;
        if ($done) {
            return;
        }
        $done = true;

        // Create table if missing (safe for fresh installs)
        $sql = "CREATE TABLE IF NOT EXISTS order_lifecycle_events (
            id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            order_id INT NOT NULL,
            admin_id INT NULL,
            event_type VARCHAR(80) NOT NULL,
            provider VARCHAR(80) NULL,
            meta LONGTEXT NULL,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            KEY idx_order_event (order_id, event_type),
            KEY idx_admin_event (admin_id, event_type),
            KEY idx_created_at (created_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";

        try {
            $this->db->exec($sql);
        } catch (\Throwable $e) {
            // Table may already exist — safe to ignore
        }

        // Incremental column additions (idempotent — silently skip if already present)
        $columns = [];
        try {
            foreach ($this->db->query("SHOW COLUMNS FROM order_lifecycle_events")->fetchAll(PDO::FETCH_ASSOC) as $col) {
                $columns[(string)($col['Field'] ?? '')] = true;
            }
        } catch (\Throwable $e) {
            return; // If we can't inspect the table, bail out gracefully
        }

        $migrations = [
            'admin_id'   => "ALTER TABLE order_lifecycle_events ADD COLUMN admin_id INT NULL AFTER order_id",
            'provider'   => "ALTER TABLE order_lifecycle_events ADD COLUMN provider VARCHAR(80) NULL AFTER event_type",
            'meta'       => "ALTER TABLE order_lifecycle_events ADD COLUMN meta LONGTEXT NULL AFTER provider",
            'created_at' => "ALTER TABLE order_lifecycle_events ADD COLUMN created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER meta",
        ];

        foreach ($migrations as $col => $ddl) {
            if (!isset($columns[$col])) {
                try { $this->db->exec($ddl); } catch (\Throwable $e) { /* already added */ }
            }
        }

        // Indexes (silently skip if duplicate)
        foreach ([
            "ALTER TABLE order_lifecycle_events ADD KEY idx_order_event (order_id, event_type)",
            "ALTER TABLE order_lifecycle_events ADD KEY idx_admin_event (admin_id, event_type)",
            "ALTER TABLE order_lifecycle_events ADD KEY idx_created_at (created_at)",
        ] as $idxSql) {
            try { $this->db->exec($idxSql); } catch (\Throwable $e) { /* duplicate key — safe to ignore */ }
        }
    }

    public function log(int $orderId, string $eventType, ?int $adminId = null, ?string $provider = null, array $meta = []): void
    {
        $stmt = $this->db->prepare("INSERT INTO order_lifecycle_events (order_id, admin_id, event_type, provider, meta, created_at) VALUES (?, ?, ?, ?, ?, NOW())");
        $stmt->execute([
            $orderId,
            $adminId ?: null,
            $eventType,
            $provider,
            $meta ? json_encode($meta, JSON_UNESCAPED_UNICODE) : null,
        ]);
    }

    public function exists(int $orderId, string $eventType): bool
    {
        $stmt = $this->db->prepare("SELECT 1 FROM order_lifecycle_events WHERE order_id = ? AND event_type = ? LIMIT 1");
        $stmt->execute([$orderId, $eventType]);
        return (bool)$stmt->fetchColumn();
    }

    public function getByOrder(int $orderId): array
    {
        $stmt = $this->db->prepare("SELECT e.*, a.username AS admin_name, a.role AS admin_role FROM order_lifecycle_events e LEFT JOIN admins a ON a.id = e.admin_id WHERE e.order_id = ? ORDER BY e.created_at DESC, e.id DESC");
        $stmt->execute([$orderId]);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
        foreach ($rows as &$row) {
            $row['meta'] = !empty($row['meta']) ? (json_decode($row['meta'], true) ?: []) : [];
        }
        return $rows;
    }

    public function getMemberSummary(int $adminId, int $days = 30): array
    {
        $days = max(1, $days);
        $since = date('Y-m-d H:i:s', strtotime('-' . $days . ' days'));

        $summary = [
            'assigned' => 0,
            'calls' => 0,
            'callbacks' => 0,
            'confirmed_calls' => 0,
            'rejected_calls' => 0,
            'confirmed' => 0,
            'rejected' => 0,
            'shipment_sent' => 0,
            'return_requested_to_provider' => 0,
            'returned_manual' => 0,
            'returned_auto_provider' => 0,
            'delivered' => 0,
            'confirmed_delivered' => 0,
            'confirmed_returned' => 0,
            'shipped_delivered' => 0,
            'shipped_returned' => 0,
            'delivered_revenue_confirmed' => 0.0,
            'delivered_revenue_shipped' => 0.0,
        ];

        $stmt = $this->db->prepare("SELECT COUNT(*) FROM order_assignments WHERE admin_id = ? AND assigned_at >= ?");
        $stmt->execute([$adminId, $since]);
        $summary['assigned'] = (int)$stmt->fetchColumn();

        $stmt = $this->db->prepare("SELECT 
                COUNT(*) AS total_calls,
                SUM(CASE WHEN result = 'callback_later' THEN 1 ELSE 0 END) AS callbacks,
                SUM(CASE WHEN result = 'confirmed' THEN 1 ELSE 0 END) AS confirmed_calls,
                SUM(CASE WHEN result = 'rejected' THEN 1 ELSE 0 END) AS rejected_calls
            FROM order_call_attempts
            WHERE admin_id = ? AND called_at >= ?");
        $stmt->execute([$adminId, $since]);
        $calls = $stmt->fetch(PDO::FETCH_ASSOC) ?: [];
        $summary['calls'] = (int)($calls['total_calls'] ?? 0);
        $summary['callbacks'] = (int)($calls['callbacks'] ?? 0);
        $summary['confirmed_calls'] = (int)($calls['confirmed_calls'] ?? 0);
        $summary['rejected_calls'] = (int)($calls['rejected_calls'] ?? 0);

        $stmt = $this->db->prepare("SELECT event_type, COUNT(*) AS c FROM order_lifecycle_events WHERE admin_id = ? AND created_at >= ? GROUP BY event_type");
        $stmt->execute([$adminId, $since]);
        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
            $type = (string)$row['event_type'];
            if (array_key_exists($type, $summary)) {
                $summary[$type] = (int)$row['c'];
            }
        }

        $stmt = $this->db->prepare("SELECT COUNT(DISTINCT c.order_id)
            FROM order_lifecycle_events c
            INNER JOIN order_lifecycle_events d ON d.order_id = c.order_id AND d.event_type = 'delivered'
            WHERE c.admin_id = ? AND c.event_type = 'confirmed' AND c.created_at >= ?");
        $stmt->execute([$adminId, $since]);
        $summary['confirmed_delivered'] = (int)$stmt->fetchColumn();

        $stmt = $this->db->prepare("SELECT COUNT(DISTINCT c.order_id)
            FROM order_lifecycle_events c
            INNER JOIN order_lifecycle_events r ON r.order_id = c.order_id AND r.event_type IN ('returned_manual','returned_auto_provider')
            WHERE c.admin_id = ? AND c.event_type = 'confirmed' AND c.created_at >= ?");
        $stmt->execute([$adminId, $since]);
        $summary['confirmed_returned'] = (int)$stmt->fetchColumn();

        $stmt = $this->db->prepare("SELECT COUNT(DISTINCT s.order_id)
            FROM order_lifecycle_events s
            INNER JOIN order_lifecycle_events d ON d.order_id = s.order_id AND d.event_type = 'delivered'
            WHERE s.admin_id = ? AND s.event_type = 'shipment_sent' AND s.created_at >= ?");
        $stmt->execute([$adminId, $since]);
        $summary['shipped_delivered'] = (int)$stmt->fetchColumn();

        $stmt = $this->db->prepare("SELECT COUNT(DISTINCT s.order_id)
            FROM order_lifecycle_events s
            INNER JOIN order_lifecycle_events r ON r.order_id = s.order_id AND r.event_type IN ('returned_manual','returned_auto_provider')
            WHERE s.admin_id = ? AND s.event_type = 'shipment_sent' AND s.created_at >= ?");
        $stmt->execute([$adminId, $since]);
        $summary['shipped_returned'] = (int)$stmt->fetchColumn();

        $stmt = $this->db->prepare("SELECT COALESCE(SUM(o.total_amount),0)
            FROM order_lifecycle_events c
            INNER JOIN order_lifecycle_events d ON d.order_id = c.order_id AND d.event_type = 'delivered'
            INNER JOIN orders o ON o.id = c.order_id
            WHERE c.admin_id = ? AND c.event_type = 'confirmed' AND c.created_at >= ?");
        $stmt->execute([$adminId, $since]);
        $summary['delivered_revenue_confirmed'] = (float)$stmt->fetchColumn();

        $stmt = $this->db->prepare("SELECT COALESCE(SUM(o.total_amount),0)
            FROM order_lifecycle_events s
            INNER JOIN order_lifecycle_events d ON d.order_id = s.order_id AND d.event_type = 'delivered'
            INNER JOIN orders o ON o.id = s.order_id
            WHERE s.admin_id = ? AND s.event_type = 'shipment_sent' AND s.created_at >= ?");
        $stmt->execute([$adminId, $since]);
        $summary['delivered_revenue_shipped'] = (float)$stmt->fetchColumn();

        return $summary;
    }
}