package com.tianting.infoloop.service;

import com.infoloop.tianting.ordersubscriptionmessageservice.CreateOrderSubscriptionMessageRpcRequest;
import com.infoloop.tianting.ordersubscriptionmessageservice.GetUserOrderSubscriptionMessageHistoryRpcRequest;

import java.util.Arrays;
import java.util.List;
import com.tianting.infoloop.App;
import com.tianting.infoloop.model.db.OrderSubscriptionMessageDb;
import org.junit.jupiter.api.BeforeAll;
import org.junit.jupiter.api.Test;
import org.junit.jupiter.api.TestInstance;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.test.context.ActiveProfiles;

import java.util.List;

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

@ActiveProfiles("dev")
@SpringBootTest(classes = App.class)
@TestInstance(TestInstance.Lifecycle.PER_CLASS)
class OrderSubscriptionMessageDbServiceTest {

  @Autowired
  private JdbcTemplate jdbcTemplate;

  @Autowired
  private OrderSubscriptionMessageDbService orderSubscriptionMessageDbService;

  @BeforeAll
  void ensureTable() {
    jdbcTemplate.execute(
        "CREATE TABLE IF NOT EXISTS order_subscription_message ("
            + "id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',"
            + "enterpriseId INT NOT NULL COMMENT '企业ID',"
            + "dinerId INT DEFAULT NULL COMMENT '订餐用户ID（dinerId）',"
            + "openId VARCHAR(255) NOT NULL COMMENT '微信用户OpenID',"
            + "templateId VARCHAR(255) NOT NULL COMMENT '订阅消息模板ID',"
            + "jumpPath VARCHAR(255) DEFAULT NULL COMMENT '跳转路径（小程序 path）',"
            + "orderPeriodStartDate DATETIME DEFAULT NULL COMMENT '关联的订餐周期开始时间（精确到秒）',"
            + "orderPeriodEndDate DATETIME DEFAULT NULL COMMENT '关联的订餐周期结束时间（精确到秒）',"
            + "createdAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',"
            + "updatedAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',"
            + "isDeleted TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否删除；0：未删除，1：已删除',"
            + "PRIMARY KEY (id),"
            + "KEY idx_enterprise_id (enterpriseId)"
            + ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订餐订阅消息授权表';");

    // 兼容本地已存在的旧表结构（修改字段类型为 DATETIME，精确到秒）
    Integer startDateType = jdbcTemplate.queryForObject(
        "SELECT COUNT(1) FROM information_schema.COLUMNS "
            + "WHERE table_schema = DATABASE() "
            + "AND table_name = 'order_subscription_message' "
            + "AND column_name = 'orderPeriodStartDate' "
            + "AND data_type IN ('date', 'bigint')",
        Integer.class);
    if (startDateType != null && startDateType > 0) {
      // 如果字段是 bigint，需要先清空数据或转换数据，这里先清空（测试环境）
      jdbcTemplate.update("UPDATE order_subscription_message SET orderPeriodStartDate = NULL WHERE orderPeriodStartDate IS NOT NULL");
      jdbcTemplate.execute(
          "ALTER TABLE order_subscription_message "
              + "MODIFY COLUMN orderPeriodStartDate DATETIME DEFAULT NULL COMMENT '关联的订餐周期开始时间（精确到秒）'");
    }
    
    Integer endDateExists = jdbcTemplate.queryForObject(
        "SELECT COUNT(1) FROM information_schema.COLUMNS "
            + "WHERE table_schema = DATABASE() "
            + "AND table_name = 'order_subscription_message' "
            + "AND column_name = 'orderPeriodEndDate'",
        Integer.class);
    if (endDateExists == null || endDateExists == 0) {
      jdbcTemplate.execute(
          "ALTER TABLE order_subscription_message "
              + "ADD COLUMN orderPeriodEndDate DATETIME DEFAULT NULL COMMENT '关联的订餐周期结束时间（精确到秒）' "
              + "AFTER orderPeriodStartDate");
    } else {
      Integer endDateType = jdbcTemplate.queryForObject(
          "SELECT COUNT(1) FROM information_schema.COLUMNS "
              + "WHERE table_schema = DATABASE() "
              + "AND table_name = 'order_subscription_message' "
              + "AND column_name = 'orderPeriodEndDate' "
              + "AND data_type IN ('date', 'bigint')",
          Integer.class);
      if (endDateType != null && endDateType > 0) {
        // 如果字段是 bigint，需要先清空数据或转换数据，这里先清空（测试环境）
        jdbcTemplate.update("UPDATE order_subscription_message SET orderPeriodEndDate = NULL WHERE orderPeriodEndDate IS NOT NULL");
        jdbcTemplate.execute(
            "ALTER TABLE order_subscription_message "
                + "MODIFY COLUMN orderPeriodEndDate DATETIME DEFAULT NULL COMMENT '关联的订餐周期结束时间（精确到秒）'");
      }
    }
  }

  @Test
  void shouldCreateAndQueryHistory() {
    final String openId = "openId_test_junit_001";

    // clean
    jdbcTemplate.update("DELETE FROM order_subscription_message WHERE enterpriseId=? AND openId=?", 1, openId);

    // create
    CreateOrderSubscriptionMessageRpcRequest createReq = CreateOrderSubscriptionMessageRpcRequest.newBuilder()
        .setEnterpriseId(1)
        .setDinerId(100)
        .setOpenId(openId)
        .setTemplateId("template_test_junit_001")
        .setJumpPath("/pages/order/index")
        .setOrderPeriodStartDate(1738512000000L) // 2025-02-03 00:00:00 (毫秒时间戳，转换为 LocalDateTime)
        .setOrderPeriodEndDate(1739030400000L) // 2025-02-09 00:00:00 (毫秒时间戳，转换为 LocalDateTime)
        .build();

    OrderSubscriptionMessageDb created = orderSubscriptionMessageDbService.createOrderSubscriptionMessage(createReq);
    assertThat(created.getId()).isNotNull();
    assertThat(created.getEnterpriseId()).isEqualTo(1);
    assertThat(created.getDinerId()).isEqualTo(100);
    assertThat(created.getJumpPath()).isEqualTo("/pages/order/index");
    assertThat(created.getOrderPeriodEndDate()).isNotNull();

    // query history
    GetUserOrderSubscriptionMessageHistoryRpcRequest historyReq = GetUserOrderSubscriptionMessageHistoryRpcRequest.newBuilder()
        .setEnterpriseId(1)
        .setOpenId(openId)
        .setOrderPeriodStartDate(0)
        .build();

    List<OrderSubscriptionMessageDb> list = orderSubscriptionMessageDbService.getUserOrderSubscriptionMessageHistory(historyReq);
    assertThat(list).isNotEmpty();
    assertThat(list.get(0).getOpenId()).isEqualTo(openId);
    assertThat(list.get(0).getJumpPath()).isEqualTo("/pages/order/index");
    assertThat(list.get(0).getOrderPeriodEndDate()).isNotNull();
  }

  @Test
  void shouldQueryByOrderPeriodStartDateWithGreaterEqual() {
    final String openId = "openId_test_junit_date_query";

    // clean
    jdbcTemplate.update("DELETE FROM order_subscription_message WHERE enterpriseId=? AND openId=?", 1, openId);

    // 创建多条不同开始时间的记录
    // 记录1: 2025-02-03 00:00:00
    CreateOrderSubscriptionMessageRpcRequest createReq1 = CreateOrderSubscriptionMessageRpcRequest.newBuilder()
        .setEnterpriseId(1)
        .setDinerId(100)
        .setOpenId(openId)
        .setTemplateId("template_test_date_1")
        .setJumpPath("/pages/order/index")
        .setOrderPeriodStartDate(1738512000000L) // 2025-02-03 00:00:00
        .setOrderPeriodEndDate(1739030400000L) // 2025-02-09 00:00:00
        .build();
    OrderSubscriptionMessageDb created1 = orderSubscriptionMessageDbService.createOrderSubscriptionMessage(createReq1);

    // 记录2: 2025-02-10 00:00:00 (晚于记录1)
    CreateOrderSubscriptionMessageRpcRequest createReq2 = CreateOrderSubscriptionMessageRpcRequest.newBuilder()
        .setEnterpriseId(1)
        .setDinerId(100)
        .setOpenId(openId)
        .setTemplateId("template_test_date_2")
        .setJumpPath("/pages/order/index")
        .setOrderPeriodStartDate(1739116800000L) // 2025-02-10 00:00:00
        .setOrderPeriodEndDate(1739635200000L) // 2025-02-16 00:00:00
        .build();
    OrderSubscriptionMessageDb created2 = orderSubscriptionMessageDbService.createOrderSubscriptionMessage(createReq2);

    // 记录3: 2025-01-27 00:00:00 (早于记录1)
    CreateOrderSubscriptionMessageRpcRequest createReq3 = CreateOrderSubscriptionMessageRpcRequest.newBuilder()
        .setEnterpriseId(1)
        .setDinerId(100)
        .setOpenId(openId)
        .setTemplateId("template_test_date_3")
        .setJumpPath("/pages/order/index")
        .setOrderPeriodStartDate(1737907200000L) // 2025-01-27 00:00:00
        .setOrderPeriodEndDate(1738425600000L) // 2025-02-02 00:00:00
        .build();
    OrderSubscriptionMessageDb created3 = orderSubscriptionMessageDbService.createOrderSubscriptionMessage(createReq3);

    // 记录4: 2025-02-03 12:00:00 (等于记录1的日期，但时间更晚)
    CreateOrderSubscriptionMessageRpcRequest createReq4 = CreateOrderSubscriptionMessageRpcRequest.newBuilder()
        .setEnterpriseId(1)
        .setDinerId(100)
        .setOpenId(openId)
        .setTemplateId("template_test_date_4")
        .setJumpPath("/pages/order/index")
        .setOrderPeriodStartDate(1738555200000L) // 2025-02-03 12:00:00
        .setOrderPeriodEndDate(1739073600000L) // 2025-02-09 12:00:00
        .build();
    OrderSubscriptionMessageDb created4 = orderSubscriptionMessageDbService.createOrderSubscriptionMessage(createReq4);

    // 测试1: 查询 orderPeriodStartDate >= 2025-02-03 00:00:00
    // 应该返回: 记录1(等于), 记录2(大于), 记录4(大于)
    // 不应该返回: 记录3(小于)
    GetUserOrderSubscriptionMessageHistoryRpcRequest historyReq = GetUserOrderSubscriptionMessageHistoryRpcRequest.newBuilder()
        .setEnterpriseId(1)
        .setOpenId(openId)
        .setOrderPeriodStartDate(1738512000000L) // 2025-02-03 00:00:00
        .build();

    List<OrderSubscriptionMessageDb> list = orderSubscriptionMessageDbService.getUserOrderSubscriptionMessageHistory(historyReq);
    
    // 验证返回的记录数量：应该包含记录1、记录2、记录4，共3条
    assertThat(list.size()).isEqualTo(3);
    
    // 验证所有返回的记录的开始时间都 >= 2025-02-03 00:00:00
    java.time.LocalDateTime threshold = java.time.Instant.ofEpochMilli(1738512000000L)
        .atZone(java.time.ZoneId.systemDefault())
        .toLocalDateTime();
    
    for (OrderSubscriptionMessageDb db : list) {
      assertThat(db.getOrderPeriodStartDate()).isNotNull();
      assertThat(db.getOrderPeriodStartDate().isAfter(threshold) || db.getOrderPeriodStartDate().isEqual(threshold))
          .as("开始时间应该 >= 2025-02-03 00:00:00，实际: %s", db.getOrderPeriodStartDate())
          .isTrue();
    }
    
    // 验证包含记录1（等于）
    assertThat(list.stream().anyMatch(db -> db.getId().equals(created1.getId()))).isTrue();
    
    // 验证包含记录2（大于）
    assertThat(list.stream().anyMatch(db -> db.getId().equals(created2.getId()))).isTrue();
    
    // 验证包含记录4（大于，同一天但时间更晚）
    assertThat(list.stream().anyMatch(db -> db.getId().equals(created4.getId()))).isTrue();
    
    // 验证不包含记录3（小于）
    assertThat(list.stream().anyMatch(db -> db.getId().equals(created3.getId()))).isFalse();

    // 测试2: 查询 orderPeriodStartDate >= 2025-02-10 00:00:00
    // 应该只返回: 记录2(等于)
    GetUserOrderSubscriptionMessageHistoryRpcRequest historyReq2 = GetUserOrderSubscriptionMessageHistoryRpcRequest.newBuilder()
        .setEnterpriseId(1)
        .setOpenId(openId)
        .setOrderPeriodStartDate(1739116800000L) // 2025-02-10 00:00:00
        .build();

    List<OrderSubscriptionMessageDb> list2 = orderSubscriptionMessageDbService.getUserOrderSubscriptionMessageHistory(historyReq2);
    
    // 验证只返回记录2
    assertThat(list2.size()).isEqualTo(1);
    assertThat(list2.get(0).getId()).isEqualTo(created2.getId());
  }

  @Test
  void shouldBatchDeleteByIds() {
    final String openId = "openId_test_junit_delete";

    // clean
    jdbcTemplate.update("DELETE FROM order_subscription_message WHERE enterpriseId=? AND openId=?", 1, openId);

    // create multiple records
    CreateOrderSubscriptionMessageRpcRequest createReq1 = CreateOrderSubscriptionMessageRpcRequest.newBuilder()
        .setEnterpriseId(1)
        .setDinerId(100)
        .setOpenId(openId)
        .setTemplateId("template_test_junit_delete_1")
        .setJumpPath("/pages/order/index")
        .setOrderPeriodStartDate(1738512000000L)
        .setOrderPeriodEndDate(1739030400000L)
        .build();

    CreateOrderSubscriptionMessageRpcRequest createReq2 = CreateOrderSubscriptionMessageRpcRequest.newBuilder()
        .setEnterpriseId(1)
        .setDinerId(100)
        .setOpenId(openId)
        .setTemplateId("template_test_junit_delete_2")
        .setJumpPath("/pages/order/index")
        .setOrderPeriodStartDate(1738512000000L)
        .setOrderPeriodEndDate(1739030400000L)
        .build();

    OrderSubscriptionMessageDb created1 = orderSubscriptionMessageDbService.createOrderSubscriptionMessage(createReq1);
    OrderSubscriptionMessageDb created2 = orderSubscriptionMessageDbService.createOrderSubscriptionMessage(createReq2);
    List<Long> ids = Arrays.asList(created1.getId(), created2.getId());

    // verify records exist
    List<OrderSubscriptionMessageDb> beforeDelete = orderSubscriptionMessageDbService.getUserOrderSubscriptionMessageHistory(
        GetUserOrderSubscriptionMessageHistoryRpcRequest.newBuilder()
            .setEnterpriseId(1)
            .setOpenId(openId)
            .setOrderPeriodStartDate(0)
            .build());
    assertThat(beforeDelete.size()).isGreaterThanOrEqualTo(2);

    // batch delete (逻辑删除)
    int affectedRows = orderSubscriptionMessageDbService.batchDeleteByIds(ids, 1);
    assertThat(affectedRows).isEqualTo(2);

    // verify deleted - 逻辑删除后查询不到
    List<OrderSubscriptionMessageDb> afterDelete = orderSubscriptionMessageDbService.getUserOrderSubscriptionMessageHistory(
        GetUserOrderSubscriptionMessageHistoryRpcRequest.newBuilder()
            .setEnterpriseId(1)
            .setOpenId(openId)
            .setOrderPeriodStartDate(0)
            .build());
    assertThat(afterDelete.size()).isLessThan(beforeDelete.size());

    // verify deleted - 但数据库里还存在（isDeleted=1）
    Integer count = jdbcTemplate.queryForObject(
        "SELECT COUNT(1) FROM order_subscription_message WHERE id IN (?,?) AND isDeleted=1",
        Integer.class,
        created1.getId(), created2.getId());
    assertThat(count).isEqualTo(2);
  }

  @Test
  void shouldBatchPhysicalDeleteByIds() {
    final String openId = "openId_test_junit_physical_delete";

    // clean
    jdbcTemplate.update("DELETE FROM order_subscription_message WHERE enterpriseId=? AND openId=?", 1, openId);

    // create multiple records
    CreateOrderSubscriptionMessageRpcRequest createReq1 = CreateOrderSubscriptionMessageRpcRequest.newBuilder()
        .setEnterpriseId(1)
        .setDinerId(100)
        .setOpenId(openId)
        .setTemplateId("template_test_junit_physical_delete_1")
        .setJumpPath("/pages/order/index")
        .setOrderPeriodStartDate(1738512000000L)
        .setOrderPeriodEndDate(1739030400000L)
        .build();

    CreateOrderSubscriptionMessageRpcRequest createReq2 = CreateOrderSubscriptionMessageRpcRequest.newBuilder()
        .setEnterpriseId(1)
        .setDinerId(100)
        .setOpenId(openId)
        .setTemplateId("template_test_junit_physical_delete_2")
        .setJumpPath("/pages/order/index")
        .setOrderPeriodStartDate(1738512000000L)
        .setOrderPeriodEndDate(1739030400000L)
        .build();

    OrderSubscriptionMessageDb created1 = orderSubscriptionMessageDbService.createOrderSubscriptionMessage(createReq1);
    OrderSubscriptionMessageDb created2 = orderSubscriptionMessageDbService.createOrderSubscriptionMessage(createReq2);
    List<Long> ids = Arrays.asList(created1.getId(), created2.getId());

    // batch physical delete
    int affectedRows = orderSubscriptionMessageDbService.batchPhysicalDeleteByIds(ids, 1);
    assertThat(affectedRows).isEqualTo(2);

    // verify deleted - 物理删除后数据库里也不存在
    Integer count = jdbcTemplate.queryForObject(
        "SELECT COUNT(1) FROM order_subscription_message WHERE id IN (?,?)",
        Integer.class,
        created1.getId(), created2.getId());
    assertThat(count).isEqualTo(0);
  }
}