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

class CustomerService {
    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 customer_service_requests (
                request_id INT AUTO_INCREMENT PRIMARY KEY,
                request_no VARCHAR(30) NOT NULL UNIQUE,
                request_type ENUM('claim','warranty','service') NOT NULL,
                status ENUM('open','in_progress','waiting_customer','approved','rejected','resolved','closed') NOT NULL DEFAULT 'open',
                priority ENUM('low','normal','high','urgent') NOT NULL DEFAULT 'normal',
                receipt_number VARCHAR(80) DEFAULT NULL,
                branch_id INT DEFAULT NULL,
                item_id INT DEFAULT NULL,
                sale_id INT DEFAULT NULL,
                customer_name VARCHAR(150) NOT NULL,
                customer_phone VARCHAR(50) DEFAULT NULL,
                customer_email VARCHAR(150) DEFAULT NULL,
                delivery_date DATE DEFAULT NULL,
                subject VARCHAR(180) NOT NULL,
                description TEXT NOT NULL,
                resolution TEXT DEFAULT NULL,
                assigned_to INT DEFAULT NULL,
                created_by INT NOT NULL,
                created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                resolved_at DATETIME DEFAULT NULL,
                updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                INDEX idx_customer_service_status (status),
                INDEX idx_customer_service_receipt (receipt_number),
                INDEX idx_customer_service_branch (branch_id),
                INDEX idx_customer_service_item (item_id)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ");

        $this->ensureColumn('customer_service_requests', 'receipt_number', "ALTER TABLE customer_service_requests ADD COLUMN receipt_number VARCHAR(80) DEFAULT NULL AFTER priority");
        $this->ensureColumn('customer_service_requests', 'delivery_date', "ALTER TABLE customer_service_requests ADD COLUMN delivery_date DATE DEFAULT NULL AFTER customer_email");
        try {
            $this->conn->exec("ALTER TABLE customer_service_requests MODIFY status ENUM('open','in_progress','waiting_customer','approved','rejected','resolved','closed') NOT NULL DEFAULT 'open'");
        } catch (Exception $e) { /* already correct */ }
        $this->ensureIndex('customer_service_requests', 'idx_customer_service_receipt', "ALTER TABLE customer_service_requests ADD INDEX idx_customer_service_receipt (receipt_number)");

        $this->conn->exec("
            CREATE TABLE IF NOT EXISTS customer_service_notes (
                note_id INT AUTO_INCREMENT PRIMARY KEY,
                request_id INT NOT NULL,
                note TEXT NOT NULL,
                visibility ENUM('internal','customer') NOT NULL DEFAULT 'internal',
                created_by INT NOT NULL,
                created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                INDEX idx_customer_service_notes_request (request_id)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        ");
    }

    public function createRequest($data) {
        $requestNo = $this->generateRequestNo($data['request_type']);
        $deliveryDate = (!empty($data['delivery_date']) && $data['delivery_date'] !== '')
            ? $data['delivery_date'] : null;

        $sql = "INSERT INTO customer_service_requests
                (request_no, request_type, priority, receipt_number, branch_id, item_id, sale_id, customer_name, customer_phone, customer_email, delivery_date, subject, description, assigned_to, created_by)
                VALUES
                (:request_no, :request_type, :priority, :receipt_number, :branch_id, :item_id, :sale_id, :customer_name, :customer_phone, :customer_email, :delivery_date, :subject, :description, :assigned_to, :created_by)";
        $stmt = $this->conn->prepare($sql);
        $stmt->execute([
            ':request_no' => $requestNo,
            ':request_type' => $data['request_type'],
            ':priority' => $data['priority'],
            ':receipt_number' => $data['receipt_number'],
            ':branch_id' => $data['branch_id'] !== null ? (int)$data['branch_id'] : null,
            ':item_id' => $data['item_id'] !== null ? (int)$data['item_id'] : null,
            ':sale_id' => $data['sale_id'] !== null ? (int)$data['sale_id'] : null,
            ':customer_name' => $data['customer_name'],
            ':customer_phone' => $data['customer_phone'],
            ':customer_email' => $data['customer_email'],
            ':delivery_date' => $deliveryDate,
            ':subject' => $data['subject'],
            ':description' => $data['description'],
            ':assigned_to' => $data['assigned_to'] !== null ? (int)$data['assigned_to'] : null,
            ':created_by' => (int)$data['created_by']
        ]);

        $requestId = (int)$this->conn->lastInsertId();
        $this->addNote($requestId, 'Request opened.', 'internal', (int)$data['created_by']);
        $this->notifications->createNotification((int)($data['branch_id'] ?: 7), "Customer {$data['request_type']} request {$requestNo} has been opened for receipt {$data['receipt_number']}.", 'Admin', 0, false);

        if ($this->logger) {
            $this->logger->log('CREATE_CUSTOMER_SERVICE_REQUEST', 'customer_service', "Opened customer service request {$requestNo}", $requestId, 'customer_service_requests', null, $data);
        }

        return $requestId;
    }

    public function updateDeliveryDate($requestId, $deliveryDate, $note, $userId) {
        $date = ($deliveryDate !== '' && $deliveryDate !== null) ? $deliveryDate : null;
        $stmt = $this->conn->prepare(
            "UPDATE customer_service_requests SET delivery_date = :delivery_date WHERE request_id = :request_id"
        );
        $result = $stmt->execute([':delivery_date' => $date, ':request_id' => (int)$requestId]);

        if ($result && trim($note) !== '') {
            $this->addNote($requestId, trim($note), 'internal', (int)$userId);
        }

        if ($result && $this->logger) {
            $this->logger->log('UPDATE_DELIVERY_DATE', 'customer_service', "Updated delivery date to {$deliveryDate} on request #{$requestId}", $requestId, 'customer_service_requests', null, ['delivery_date' => $date]);
        }

        return $result;
    }

    public function updateRequest($requestId, $status, $resolution, $note, $visibility, $userId) {
        $allowed = ['open', 'in_progress', 'waiting_customer', 'approved', 'rejected', 'resolved', 'closed'];
        if (!in_array($status, $allowed, true)) {
            return false;
        }

        $old = $this->getById($requestId);
        if (!$old) {
            return false;
        }

        $sql = "UPDATE customer_service_requests
                SET status = :status,
                    resolution = :resolution,
                    resolved_at = CASE WHEN :status_for_resolution IN ('resolved', 'closed') AND resolved_at IS NULL THEN NOW() ELSE resolved_at END
                WHERE request_id = :request_id";
        $stmt = $this->conn->prepare($sql);
        $result = $stmt->execute([
            ':status' => $status,
            ':status_for_resolution' => $status,
            ':resolution' => $resolution,
            ':request_id' => (int)$requestId
        ]);

        if ($result && trim($note) !== '') {
            $this->addNote($requestId, $note, $visibility, $userId);
        }

        if ($result && $this->logger) {
            $this->logger->log('UPDATE_CUSTOMER_SERVICE_REQUEST', 'customer_service', "Updated customer service request {$old['request_no']} to {$status}", $requestId, 'customer_service_requests', $old, ['status' => $status, 'resolution' => $resolution]);
        }

        return $result;
    }

    public function addNote($requestId, $note, $visibility, $userId) {
        $visibility = $visibility === 'customer' ? 'customer' : 'internal';
        $stmt = $this->conn->prepare("INSERT INTO customer_service_notes (request_id, note, visibility, created_by) VALUES (:request_id, :note, :visibility, :created_by)");
        return $stmt->execute([
            ':request_id' => (int)$requestId,
            ':note' => $note,
            ':visibility' => $visibility,
            ':created_by' => (int)$userId
        ]);
    }

    public function getRequests($branchId = null) {
        $sql = "SELECT r.*, b.branch_name, i.item_name, i.item_details, i.unique_code, u.username AS created_by_name
                FROM customer_service_requests r
                LEFT JOIN branches b ON b.branch_id = r.branch_id
                LEFT JOIN items i ON i.item_id = r.item_id
                LEFT JOIN users u ON u.user_id = r.created_by";
        $params = [];
        if ($branchId !== null) {
            $sql .= " WHERE r.branch_id = :branch_id";
            $params[':branch_id'] = (int)$branchId;
        }
        $sql .= " ORDER BY r.updated_at DESC";
        $stmt = $this->conn->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function getById($requestId) {
        $stmt = $this->conn->prepare("SELECT r.*, b.branch_name, i.item_name, i.item_details, i.unique_code
                                      FROM customer_service_requests r
                                      LEFT JOIN branches b ON b.branch_id = r.branch_id
                                      LEFT JOIN items i ON i.item_id = r.item_id
                                      WHERE r.request_id = :request_id");
        $stmt->execute([':request_id' => (int)$requestId]);
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }

    public function findSalesByCustomerName($query, $branchId = null, $limit = 60) {
        $query = trim($query);
        if (strlen($query) < 2) {
            return [];
        }

        // Match customer_name OR customer_phone (useful for phone-number lookups too)
        $sql = "SELECT si.transaction_id, si.branch_id, si.customer_name, si.customer_phone,
                       si.sold_at, COUNT(*) AS item_count,
                       SUM(si.total_price) AS total_amount,
                       b.branch_name
                FROM sold_items si
                LEFT JOIN branches b ON b.branch_id = si.branch_id
                WHERE (si.customer_name LIKE :q OR si.customer_phone LIKE :q2)";
        $params = [
            ':q'  => '%' . $query . '%',
            ':q2' => '%' . $query . '%',
        ];

        if ($branchId !== null) {
            $sql .= " AND si.branch_id = :branch_id";
            $params[':branch_id'] = (int)$branchId;
        }

        $sql .= " GROUP BY si.transaction_id, si.branch_id, si.customer_name,
                            si.customer_phone, si.sold_at, b.branch_name
                  ORDER BY si.sold_at DESC
                  LIMIT " . (int)$limit;

        $stmt = $this->conn->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function findSaleByReceipt($receiptNumber, $branchId = null) {
        $receiptNumber = trim($receiptNumber);
        if ($receiptNumber === '') {
            return null;
        }

        $sql = "SELECT si.sale_id, si.transaction_id, si.branch_id, si.customer_name, si.customer_phone,
                       si.sold_at, si.item_id, si.unique_code, si.unit_price, si.total_price,
                       i.item_name, i.item_details, i.warranty_months, b.branch_name
                FROM sold_items si
                LEFT JOIN items i ON i.item_id = si.item_id
                LEFT JOIN branches b ON b.branch_id = si.branch_id
                WHERE (si.transaction_id = :receipt_number OR si.transaction_id LIKE :verified_receipt ESCAPE '\\\\')";
        $params = [
            ':receipt_number' => $receiptNumber,
            ':verified_receipt' => $receiptNumber . '\_VERIFIED\_%'
        ];

        if ($branchId !== null) {
            $sql .= " AND si.branch_id = :branch_id";
            $params[':branch_id'] = (int)$branchId;
        }

        $sql .= " ORDER BY si.sale_id ASC";
        $stmt = $this->conn->prepare($sql);
        $stmt->execute($params);
        $items = $stmt->fetchAll(PDO::FETCH_ASSOC);

        if (!$items) {
            return null;
        }

        $first = $items[0];
        return [
            'receipt_number' => $receiptNumber,
            'stored_transaction_id' => $first['transaction_id'],
            'branch_id' => $first['branch_id'],
            'branch_name' => $first['branch_name'],
            'customer_name' => $first['customer_name'],
            'customer_phone' => $first['customer_phone'],
            'sold_at' => $first['sold_at'],
            'items' => $items
        ];
    }

    public function getNotes($requestId) {
        $sql = "SELECT n.*, u.username
                FROM customer_service_notes n
                LEFT JOIN users u ON u.user_id = n.created_by
                WHERE n.request_id = :request_id
                ORDER BY n.created_at DESC";
        $stmt = $this->conn->prepare($sql);
        $stmt->execute([':request_id' => (int)$requestId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    private function generateRequestNo($type) {
        $prefix = strtoupper(substr($type, 0, 3));
        return $prefix . '-' . date('ymd') . '-' . strtoupper(substr(uniqid(), -5));
    }

    private function ensureColumn($table, $column, $alterSql) {
        $stmt = $this->conn->prepare("SHOW COLUMNS FROM {$table} LIKE :column_name");
        $stmt->execute([':column_name' => $column]);
        if (!$stmt->fetch(PDO::FETCH_ASSOC)) {
            $this->conn->exec($alterSql);
        }
    }

    private function ensureIndex($table, $index, $alterSql) {
        $stmt = $this->conn->prepare("SHOW INDEX FROM {$table} WHERE Key_name = :index_name");
        $stmt->execute([':index_name' => $index]);
        if (!$stmt->fetch(PDO::FETCH_ASSOC)) {
            $this->conn->exec($alterSql);
        }
    }
}

