<?php

require_once 'Logger.php';

class Order {
    private $conn;
    private $logger;
    private $table_name = "orders";

    public function __construct($db, $logger = null) {
        $this->conn = $db;
        $this->logger = $logger;
        $this->ensureColumns();
        $this->ensurePaymentsTable();
        $this->ensurePaymentVerificationColumns();
    }

    private function ensureColumns() {
        $this->conn->exec("ALTER TABLE orders MODIFY status ENUM('pending','approved','rejected','delivered','completed') NOT NULL DEFAULT 'pending'");
        foreach ([
            'payment_method'       => "VARCHAR(40) DEFAULT NULL",
            'final_payment_amount' => "DECIMAL(10,2) NOT NULL DEFAULT 0",
            'assigned_code'        => "VARCHAR(255) DEFAULT NULL",
            'eis_transaction_id'   => "VARCHAR(80) DEFAULT NULL",
            'eis_outbox_id'        => "INT DEFAULT NULL",
            'completed_by'         => "INT DEFAULT NULL",
            'completed_at'         => "DATETIME DEFAULT NULL",
            'customer_id'          => "INT DEFAULT NULL",
        ] as $column => $definition) {
            $stmt = $this->conn->query("SHOW COLUMNS FROM orders LIKE '" . $column . "'");
            if (!$stmt->fetchColumn()) {
                $this->conn->exec("ALTER TABLE orders ADD COLUMN `" . $column . "` " . $definition);
            }
        }
        $codeColumn = $this->conn->query("SHOW COLUMNS FROM orders LIKE 'assigned_code'")->fetch(PDO::FETCH_ASSOC);
        if ($codeColumn && stripos((string)$codeColumn['Type'], 'text') === false) {
            $this->conn->exec('ALTER TABLE orders MODIFY assigned_code TEXT DEFAULT NULL');
        }
    }

    private function ensurePaymentsTable() {
        $this->conn->exec("
            CREATE TABLE IF NOT EXISTS `order_payments` (
              `payment_id` INT(11) NOT NULL AUTO_INCREMENT,
              `order_id` INT(11) NOT NULL,
              `branch_id` INT(11) NOT NULL,
              `amount` DECIMAL(10,2) NOT NULL,
              `payment_method` VARCHAR(40) NOT NULL DEFAULT 'cash',
              `notes` VARCHAR(255) DEFAULT NULL,
              `is_initial` TINYINT(1) NOT NULL DEFAULT 0,
              `recorded_by` INT(11) NOT NULL,
              `payment_date` DATETIME NOT NULL,
              PRIMARY KEY (`payment_id`),
              KEY `idx_order_id` (`order_id`),
              KEY `idx_branch_id` (`branch_id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
        ");
    }

    // Weekly-report verification support for order_payments, mirroring the same
    // is_verified/report_id convention Sales::verifyWeeklyReport() already applies
    // to sold_items/laybuy_items/deposits — self-heals here too since either class
    // may run first depending on which page loads.
    private function ensurePaymentVerificationColumns() {
        $stmt = $this->conn->query("SHOW COLUMNS FROM order_payments LIKE 'is_verified'");
        if (!$stmt->fetchColumn()) {
            $this->conn->exec('ALTER TABLE order_payments ADD COLUMN is_verified TINYINT(1) NOT NULL DEFAULT 0');
        }
        $stmt = $this->conn->query("SHOW COLUMNS FROM order_payments LIKE 'report_id'");
        if (!$stmt->fetchColumn()) {
            $this->conn->exec('ALTER TABLE order_payments ADD COLUMN report_id INT DEFAULT NULL');
        }
    }

    // -------------------------------------------------------------------------
    // Core CRUD
    // -------------------------------------------------------------------------

    public function createOrder($branchId, $itemName, $price, $customerName, $customerContact, $amountPaid, $deliveryDate, $status, $notes, $createdBy, $paymentMethod = 'cash', $customerId = null) {
        $orderNumber = 'ORD' . date('Y') . sprintf('%04d', $this->getNextOrderNumber());

        $stmt = $this->conn->prepare("
            INSERT INTO " . $this->table_name . "
              (branch_id, order_number, item_name, price, customer_name, customer_contact,
               amount_paid, delivery_date, status, notes, created_by, customer_id, created_at)
            VALUES
              (:branch_id, :order_number, :item_name, :price, :customer_name, :customer_contact,
               :amount_paid, :delivery_date, :status, :notes, :created_by, :customer_id, NOW())
        ");
        $stmt->bindParam(':branch_id',        $branchId,       PDO::PARAM_INT);
        $stmt->bindParam(':order_number',      $orderNumber);
        $stmt->bindParam(':item_name',         $itemName);
        $stmt->bindParam(':price',             $price);
        $stmt->bindParam(':customer_name',     $customerName);
        $stmt->bindParam(':customer_contact',  $customerContact);
        $stmt->bindParam(':amount_paid',       $amountPaid);
        $stmt->bindParam(':delivery_date',     $deliveryDate);
        $stmt->bindParam(':status',            $status);
        $stmt->bindParam(':notes',             $notes);
        $stmt->bindParam(':created_by',        $createdBy, PDO::PARAM_INT);
        $stmt->bindParam(':customer_id',       $customerId);

        $result = $stmt->execute();

        if ($result) {
            $orderId = (int)$this->conn->lastInsertId();

            // Seed the initial deposit as a payment record
            if ($amountPaid > 0) {
                $this->insertPaymentRecord($orderId, (int)$branchId, round((float)$amountPaid, 2), $paymentMethod, 'Initial deposit', 1, (int)$createdBy, null);
            }

            if ($this->logger) {
                $this->logger->logOrder('CREATE', $orderId, [
                    'branch_id' => $branchId, 'order_number' => $orderNumber,
                    'item_name' => $itemName, 'price' => $price,
                    'customer_name' => $customerName, 'customer_contact' => $customerContact,
                    'amount_paid' => $amountPaid, 'delivery_date' => $deliveryDate,
                    'status' => $status, 'notes' => $notes, 'created_by' => $createdBy,
                ]);
            }
        }

        return $result;
    }

    public function getOrderById($orderId) {
        $stmt = $this->conn->prepare("
            SELECT o.*, u.username as created_by_name
            FROM " . $this->table_name . " o
            LEFT JOIN users u ON o.created_by = u.user_id
            WHERE o.order_id = :order_id
            LIMIT 1
        ");
        $stmt->bindParam(':order_id', $orderId, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }

    public function getOrdersByBranch($branchId) {
        $stmt = $this->conn->prepare("
            SELECT o.*, u.username as created_by_name,
                   COALESCE(
                       (SELECT SUM(p.amount) FROM order_payments p WHERE p.order_id = o.order_id),
                       o.amount_paid
                   ) as total_paid
            FROM " . $this->table_name . " o
            LEFT JOIN users u ON o.created_by = u.user_id
            WHERE o.branch_id = :branch_id
            ORDER BY o.created_at DESC
        ");
        $stmt->bindParam(':branch_id', $branchId, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function getAllOrders() {
        $stmt = $this->conn->prepare("
            SELECT o.*, b.branch_name, u.username as created_by_name,
                   COALESCE(
                       (SELECT SUM(p.amount) FROM order_payments p WHERE p.order_id = o.order_id),
                       o.amount_paid
                   ) as total_paid
            FROM " . $this->table_name . " o
            LEFT JOIN branches b ON b.branch_id = o.branch_id
            LEFT JOIN users u ON o.created_by = u.user_id
            ORDER BY o.created_at DESC
        ");
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function updateOrder($orderId, $itemName, $price, $customerName, $customerContact, $amountPaid, $deliveryDate, $status, $notes) {
        $oldData = null;
        if ($this->logger) {
            $old = $this->getOrderById($orderId);
            if ($old) {
                $oldData = array_intersect_key($old, array_flip(['item_name','price','customer_name','customer_contact','amount_paid','delivery_date','status','notes']));
            }
        }

        $stmt = $this->conn->prepare("
            UPDATE " . $this->table_name . "
            SET item_name = :item_name, price = :price,
                customer_name = :customer_name, customer_contact = :customer_contact,
                amount_paid = :amount_paid, delivery_date = :delivery_date,
                status = :status, notes = :notes
            WHERE order_id = :order_id
        ");
        $stmt->bindParam(':order_id',        $orderId,        PDO::PARAM_INT);
        $stmt->bindParam(':item_name',        $itemName);
        $stmt->bindParam(':price',            $price);
        $stmt->bindParam(':customer_name',    $customerName);
        $stmt->bindParam(':customer_contact', $customerContact);
        $stmt->bindParam(':amount_paid',      $amountPaid);
        $stmt->bindParam(':delivery_date',    $deliveryDate);
        $stmt->bindParam(':status',           $status);
        $stmt->bindParam(':notes',            $notes);

        $result = $stmt->execute();

        if ($result && $this->logger && $oldData) {
            $this->logger->logOrder('UPDATE', $orderId, compact('item_name','price','customer_name','customer_contact','amount_paid','delivery_date','status','notes'), $oldData);
        }

        return $result;
    }

    public function deleteOrder($orderId) {
        $orderData = $this->logger ? $this->getOrderById($orderId) : null;

        $this->conn->prepare("DELETE FROM order_payments WHERE order_id = :id")->execute([':id' => (int)$orderId]);

        $stmt = $this->conn->prepare("DELETE FROM " . $this->table_name . " WHERE order_id = :order_id");
        $stmt->bindParam(':order_id', $orderId, PDO::PARAM_INT);
        $result = $stmt->execute();

        if ($result && $this->logger && $orderData) {
            $this->logger->logOrder('DELETE', $orderId, null, $orderData);
        }

        return $result;
    }

    public function updateStatus($orderId, $status) {
        $allowed = ['pending', 'approved', 'rejected', 'delivered', 'completed'];
        if (!in_array($status, $allowed, true)) {
            throw new InvalidArgumentException('Invalid order status.');
        }
        $stmt = $this->conn->prepare("UPDATE " . $this->table_name . " SET status = :status WHERE order_id = :order_id");
        return $stmt->execute([':status' => $status, ':order_id' => (int)$orderId]);
    }

    // -------------------------------------------------------------------------
    // Payment tracking
    // -------------------------------------------------------------------------

    /**
     * Record a payment against an order.
     * Automatically seeds the initial deposit record from orders.amount_paid
     * for legacy orders that pre-date this payment table.
     */
    public function recordPayment($orderId, $amount, $paymentMethod, $notes, $userId, $isInitial = false) {
        $orderId = (int)$orderId;
        $amount  = round((float)$amount, 2);
        if ($amount <= 0) return false;

        $order = $this->getOrderById($orderId);
        if (!$order) return false;

        // Auto-migrate: if this is not an initial record and no payments exist yet,
        // seed the legacy amount_paid as the initial deposit before recording new payment.
        if (!$isInitial) {
            $checkStmt = $this->conn->prepare("SELECT COUNT(*) FROM order_payments WHERE order_id = :id");
            $checkStmt->execute([':id' => $orderId]);
            if ((int)$checkStmt->fetchColumn() === 0 && (float)$order['amount_paid'] > 0) {
                $this->insertPaymentRecord(
                    $orderId,
                    (int)$order['branch_id'],
                    round((float)$order['amount_paid'], 2),
                    $order['payment_method'] ?? 'cash',
                    'Initial deposit (migrated)',
                    1,
                    (int)$order['created_by'],
                    $order['created_at']
                );
            }
        }

        return $this->insertPaymentRecord(
            $orderId,
            (int)$order['branch_id'],
            $amount,
            $paymentMethod,
            $notes,
            $isInitial ? 1 : 0,
            (int)$userId,
            null
        );
    }

    /** Returns all payments for an order, oldest first. */
    public function getPayments($orderId) {
        $stmt = $this->conn->prepare("
            SELECT op.*, u.username as recorded_by_name
            FROM order_payments op
            LEFT JOIN users u ON op.recorded_by = u.user_id
            WHERE op.order_id = :id
            ORDER BY op.payment_date ASC, op.payment_id ASC
        ");
        $stmt->execute([':id' => (int)$orderId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    /** Unverified order payments for a branch (mirrors Laybuy::getWeeklyPaymentDetails), for the weekly verification report. */
    public function getUnverifiedPayments($branchId) {
        $stmt = $this->conn->prepare("
            SELECT op.payment_id, op.order_id, op.amount, op.payment_method, op.notes, op.is_initial, op.payment_date,
                   o.order_number, o.item_name, o.customer_name, o.assigned_code
            FROM order_payments op
            JOIN orders o ON o.order_id = op.order_id
            WHERE op.branch_id = :branch_id AND op.is_verified = 0
            ORDER BY op.payment_date ASC
        ");
        $stmt->execute([':branch_id' => (int)$branchId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    /** Total amount paid across all payment records (falls back to orders.amount_paid for legacy rows). */
    public function getTotalPaid($orderId) {
        $orderId = (int)$orderId;
        $stmt = $this->conn->prepare("SELECT COUNT(*), COALESCE(SUM(amount), 0) FROM order_payments WHERE order_id = :id");
        $stmt->execute([':id' => $orderId]);
        [$count, $sum] = $stmt->fetch(PDO::FETCH_NUM);
        if ((int)$count > 0) return round((float)$sum, 2);

        // Legacy fallback
        $stmt = $this->conn->prepare("SELECT COALESCE(amount_paid, 0) FROM orders WHERE order_id = :id");
        $stmt->execute([':id' => $orderId]);
        return round((float)$stmt->fetchColumn(), 2);
    }

    /** Remaining balance = price - total paid. */
    public function getBalance($orderId, array $order = null) {
        if ($order === null) {
            $order = $this->getOrderById($orderId);
        }
        $price = round((float)($order['price'] ?? 0), 2);
        return round($price - $this->getTotalPaid($orderId), 2);
    }

    // -------------------------------------------------------------------------
    // Completion
    // -------------------------------------------------------------------------

    /**
     * Mark order completed, assign unique code, optionally record a final payment.
     * Preserves backward-compatible EIS columns.
     */
    public function completeWithCode($orderId, $uniqueCode, $finalPaymentAmount, $paymentMethod, $userId, $eisTransactionId, $eisOutboxId = null) {
        $orderId = (int)$orderId;

        // Record the final payment if non-zero
        if ((float)$finalPaymentAmount > 0) {
            $this->recordPayment($orderId, (float)$finalPaymentAmount, $paymentMethod, 'Final payment', $userId);
        }

        $stmt = $this->conn->prepare("
            UPDATE " . $this->table_name . "
            SET assigned_code = :assigned_code,
                final_payment_amount = :final_payment_amount,
                payment_method = :payment_method,
                eis_transaction_id = :eis_transaction_id,
                eis_outbox_id = :eis_outbox_id,
                status = 'completed',
                completed_by = :completed_by,
                completed_at = NOW()
            WHERE order_id = :order_id
        ");
        return $stmt->execute([
            ':assigned_code'        => $uniqueCode,
            ':final_payment_amount' => round((float)$finalPaymentAmount, 2),
            ':payment_method'       => $paymentMethod,
            ':eis_transaction_id'   => $eisTransactionId,
            ':eis_outbox_id'        => $eisOutboxId,
            ':completed_by'         => (int)$userId,
            ':order_id'             => $orderId,
        ]);
    }

    // -------------------------------------------------------------------------
    // Internal helpers
    // -------------------------------------------------------------------------

    private function insertPaymentRecord($orderId, $branchId, $amount, $paymentMethod, $notes, $isInitial, $recordedBy, $paymentDate) {
        $stmt = $this->conn->prepare("
            INSERT INTO order_payments
              (order_id, branch_id, amount, payment_method, notes, is_initial, recorded_by, payment_date)
            VALUES
              (:oid, :bid, :amt, :pm, :notes, :is_init, :uid, :pdate)
        ");
        return $stmt->execute([
            ':oid'     => $orderId,
            ':bid'     => $branchId,
            ':amt'     => $amount,
            ':pm'      => $paymentMethod ?: 'cash',
            ':notes'   => $notes,
            ':is_init' => $isInitial,
            ':uid'     => $recordedBy,
            ':pdate'   => $paymentDate ?? date('Y-m-d H:i:s'),
        ]);
    }

    private function getNextOrderNumber() {
        $stmt = $this->conn->prepare("
            SELECT MAX(CAST(SUBSTRING(order_number, 9) AS UNSIGNED)) as max_number
            FROM " . $this->table_name . "
            WHERE order_number LIKE 'ORD" . date('Y') . "%'
        ");
        $stmt->execute();
        $result = $stmt->fetch(PDO::FETCH_ASSOC);
        return ($result['max_number'] ?? 0) + 1;
    }
}
?>
