<?php

declare(strict_types=1);
namespace Repositories;

use RuntimeException;

final class OrderRepository extends BaseRepository
{
    public function create(array $order, array $items, bool $deductStockImmediately = false): int
    {
        $this->db->beginTransaction();
        try {
            if ($deductStockImmediately) {
                $this->assertAndDeductStock($items);
            }

            $sql = 'INSERT INTO orders(user_id, order_number, customer_name, customer_email, customer_phone, address_id, address_line1, address_line2, city, state, pincode, address_snapshot, payment_method, payment_status, order_status, subtotal, shipping_amount, discount_amount, tax_amount, total_amount, delivery_estimate_date, notes, razorpay_order_id, razorpay_payment_id)
                    VALUES(:user_id,:order_number,:customer_name,:customer_email,:customer_phone,:address_id,:address_line1,:address_line2,:city,:state,:pincode,:address_snapshot,:payment_method,:payment_status,:order_status,:subtotal,:shipping_amount,:discount_amount,:tax_amount,:total_amount,:delivery_estimate_date,:notes,:razorpay_order_id,:razorpay_payment_id)';
            $stmt = $this->db->prepare($sql);
            $stmt->execute($order);
            $orderId = (int)$this->db->lastInsertId();

            $itemStmt = $this->db->prepare('INSERT INTO order_items(order_id, product_id, product_name, product_slug, quantity, unit_price, total_price) VALUES(:order_id,:product_id,:product_name,:product_slug,:quantity,:unit_price,:total_price)');
            foreach ($items as $item) {
                $item['order_id'] = $orderId;
                $itemStmt->execute($item);
            }

            $this->insertTimeline($orderId, $order['order_status'], $order['order_status'] === 'payment_pending' ? 'Payment Pending' : 'Order Placed');
            if ($order['payment_method'] === 'cod') {
                $this->insertPayment($orderId, 'cod', null, null, null, (float)$order['total_amount'], 'created', ['method' => 'cod']);
            }

            $this->db->commit();
            return $orderId;
        } catch (\Throwable $e) {
            $this->db->rollBack();
            throw $e;
        }
    }

    public function findByOrderNumber(string $orderNumber): ?array
    {
        return $this->fetchOne('SELECT * FROM orders WHERE order_number=:order_number LIMIT 1', ['order_number' => $orderNumber]);
    }

    public function itemsByOrderNumber(string $orderNumber): array
    {
        return $this->fetchAll('SELECT oi.* FROM order_items oi JOIN orders o ON o.id=oi.order_id WHERE o.order_number=:order_number', ['order_number' => $orderNumber]);
    }

    public function attachRazorpayOrderId(string $orderNumber, string $razorpayOrderId): void
    {
        $stmt = $this->db->prepare('UPDATE orders SET razorpay_order_id=:razorpay_order_id WHERE order_number=:order_number AND payment_method="razorpay" AND payment_status="pending"');
        $stmt->execute(['razorpay_order_id' => $razorpayOrderId, 'order_number' => $orderNumber]);
        if ($stmt->rowCount() === 0) throw new RuntimeException('Unable to attach Razorpay order to pending order');
    }

    public function markPaymentFailed(string $orderNumber): void
    {
        $order = $this->findByOrderNumber($orderNumber);
        $this->execute('UPDATE orders SET payment_status="failed", order_status="cancelled" WHERE order_number=:order_number AND payment_status="pending"', ['order_number' => $orderNumber]);
        if ($order) $this->insertTimeline((int)$order['id'], 'cancelled', 'Payment Failed', 'Payment was cancelled or failed.');
    }

    public function confirmPaidAndDeductStock(string $orderNumber, string $razorpayOrderId, string $razorpayPaymentId, float $paidAmount, ?string $signature = null, array $rawPayment = []): void
    {
        $this->db->beginTransaction();
        try {
            $order = $this->fetchOne('SELECT * FROM orders WHERE order_number=:order_number FOR UPDATE', ['order_number' => $orderNumber]);
            if (!$order) throw new RuntimeException('Order not found');
            if ($order['payment_status'] === 'paid') { $this->db->commit(); return; }
            if ($order['payment_method'] !== 'razorpay') throw new RuntimeException('Invalid payment method for verification');
            if ($order['payment_status'] !== 'pending') throw new RuntimeException('Order is not pending payment');
            if (($order['razorpay_order_id'] ?? '') !== $razorpayOrderId) throw new RuntimeException('Razorpay order mismatch');
            if (abs((float)$order['total_amount'] - $paidAmount) > 0.01) throw new RuntimeException('Payment amount mismatch');

            $items = $this->fetchAll('SELECT * FROM order_items WHERE order_id=:order_id', ['order_id' => $order['id']]);
            $this->assertAndDeductStock($items);

            $stmt = $this->db->prepare('UPDATE orders SET payment_status="paid", order_status="confirmed", razorpay_payment_id=:payment_id WHERE id=:id');
            $stmt->execute(['payment_id' => $razorpayPaymentId, 'id' => $order['id']]);

            $this->insertPayment((int)$order['id'], 'razorpay', $razorpayOrderId, $razorpayPaymentId, $signature, $paidAmount, 'captured', $rawPayment);
            $this->insertTimeline((int)$order['id'], 'confirmed', 'Payment Confirmed', 'Razorpay payment verified successfully.');

            if (!empty($order['user_id'])) {
                $this->execute('DELETE FROM cart_items WHERE user_id=:user_id', ['user_id' => (int)$order['user_id']]);
            }
            $this->db->commit();
        } catch (\Throwable $e) {
            $this->db->rollBack();
            throw $e;
        }
    }

    public function listForUser(int $userId): array
    {
        return $this->fetchAll('SELECT o.*, (SELECT pi.image_url FROM order_items oi JOIN product_images pi ON pi.product_id=oi.product_id WHERE oi.order_id=o.id ORDER BY pi.sort_order, pi.id LIMIT 1) AS product_thumbnail FROM orders o WHERE o.user_id=:user_id ORDER BY o.created_at DESC', ['user_id'=>$userId]);
    }

    public function detailForUser(int $userId, string $orderNumber): ?array
    {
        $order = $this->fetchOne('SELECT * FROM orders WHERE user_id=:user_id AND order_number=:order_number LIMIT 1', ['user_id'=>$userId,'order_number'=>$orderNumber]);
        if (!$order) return null;
        $order['items'] = $this->fetchAll('SELECT * FROM order_items WHERE order_id=:id', ['id'=>$order['id']]);
        $order['timeline'] = $this->fetchAll('SELECT status,title,note,created_at FROM order_timeline WHERE order_id=:id ORDER BY created_at ASC', ['id'=>$order['id']]);
        $order['payment'] = $this->fetchOne('SELECT provider,provider_order_id,provider_payment_id,amount,currency,status,created_at FROM payments WHERE order_id=:id ORDER BY id DESC LIMIT 1', ['id'=>$order['id']]);
        return $order;
    }

    public function adminList(array $filters = []): array
    {
        $sql = 'SELECT o.*, u.name AS user_name, u.email AS user_email, (SELECT COUNT(*) FROM order_items oi WHERE oi.order_id=o.id) AS items_count FROM orders o LEFT JOIN users u ON u.id=o.user_id WHERE 1=1';
        $params = [];
        if (!empty($filters['payment_status'])) { $sql .= ' AND o.payment_status=:payment_status'; $params['payment_status']=$filters['payment_status']; }
        if (!empty($filters['order_status'])) { $sql .= ' AND o.order_status=:order_status'; $params['order_status']=$filters['order_status']; }
        if (!empty($filters['q'])) {
    $sql .= ' AND (o.order_number LIKE :q1 OR o.customer_phone LIKE :q2 OR o.customer_name LIKE :q3)';
    $params['q1'] = $params['q2'] = $params['q3'] = '%' . $filters['q'] . '%';
}
        $sql .= ' ORDER BY o.created_at DESC LIMIT 200';
        return $this->fetchAll($sql, $params);
    }

    public function detailById(int $id): ?array
    {
        $order = $this->fetchOne('SELECT * FROM orders WHERE id=:id LIMIT 1', ['id'=>$id]);
        if (!$order) return null;
        $order['items'] = $this->fetchAll('SELECT * FROM order_items WHERE order_id=:id', ['id'=>$id]);
        $order['timeline'] = $this->fetchAll('SELECT status,title,note,created_at FROM order_timeline WHERE order_id=:id ORDER BY created_at ASC', ['id'=>$id]);
        $order['payment'] = $this->fetchOne('SELECT * FROM payments WHERE order_id=:id ORDER BY id DESC LIMIT 1', ['id'=>$id]);
        return $order;
    }

    public function updateStatus(int $id, string $status): void
    {
        $allowed = ['payment_pending','confirmed','processing','packed','shipped','out_for_delivery','delivered','cancelled','returned','refunded'];
        if (!in_array($status, $allowed, true)) throw new RuntimeException('Invalid status');
        $this->execute('UPDATE orders SET order_status=:status WHERE id=:id', ['status'=>$status,'id'=>$id]);
        $this->insertTimeline($id, $status, ucwords(str_replace('_',' ', $status)));
    }

   private function assertAndDeductStock(array $items): void
{
    $check = $this->db->prepare('SELECT id, name, stock_quantity FROM products WHERE id=:id AND status="active" FOR UPDATE');
    // Fix: :qty can only appear once in PDO named params
    $deduct = $this->db->prepare('UPDATE products SET stock_quantity = stock_quantity - :qty WHERE id=:id AND stock_quantity >= :min_qty');
    foreach ($items as $item) {
        $productId = (int)$item['product_id'];
        $qty = (int)$item['quantity'];
        $check->execute(['id' => $productId]);
        $product = $check->fetch();
        if (!$product) throw new RuntimeException('Invalid product in order');
        if ((int)$product['stock_quantity'] < $qty) throw new RuntimeException($product['name'] . ' does not have enough stock');
        $deduct->execute(['qty' => $qty, 'id' => $productId, 'min_qty' => $qty]); // ← min_qty instead of second :qty
        if ($deduct->rowCount() === 0) throw new RuntimeException($product['name'] . ' stock update failed');
    }
}

    private function insertTimeline(int $orderId, string $status, string $title, ?string $note = null): void
    {
        $stmt = $this->db->prepare('INSERT INTO order_timeline(order_id,status,title,note) VALUES(:order_id,:status,:title,:note)');
        $stmt->execute(['order_id'=>$orderId,'status'=>$status,'title'=>$title,'note'=>$note]);
    }

    private function insertPayment(int $orderId, string $provider, ?string $providerOrderId, ?string $providerPaymentId, ?string $signature, float $amount, string $status, array $raw = []): void
    {
        $stmt = $this->db->prepare('INSERT INTO payments(order_id,provider,provider_order_id,provider_payment_id,provider_signature,amount,currency,status,raw_response) VALUES(:order_id,:provider,:provider_order_id,:provider_payment_id,:provider_signature,:amount,"INR",:status,:raw_response) ON DUPLICATE KEY UPDATE status=VALUES(status), raw_response=VALUES(raw_response), updated_at=NOW()');
        $stmt->execute(['order_id'=>$orderId,'provider'=>$provider,'provider_order_id'=>$providerOrderId,'provider_payment_id'=>$providerPaymentId,'provider_signature'=>$signature,'amount'=>$amount,'status'=>$status,'raw_response'=>json_encode($raw, JSON_UNESCAPED_UNICODE)]);
    }
}
