<?php
class Clearance {
    private $db;

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

    public function getClearanceData($userId) {
        $userId = $this->db->escape($userId);
        $clearance = $this->db->query("SELECT total_sold, clearance_amount FROM clearance WHERE user_id = '$userId'")->row;
        $orders = $this->db->query("SELECT * FROM orders WHERE user_id = '$userId'")->rows;
        return ['clearance' => $clearance, 'orders' => $orders];
    }

    public function getClearanceHistoryByUserId($userId) {
        $userId = $this->db->escape($userId);

        return $this->db->query("
            SELECT *
            FROM clearance_history
            WHERE user_id = '{$userId}'
            ORDER BY created_at DESC
        ")->rows;
    }

    public function getAllClearanceHistories() {
        return $this->db->query("
            SELECT *
            FROM clearance_history
            ORDER BY created_at DESC
        ")->rows;
    }

    public function getUnconfirmedInvoices() {
        return $this->db->query("
            SELECT *
            FROM clearance
        ")->rows;
    }

    public function getFinalOrdersByUserId($userId) {
        $userId = $this->db->escape($userId);

        return $this->db->query("
            SELECT id, item_id, item_name, unit_price, quantity, sold, stock, returned, order_time 
            FROM orders 
            WHERE user_id = '{$userId}' 
            AND (sold > 0 OR returned > 0)  -- Only include items with non-zero sold or returned
            ORDER BY order_time DESC
        ")->rows;
    }

    public function confirmInvoiceById($invoiceId) {
        $invoiceId = $this->db->escape($invoiceId);
        $this->db->query("START TRANSACTION");

        try {
            // Fetch clearance information by invoice ID
            $clearance = $this->db->query("
                SELECT user_id, total_sold, clearance_amount
                FROM clearance
                WHERE id = '{$invoiceId}'
            ")->row;

            if (!$clearance) {
                throw new Exception('No clearance data found for the given ID.');
            }

            // Fetch sold items details to be stored in JSON format
            $soldItemsDetails = $this->getFinalOrdersByUserId($clearance['user_id']);
            // Convert the details to a JSON string
            $soldItemsJson = json_encode($soldItemsDetails);

            // Insert into clearance_history with sold_items details
            $this->db->query("
                INSERT INTO clearance_history (user_id, total_sold, clearance_amount, sold_items)
                VALUES ('{$clearance['user_id']}', '{$clearance['total_sold']}', '{$clearance['clearance_amount']}', '{$soldItemsJson}')
            ");

            // Update orders: reset sold and returned, set quantity back to stock
            $this->db->query("
                UPDATE orders 
                SET sold = 0, returned = 0, quantity = stock 
                WHERE user_id = '{$clearance['user_id']}'
            ");

            // Delete from clearance
            $this->db->query("
                DELETE FROM clearance 
                WHERE id = '{$invoiceId}'
            ");

            $this->db->query("COMMIT");
            return ['success' => true, 'message' => 'Invoice confirmed.'];
        } catch (Exception $e) {
            $this->db->query("ROLLBACK");
            return ['success' => false, 'message' => $e->getMessage()];
        }
    }


    public function getAllUsers() {
        return $this->db->query("SELECT id, username FROM users")->rows;
    }
}