package com.talentcard.server.mapper;

import static org.assertj.core.api.Assertions.assertThat;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.ibatis.mapping.Environment;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.apache.ibatis.transaction.jdbc.JdbcTransactionFactory;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

public class MerchantSettlementStatisticsMapperTests {

    private SqlSessionFactory sessionFactory;

    @BeforeEach
    void setUp() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource(
                "jdbc:h2:mem:merchant-settlement-statistics-" + System.nanoTime()
                        + ";MODE=MySQL;DATABASE_TO_LOWER=TRUE;DB_CLOSE_DELAY=-1", "sa", "");
        createSchema(dataSource);
        insertFixture(dataSource);

        Environment environment = new Environment("test", new JdbcTransactionFactory(), dataSource);
        Configuration configuration = new Configuration(environment);
        configuration.addMapper(MerchantSettlementStatisticsMapper.class);
        sessionFactory = new SqlSessionFactoryBuilder().build(configuration);
    }

    @Test
    void includesHotelFreeOfflineSubsidyWithoutIncreasingTransactionAmount() {
        LocalDateTime start = LocalDate.now().atStartOfDay();
        LocalDateTime end = LocalDate.now().plusDays(1).atStartOfDay();

        try (SqlSession session = sessionFactory.openSession()) {
            MerchantSettlementStatisticsMapper mapper = session.getMapper(MerchantSettlementStatisticsMapper.class);

            List<Map<String, Object>> summaries = mapper.selectPeriodSummaries(
                    88L, "HOTEL", "DAY", start, end, 20, 0);
            assertThat(summaries).hasSize(1);
            assertTotals(summaries.get(0));
            assertThat(mapper.countPeriodSummaries(88L, "HOTEL", "DAY", start, end)).isEqualTo(1L);

            List<Map<String, Object>> overview = mapper.selectMerchantOverviews(88L, "HOTEL", start, end);
            assertThat(overview).hasSize(1);
            assertTotals(overview.get(0));

            Map<String, Object> cards = mapper.selectDashboardCards(88L, start, end, start, end);
            assertThat(longValue(cards, "transactionAmountCents")).isEqualTo(10_000L);
            assertThat(longValue(cards, "subsidyAmountCents")).isEqualTo(28_850L);
            assertThat(longValue(cards, "todayVerificationCount")).isEqualTo(2L);

            List<Map<String, Object>> details = mapper.selectSettlementExportDetails(88L, 1, 1, start, end, 20);
            assertThat(details).hasSize(2);
            Map<String, Object> freeStay = details.stream()
                    .filter(row -> "HOTEL_FREE".equals(String.valueOf(value(row, "orderType"))))
                    .findFirst().orElseThrow();
            assertThat(longValue(freeStay, "transactionAmountCents")).isZero();
            assertThat(longValue(freeStay, "subsidyAmountCents")).isEqualTo(26_850L);
            assertThat(longValue(freeStay, "settlementAmountCents")).isEqualTo(26_850L);
        }
    }

    private void assertTotals(Map<String, Object> row) {
        assertThat(longValue(row, "orderCount")).isEqualTo(2L);
        assertThat(longValue(row, "transactionAmountCents")).isEqualTo(10_000L);
        assertThat(longValue(row, "subsidyAmountCents")).isEqualTo(28_850L);
        assertThat(longValue(row, "settlementAmountCents")).isEqualTo(38_850L);
    }

    private long longValue(Map<String, Object> row, String key) {
        Object value = value(row, key);
        return value instanceof Number number ? number.longValue() : 0L;
    }

    private Object value(Map<String, Object> row, String key) {
        for (Map.Entry<String, Object> entry : row.entrySet()) {
            if (entry.getKey().equalsIgnoreCase(key)) {
                return entry.getValue();
            }
        }
        return null;
    }

    private void createSchema(DataSource dataSource) {
        JdbcTemplate jdbc = new JdbcTemplate(dataSource);
        jdbc.execute("create alias if not exists date_format for 'com.talentcard.server.mapper.MerchantSettlementStatisticsMapperTests.dateFormat'");
        jdbc.execute("""
                create table merchant (
                    id bigint primary key, merchant_name varchar(128), merchant_type varchar(64),
                    merchant_type_code varchar(64), deleted tinyint not null default 0
                )
                """);
        jdbc.execute("""
                create table benefit_order (
                    id bigint primary key, order_no varchar(64), order_type varchar(64), merchant_id bigint,
                    store_id bigint, talent_id bigint, card_id bigint, benefit_id bigint, product_id bigint,
                    coupon_id bigint, product_name_snapshot varchar(255), order_amount bigint, pay_amount bigint,
                    coupon_amount bigint, subsidy_amount bigint, order_status tinyint, verify_status tinyint,
                    created_at timestamp, updated_at timestamp, deleted tinyint not null default 0
                )
                """);
        jdbc.execute("""
                create table verify_record (
                    id bigint primary key, verify_no varchar(64), order_id bigint, talent_id bigint, card_id bigint,
                    merchant_id bigint, store_id bigint, staff_id bigint, verify_type varchar(64), verify_amount bigint,
                    subsidy_amount bigint, verify_time timestamp, status tinyint not null default 1
                )
                """);
        jdbc.execute("create table merchant_product (id bigint primary key, product_name varchar(255), is_government_subsidy tinyint, deleted tinyint)");
        jdbc.execute("create table merchant_coupon (id bigint primary key, government_subsidy tinyint, deleted tinyint)");
        jdbc.execute("create table merchant_store (id bigint primary key, store_name varchar(128), deleted tinyint)");
        jdbc.execute("create table talent_user (id bigint primary key, name varchar(64), mobile varchar(32), deleted tinyint)");
        jdbc.execute("create table talent_card (id bigint primary key, card_no varchar(64), card_level varchar(64), deleted tinyint)");
    }

    private void insertFixture(DataSource dataSource) {
        JdbcTemplate jdbc = new JdbcTemplate(dataSource);
        LocalDateTime now = LocalDateTime.now();
        jdbc.update("insert into merchant(id, merchant_name, merchant_type, merchant_type_code, deleted) values (88, '测试酒店', '酒店服务', 'HOTEL', 0)");
        jdbc.update("insert into merchant_store(id, store_name, deleted) values (7, '酒店门店', 0)");
        jdbc.update("insert into talent_user(id, name, mobile, deleted) values (1015, '测试人才', '13800000000', 0)");
        jdbc.update("insert into talent_card(id, card_no, card_level, deleted) values (10, 'TC001', '卓越人才卡', 0)");
        jdbc.update("""
                insert into benefit_order(id, order_no, order_type, merchant_id, store_id, talent_id, card_id,
                                          product_name_snapshot, order_amount, pay_amount, coupon_amount, subsidy_amount,
                                          order_status, verify_status, created_at, updated_at, deleted)
                values (1, 'O001', 'GROUP_BUY', 88, 7, 1015, 10, '测试团单', 12000, 10000, 0, 2000, 1, 1, ?, ?, 0)
                """, now, now);
        jdbc.update("""
                insert into verify_record(id, verify_no, order_id, talent_id, card_id, merchant_id, store_id, staff_id,
                                          verify_type, verify_amount, subsidy_amount, verify_time, status)
                values (1, 'V001', 1, 1015, 10, 88, 7, 9, 'ORDER_CODE', 10000, 2000, ?, 1)
                """, now);
        jdbc.update("""
                insert into verify_record(id, verify_no, order_id, talent_id, card_id, merchant_id, store_id, staff_id,
                                          verify_type, verify_amount, subsidy_amount, verify_time, status)
                values (2, 'V002', null, 1015, 10, 88, 7, 9, 'HOTEL_FREE', 0, 26850, ?, 1)
                """, now);
    }

    public static String dateFormat(java.sql.Timestamp value, String pattern) {
        DateTimeFormatter formatter = "%Y-%m".equals(pattern)
                ? DateTimeFormatter.ofPattern("yyyy-MM")
                : DateTimeFormatter.ofPattern("yyyy-MM-dd");
        return value.toLocalDateTime().format(formatter);
    }
}