<?php
if ( ! defined( 'ABSPATH' ) ) exit;

class ROS_DB {

    public static function install() {
        global $wpdb;
        $charset = $wpdb->get_charset_collate();
        require_once ABSPATH . 'wp-admin/includes/upgrade.php';

        $wpdb->query( "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}ros_categories (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(100) NOT NULL,
            sort_order INT DEFAULT 0,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP
        ) $charset;" );

        $wpdb->query( "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}ros_dishes (
            id INT AUTO_INCREMENT PRIMARY KEY,
            category_id INT NOT NULL,
            name VARCHAR(150) NOT NULL,
            description TEXT,
            price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
            image_url VARCHAR(500),
            available TINYINT(1) DEFAULT 1,
            sort_order INT DEFAULT 0,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP
        ) $charset;" );

        $wpdb->query( "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}ros_tables (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(50) NOT NULL,
            seats INT DEFAULT 4,
            status ENUM('free','occupied') DEFAULT 'free',
            shape VARCHAR(30) DEFAULT 'rect_4',
            sort_order INT DEFAULT 0
        ) $charset;" );

        $wpdb->query( "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}ros_orders (
            id INT AUTO_INCREMENT PRIMARY KEY,
            table_id INT NOT NULL,
            table_session VARCHAR(64) NOT NULL DEFAULT '',
            session_key VARCHAR(64) NOT NULL,
            status ENUM('pending','accepted','preparing','ready','customer_closed','closed') DEFAULT 'pending',
            total DECIMAL(10,2) DEFAULT 0.00,
            notes TEXT,
            paid_at DATETIME NULL,
            payment_method VARCHAR(50) NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        ) $charset;" );

        $wpdb->query( "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}ros_order_items (
            id INT AUTO_INCREMENT PRIMARY KEY,
            order_id INT NOT NULL,
            dish_id INT NOT NULL,
            dish_name VARCHAR(150) NOT NULL,
            dish_price DECIMAL(10,2) NOT NULL,
            quantity INT NOT NULL DEFAULT 1,
            subtotal DECIMAL(10,2) NOT NULL DEFAULT 0.00
        ) $charset;" );

        // Reservations table
        $wpdb->query( "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}ros_reservations (
            id INT AUTO_INCREMENT PRIMARY KEY,
            table_id INT NOT NULL,
            guest_name VARCHAR(100) NOT NULL,
            arrival_time DATETIME NOT NULL,
            persons INT NOT NULL DEFAULT 1,
            notes TEXT,
            status ENUM('active','released') DEFAULT 'active',
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP
        ) $charset;" );

        // Receipts archive — one row per table session (visit)
        $wpdb->query( "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}ros_receipts (
            id INT AUTO_INCREMENT PRIMARY KEY,
            table_id INT NOT NULL,
            table_name VARCHAR(50) NOT NULL,
            table_session VARCHAR(64) NOT NULL,
            payment_method VARCHAR(50) DEFAULT 'cash',
            grand_total DECIMAL(10,2) NOT NULL DEFAULT 0.00,
            order_ids TEXT NOT NULL,
            receipt_data LONGTEXT NOT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP
        ) $charset;" );

        $wpdb->query( "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}ros_settings (
            setting_key VARCHAR(100) PRIMARY KEY,
            setting_value TEXT
        ) $charset;" );


        // Add shape column if missing
        $shape_col = $wpdb->get_col( "SHOW COLUMNS FROM {$wpdb->prefix}ros_tables LIKE 'shape'" );
        if ( empty( $shape_col ) ) {
            $wpdb->query( "ALTER TABLE {$wpdb->prefix}ros_tables ADD COLUMN shape VARCHAR(30) DEFAULT 'rect_4' AFTER seats" );
        }
        // Migrate existing installs
        $cols = $wpdb->get_col( "SHOW COLUMNS FROM {$wpdb->prefix}ros_orders LIKE 'table_session'" );
        if ( empty( $cols ) ) {
            $wpdb->query( "ALTER TABLE {$wpdb->prefix}ros_orders ADD COLUMN table_session VARCHAR(64) NOT NULL DEFAULT '' AFTER table_id" );
        }
        $cols2 = $wpdb->get_col( "SHOW COLUMNS FROM {$wpdb->prefix}ros_orders LIKE 'paid_at'" );
        if ( empty( $cols2 ) ) {
            $wpdb->query( "ALTER TABLE {$wpdb->prefix}ros_orders ADD COLUMN paid_at DATETIME NULL, ADD COLUMN payment_method VARCHAR(50) NULL" );
        }
        $wpdb->query( "ALTER TABLE {$wpdb->prefix}ros_orders MODIFY COLUMN status ENUM('pending','accepted','preparing','ready','customer_closed','closed') DEFAULT 'pending'" );

        // Default tables
        $count = $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->prefix}ros_tables" );
        if ( $count == 0 ) {
            for ( $i = 1; $i <= 5; $i++ ) {
                $wpdb->insert( $wpdb->prefix . 'ros_tables', [
                    'name' => 'Table ' . $i, 'seats' => 4, 'status' => 'free', 'sort_order' => $i,
                ] );
            }
        }

        $defaults = [
            'restaurant_name'    => 'My Restaurant',
            'restaurant_address' => '123 Main Street, City',
            'restaurant_phone'   => '+1 234 567 890',
            'logo_url'           => '',
            'currency_symbol'    => '$',
        ];
        foreach ( $defaults as $key => $value ) {
            $wpdb->query( $wpdb->prepare(
                "INSERT IGNORE INTO {$wpdb->prefix}ros_settings (setting_key, setting_value) VALUES (%s, %s)",
                $key, $value
            ) );
        }
    }

    public static function get_setting( $key ) {
        global $wpdb;
        return $wpdb->get_var( $wpdb->prepare(
            "SELECT setting_value FROM {$wpdb->prefix}ros_settings WHERE setting_key = %s", $key
        ) );
    }

    public static function get_all_settings() {
        global $wpdb;
        $rows = $wpdb->get_results( "SELECT setting_key, setting_value FROM {$wpdb->prefix}ros_settings", ARRAY_A );
        $out  = [];
        foreach ( $rows as $row ) { $out[ $row['setting_key'] ] = $row['setting_value']; }
        return $out;
    }

    public static function get_categories() {
        global $wpdb;
        return $wpdb->get_results( "SELECT * FROM {$wpdb->prefix}ros_categories ORDER BY sort_order, name", ARRAY_A );
    }

    public static function get_dishes( $category_id = null ) {
        global $wpdb;
        if ( $category_id ) {
            return $wpdb->get_results( $wpdb->prepare(
                "SELECT * FROM {$wpdb->prefix}ros_dishes WHERE category_id = %d AND available = 1 ORDER BY sort_order, name",
                $category_id
            ), ARRAY_A );
        }
        return $wpdb->get_results( "SELECT * FROM {$wpdb->prefix}ros_dishes WHERE available = 1 ORDER BY sort_order, name", ARRAY_A );
    }

    public static function get_tables() {
        global $wpdb;
        return $wpdb->get_results( "SELECT * FROM {$wpdb->prefix}ros_tables ORDER BY sort_order", ARRAY_A );
    }

    public static function get_active_orders_for_table( $table_id ) {
        global $wpdb;
        return $wpdb->get_results( $wpdb->prepare(
            "SELECT * FROM {$wpdb->prefix}ros_orders WHERE table_id = %d AND status NOT IN ('closed') ORDER BY id ASC",
            $table_id
        ), ARRAY_A );
    }

    public static function get_active_session( $table_id ) {
        global $wpdb;
        return $wpdb->get_var( $wpdb->prepare(
            "SELECT table_session FROM {$wpdb->prefix}ros_orders WHERE table_id = %d AND status NOT IN ('closed') ORDER BY id ASC LIMIT 1",
            $table_id
        ) );
    }

    public static function get_order_items( $order_id ) {
        global $wpdb;
        return $wpdb->get_results( $wpdb->prepare(
            "SELECT * FROM {$wpdb->prefix}ros_order_items WHERE order_id = %d",
            $order_id
        ), ARRAY_A );
    }

    /** Get all sessions grouped — for admin orders view */
    public static function get_sessions( $limit = 100 ) {
        global $wpdb;
        return $wpdb->get_results(
            "SELECT
                table_session,
                table_id,
                MAX(t.name) as table_name,
                MIN(o.created_at) as started_at,
                MAX(o.updated_at) as last_update,
                SUM(o.total) as grand_total,
                MAX(o.payment_method) as payment_method,
                GROUP_CONCAT(o.id ORDER BY o.id) as order_ids,
                COUNT(o.id) as order_count,
                MAX(o.status) as last_status
             FROM {$wpdb->prefix}ros_orders o
             LEFT JOIN {$wpdb->prefix}ros_tables t ON t.id = o.table_id
             WHERE o.table_session != ''
             GROUP BY o.table_session
             ORDER BY started_at DESC
             LIMIT $limit",
            ARRAY_A
        );
    }

    /** Get all orders in a session */
    public static function get_session_orders( $table_session ) {
        global $wpdb;
        return $wpdb->get_results( $wpdb->prepare(
            "SELECT o.*, t.name as table_name FROM {$wpdb->prefix}ros_orders o
             LEFT JOIN {$wpdb->prefix}ros_tables t ON t.id = o.table_id
             WHERE o.table_session = %s ORDER BY o.id ASC",
            $table_session
        ), ARRAY_A );
    }

    /** Save receipt to archive */
    public static function save_receipt( $table_id, $table_name, $table_session, $payment_method, $grand_total, $order_ids, $receipt_data ) {
        global $wpdb;
        // Check receipts table exists
        $tbl = $wpdb->prefix . 'ros_receipts';
        $exists = $wpdb->get_var( "SHOW TABLES LIKE '$tbl'" );
        if ( ! $exists ) { self::install(); }

        $wpdb->insert( $tbl, [
            'table_id'       => intval( $table_id ),
            'table_name'     => sanitize_text_field( $table_name ),
            'table_session'  => sanitize_text_field( $table_session ),
            'payment_method' => sanitize_text_field( $payment_method ),
            'grand_total'    => floatval( $grand_total ),
            'order_ids'      => sanitize_text_field( $order_ids ),
            'receipt_data'   => wp_json_encode( $receipt_data ),
        ] );
        return $wpdb->insert_id;
    }

    public static function get_receipts( $limit = 100 ) {
        global $wpdb;
        $tbl = $wpdb->prefix . 'ros_receipts';
        $exists = $wpdb->get_var( "SHOW TABLES LIKE '$tbl'" );
        if ( ! $exists ) return [];
        return $wpdb->get_results( "SELECT * FROM $tbl ORDER BY created_at DESC LIMIT $limit", ARRAY_A );
    }

    public static function get_orders_report( $date_from = null, $date_to = null ) {
        global $wpdb;
        $where = "WHERE o.status = 'closed'";
        if ( $date_from ) $where .= $wpdb->prepare( " AND DATE(o.created_at) >= %s", $date_from );
        if ( $date_to )   $where .= $wpdb->prepare( " AND DATE(o.created_at) <= %s", $date_to );
        return $wpdb->get_results(
            "SELECT o.*, t.name as table_name FROM {$wpdb->prefix}ros_orders o
             LEFT JOIN {$wpdb->prefix}ros_tables t ON t.id = o.table_id
             $where ORDER BY o.created_at DESC",
            ARRAY_A
        );
    }
    public static function get_active_reservations() {
        global $wpdb;
        return $wpdb->get_results(
            "SELECT r.*, t.name as table_name FROM {$wpdb->prefix}ros_reservations r
             LEFT JOIN {$wpdb->prefix}ros_tables t ON t.id = r.table_id
             WHERE r.status = 'active' ORDER BY r.arrival_time ASC",
            ARRAY_A
        );
    }

    public static function get_reservation_for_table( $table_id ) {
        global $wpdb;
        return $wpdb->get_row( $wpdb->prepare(
            "SELECT * FROM {$wpdb->prefix}ros_reservations WHERE table_id = %d AND status = 'active' ORDER BY id DESC LIMIT 1",
            $table_id
        ), ARRAY_A );
    }

    public static function get_all_reservations( $limit = 200 ) {
        global $wpdb;
        return $wpdb->get_results(
            "SELECT r.*, t.name as table_name FROM {$wpdb->prefix}ros_reservations r
             LEFT JOIN {$wpdb->prefix}ros_tables t ON t.id = r.table_id
             ORDER BY r.created_at DESC LIMIT $limit",
            ARRAY_A
        );
    }

}
