<?php
/**
 * Database layer for dish view / click statistics.
 *
 * @package Maca_Menu_List
 */

if (!defined('ABSPATH')) {
    exit;
}

/**
 * Dish view events table name.
 *
 * @return string
 */
function maca_menulist_db_dish_views_table() {
    global $wpdb;

    return $wpdb->prefix . 'maca_dish_views';
}

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.SchemaChange

/**
 * Create dish views table.
 */
function maca_menulist_db_create_dish_views_table() {
    global $wpdb;

    if (!isset($wpdb) || empty($wpdb)) {
        return;
    }

    $charset_collate = $wpdb->get_charset_collate();
    $table = maca_menulist_db_dish_views_table();

    $sql = "CREATE TABLE $table (
        id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
        dish_id mediumint(9) NOT NULL,
        event_type varchar(20) NOT NULL DEFAULT 'click',
        viewer_key varchar(64) DEFAULT NULL,
        created_at datetime DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY  (id),
        KEY dish_id (dish_id),
        KEY created_at (created_at),
        KEY dish_event_date (dish_id, event_type, created_at)
    ) $charset_collate;";

    require_once ABSPATH . 'wp-admin/includes/upgrade.php';
    dbDelta($sql);
}

/**
 * Ensure dish views table exists.
 */
function maca_menulist_db_ensure_dish_views_table() {
    global $wpdb;

    static $ensured = false;
    if ($ensured) {
        return;
    }

    $table = maca_menulist_db_dish_views_table();
    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
    $exists = $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $table));

    if ($exists !== $table) {
        maca_menulist_db_create_dish_views_table();
    }

    $ensured = true;
}

/**
 * Valid dish view event types.
 *
 * @return array<int, string>
 */
function maca_menulist_dish_view_event_types() {
    return array('click_image', 'click_card', 'add_order');
}

/**
 * Record a dish view / click event.
 *
 * @param int    $dish_id    Dish ID.
 * @param string $event_type Event type key.
 * @param string $viewer_key Optional guest key for diagnostics.
 * @return bool
 */
function maca_menulist_db_record_dish_view($dish_id, $event_type = 'click_card', $viewer_key = '') {
    global $wpdb;

    maca_menulist_db_ensure_dish_views_table();

    $dish_id = absint($dish_id);
    if ($dish_id <= 0) {
        return false;
    }

    $event_type = sanitize_key($event_type);
    if (!in_array($event_type, maca_menulist_dish_view_event_types(), true)) {
        $event_type = 'click_card';
    }

    $viewer_key = sanitize_text_field($viewer_key);
    if (strlen($viewer_key) > 64) {
        $viewer_key = substr($viewer_key, 0, 64);
    }

    $dish = maca_menulist_db_get_dish($dish_id);
    if (!$dish) {
        return false;
    }

    $row = array(
        'dish_id' => $dish_id,
        'event_type' => $event_type,
        'viewer_key' => $viewer_key !== '' ? $viewer_key : null,
        'created_at' => maca_menulist_wp_now_mysql(),
    );

    $formats = array('%d', '%s', '%s', '%s');

    return (bool) $wpdb->insert(maca_menulist_db_dish_views_table(), $row, $formats);
}

/**
 * Normalize dashboard / stats date range (site timezone).
 *
 * @param string $date_from Y-m-d or empty.
 * @param string $date_to   Y-m-d or empty.
 * @return array{date_from: string, date_to: string, label: string}
 */
function maca_menulist_normalize_stats_date_range($date_from = '', $date_to = '') {
    $today = wp_date('Y-m-d');
    $date_from = sanitize_text_field($date_from);
    $date_to = sanitize_text_field($date_to);

    if ($date_from === '' && $date_to === '') {
        $date_from = $today;
        $date_to = $today;
    } elseif ($date_from === '') {
        $date_from = $date_to;
    } elseif ($date_to === '') {
        $date_to = $date_from;
    }

    if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $date_from)) {
        $date_from = $today;
    }
    if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $date_to)) {
        $date_to = $today;
    }

    if ($date_from > $date_to) {
        $swap = $date_from;
        $date_from = $date_to;
        $date_to = $swap;
    }

    if ($date_from === $date_to) {
        $label = maca_menulist_format_wp_date($date_from);
    } else {
        $label = sprintf(
            '%s – %s',
            maca_menulist_format_wp_date($date_from),
            maca_menulist_format_wp_date($date_to)
        );
    }

    return array(
        'date_from' => $date_from,
        'date_to' => $date_to,
        'label' => $label,
    );
}

/**
 * Top dishes by guest clicks/views in a date range.
 *
 * @param string $date_from Start date Y-m-d.
 * @param string $date_to   End date Y-m-d.
 * @param int    $limit     Max rows.
 * @return array<int, array{dish_id: int, title: string, clicks: int, edit_url: string}>
 */
function maca_menulist_db_get_top_dishes_by_clicks($date_from, $date_to, $limit = 5) {
    global $wpdb;

    if (!maca_menulist_is_dishes_mode()) {
        return array();
    }

    maca_menulist_db_ensure_dish_views_table();

    $range = maca_menulist_normalize_stats_date_range($date_from, $date_to);
    $date_from = $range['date_from'];
    $date_to = $range['date_to'];
    $limit = max(1, min(100, (int) $limit));

    $views_table = maca_menulist_db_dish_views_table();
    $dishes_table = maca_menulist_db_dishes_table();

    $rows = $wpdb->get_results(
        $wpdb->prepare(
            "SELECT v.dish_id, COUNT(*) AS click_count, d.title, d.title_en
            FROM $views_table v
            INNER JOIN $dishes_table d ON d.id = v.dish_id
            WHERE v.created_at >= %s AND v.created_at <= %s
            GROUP BY v.dish_id, d.title, d.title_en
            ORDER BY click_count DESC, d.title ASC
            LIMIT %d",
            $date_from . ' 00:00:00',
            $date_to . ' 23:59:59',
            $limit
        )
    );

    $out = array();
    foreach ($rows as $row) {
        $dish_id = (int) $row->dish_id;
        $out[] = array(
            'dish_id' => $dish_id,
            'title' => maca_menulist_get_dish_display_title($row),
            'clicks' => (int) $row->click_count,
            'edit_url' => admin_url('admin.php?page=maca-restu-dishes&action=edit&dish_id=' . $dish_id),
        );
    }

    return $out;
}

/**
 * Order line stats per dish for a date range.
 *
 * @param string $date_from Start date Y-m-d.
 * @param string $date_to   End date Y-m-d.
 * @return array<int, array{qty: int, orders: int, revenue: float}>
 */
function maca_menulist_db_get_dish_order_stats_for_range($date_from, $date_to) {
    global $wpdb;

    if (!maca_menulist_is_dishes_mode() || !maca_menulist_table_order_enabled()) {
        return array();
    }

    maca_menulist_db_ensure_table_order_tables();

    $range = maca_menulist_normalize_stats_date_range($date_from, $date_to);
    $orders_table = maca_menulist_db_table_orders_table();
    $lines_table = maca_menulist_db_table_order_lines_table();

    $rows = $wpdb->get_results(
        $wpdb->prepare(
            "SELECT l.dish_id,
                COALESCE(SUM(l.qty), 0) AS qty_sold,
                COUNT(DISTINCT l.order_id) AS order_count,
                COALESCE(SUM(l.qty * COALESCE(l.price, 0)), 0) AS line_revenue
            FROM $lines_table l
            INNER JOIN $orders_table o ON o.id = l.order_id
            WHERE l.dish_id IS NOT NULL AND l.dish_id > 0
            AND o.created_at >= %s AND o.created_at <= %s
            GROUP BY l.dish_id",
            $range['date_from'] . ' 00:00:00',
            $range['date_to'] . ' 23:59:59'
        )
    );

    $stats = array();
    foreach ($rows as $row) {
        $dish_id = (int) $row->dish_id;
        $stats[ $dish_id ] = array(
            'qty' => (int) $row->qty_sold,
            'orders' => (int) $row->order_count,
            'revenue' => round((float) $row->line_revenue, 2),
        );
    }

    return $stats;
}

/**
 * Full dish statistics for admin (clicks + orders in range).
 *
 * @param string $date_from Start date Y-m-d.
 * @param string $date_to   End date Y-m-d.
 * @param string $orderby   clicks|orders|revenue|title.
 * @return array<int, array<string, mixed>>
 */
function maca_menulist_db_get_dish_statistics_rows($date_from, $date_to, $orderby = 'clicks') {
    global $wpdb;

    if (!maca_menulist_is_dishes_mode()) {
        return array();
    }

    maca_menulist_db_ensure_dish_views_table();

    $range = maca_menulist_normalize_stats_date_range($date_from, $date_to);
    $date_from = $range['date_from'];
    $date_to = $range['date_to'];

    $dishes = maca_menulist_db_get_dishes(false);
    $order_stats = maca_menulist_db_get_dish_order_stats_for_range($date_from, $date_to);

    $views_table = maca_menulist_db_dish_views_table();
    $click_rows = $wpdb->get_results(
        $wpdb->prepare(
            "SELECT dish_id, COUNT(*) AS click_count
            FROM $views_table
            WHERE created_at >= %s AND created_at <= %s
            GROUP BY dish_id",
            $date_from . ' 00:00:00',
            $date_to . ' 23:59:59'
        )
    );

    $clicks_by_dish = array();
    foreach ($click_rows as $row) {
        $clicks_by_dish[ (int) $row->dish_id ] = (int) $row->click_count;
    }

    $rows = array();
    foreach ($dishes as $dish) {
        $dish_id = (int) $dish->id;
        $clicks = isset($clicks_by_dish[ $dish_id ]) ? $clicks_by_dish[ $dish_id ] : 0;
        $orders = isset($order_stats[ $dish_id ]) ? $order_stats[ $dish_id ] : array(
            'qty' => 0,
            'orders' => 0,
            'revenue' => 0.0,
        );

        if (!(int) $dish->is_active && $clicks === 0 && $orders['qty'] === 0) {
            continue;
        }

        $rows[] = array(
            'dish_id' => $dish_id,
            'title' => maca_menulist_get_dish_display_title($dish),
            'is_active' => (bool) $dish->is_active,
            'clicks' => $clicks,
            'qty_sold' => (int) $orders['qty'],
            'order_count' => (int) $orders['orders'],
            'revenue' => (float) $orders['revenue'],
            'revenue_label' => maca_menulist_format_price($orders['revenue']),
            'edit_url' => admin_url('admin.php?page=maca-restu-dishes&action=edit&dish_id=' . $dish_id),
        );
    }

    $orderby = sanitize_key($orderby);
    usort(
        $rows,
        static function ($a, $b) use ($orderby) {
            switch ($orderby) {
                case 'orders':
                    return $b['qty_sold'] <=> $a['qty_sold'] ?: strcmp($a['title'], $b['title']);
                case 'revenue':
                    return $b['revenue'] <=> $a['revenue'] ?: strcmp($a['title'], $b['title']);
                case 'title':
                    return strcmp($a['title'], $b['title']);
                case 'clicks':
                default:
                    return $b['clicks'] <=> $a['clicks'] ?: strcmp($a['title'], $b['title']);
            }
        }
    );

    return $rows;
}

/**
 * Total click count in range.
 *
 * @param string $date_from Start date Y-m-d.
 * @param string $date_to   End date Y-m-d.
 * @return int
 */
function maca_menulist_db_count_dish_clicks_in_range($date_from, $date_to) {
    global $wpdb;

    maca_menulist_db_ensure_dish_views_table();

    $range = maca_menulist_normalize_stats_date_range($date_from, $date_to);
    $table = maca_menulist_db_dish_views_table();

    return (int) $wpdb->get_var(
        $wpdb->prepare(
            "SELECT COUNT(*) FROM $table WHERE created_at >= %s AND created_at <= %s",
            $range['date_from'] . ' 00:00:00',
            $range['date_to'] . ' 23:59:59'
        )
    );
}

/**
 * Drop dish views table on uninstall.
 */
function maca_menulist_db_drop_dish_views_table() {
    global $wpdb;

    $wpdb->query('DROP TABLE IF EXISTS ' . maca_menulist_db_dish_views_table());
}

// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.SchemaChange
