package com.tourguide.modules.dashboard.mapper;

import com.tourguide.modules.dashboard.dto.DashboardOrderTypeRow;
import com.tourguide.modules.dashboard.dto.DashboardScenicRow;
import com.tourguide.modules.dashboard.dto.DashboardTrendRow;
import org.apache.ibatis.annotations.Select;
import java.math.BigDecimal;
import java.util.List;

public interface DashboardMapper {
    @Select("SELECT COALESCE(SUM(COALESCE(payment_amount, amount, 0)), 0) " +
            "FROM tour_order WHERE deleted = 0 AND payment_time IS NOT NULL " +
            "AND (refund_status IS NULL OR refund_status IN ('NONE', 'REFUND_FAILED')) " +
            "AND status <> 'REFUNDED'")
    BigDecimal selectTotalRevenue();

    @Select({
            "WITH RECURSIVE days AS (",
            " SELECT CURDATE() - INTERVAL 6 DAY AS day_value",
            " UNION ALL SELECT day_value + INTERVAL 1 DAY FROM days WHERE day_value < CURDATE()",
            ")",
            "SELECT DATE_FORMAT(days.day_value, '%m-%d') AS date_label,",
            " COALESCE(SUM(CASE WHEN orders.payment_time IS NOT NULL",
            " AND (orders.refund_status IS NULL OR orders.refund_status IN ('NONE', 'REFUND_FAILED'))",
            " AND orders.status <> 'REFUNDED' THEN COALESCE(orders.payment_amount, orders.amount, 0)",
            " ELSE 0 END), 0) AS revenue",
            "FROM days LEFT JOIN tour_order orders",
            " ON orders.deleted = 0 AND DATE(COALESCE(orders.payment_time, orders.placed_at, orders.created_at)) = days.day_value",
            "GROUP BY days.day_value ORDER BY days.day_value"
    })
    List<DashboardTrendRow> selectRevenueTrend();

    @Select({
            "SELECT scenic_name, COUNT(*) AS order_count,",
            " SUM(CASE WHEN DATE(created_at) = CURDATE() THEN 1 ELSE 0 END) AS today_count,",
            " SUM(CASE WHEN DATE(created_at) = CURDATE() - INTERVAL 1 DAY THEN 1 ELSE 0 END) AS yesterday_count",
            "FROM tour_order",
            "WHERE deleted = 0 AND scenic_name IS NOT NULL AND scenic_name <> ''",
            " AND status NOT IN ('CANCELLED', 'REFUNDED')",
            " AND created_at >= CURDATE() - INTERVAL 30 DAY",
            "GROUP BY scenic_name ORDER BY order_count DESC, scenic_name LIMIT 5"
    })
    List<DashboardScenicRow> selectScenicRanking();

    @Select({
            "SELECT order_type, COUNT(*) AS order_count FROM tour_order",
            "WHERE deleted = 0 AND status NOT IN ('CANCELLED', 'REFUNDED')",
            "GROUP BY order_type ORDER BY order_count DESC"
    })
    List<DashboardOrderTypeRow> selectOrderTypeStats();
}