<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.gxwebsoft.common.system.mapper.TenantSubscriptionMapper">

  <!-- 根据租户ID查询订阅信息（关联查询） -->
  <select id="selectByTenantIdRel" resultType="com.gxwebsoft.common.system.entity.TenantSubscription">
    SELECT s.*,
           p.package_name,
           p.version,
           p.max_users,
           p.max_orders,
           p.storage_limit,
           p.api_limit,
           p.features
    FROM sys_tenant_subscription s
           LEFT JOIN sys_tenant_package p ON s.package_id = p.package_id
    WHERE s.tenant_id = #{tenantId}
      AND s.status = 1
    LIMIT 1
  </select>

  <!-- 查询即将过期的订阅（用于提醒） -->
  <select id="selectExpiringSoon" resultType="com.gxwebsoft.common.system.entity.TenantSubscription">
    SELECT s.*,
           t.tenant_name,
           t.phone
    FROM sys_tenant_subscription s
           LEFT JOIN sys_tenant t ON s.tenant_id = t.tenant_id
    WHERE s.status = 1
      AND s.is_expired = 0
      AND s.end_time BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL #{days} DAY)
    ORDER BY s.end_time ASC
  </select>

  <!-- 查询已过期的订阅 -->
  <select id="selectExpired" resultType="com.gxwebsoft.common.system.entity.TenantSubscription">
    SELECT s.*
    FROM sys_tenant_subscription s
    WHERE s.status = 1
      AND s.is_expired = 0
      AND s.end_time &lt; NOW()
  </select>

</mapper>
