qipai/database/migrations/2026062423_m07c_benefits.up.sql

CREATE TABLE IF NOT EXISTS qipai_coupon_templates (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  tenant_id BIGINT UNSIGNED NOT NULL,
  store_id BIGINT UNSIGNED NULL,
  name VARCHAR(128) NOT NULL,
  coupon_type VARCHAR(32) NOT NULL,
  discount_amount_cents INT UNSIGNED NOT NULL DEFAULT 0,
  time_minutes INT UNSIGNED NOT NULL DEFAULT 0,
  min_order_amount_cents INT UNSIGNED NOT NULL DEFAULT 0,
  room_category_id BIGINT UNSIGNED NULL,
  room_id BIGINT UNSIGNED NULL,
  weekdays_json JSON NULL,
  holiday_only TINYINT(1) NOT NULL DEFAULT 0,
  starts_at DATETIME(3) NULL,
  ends_at DATETIME(3) NULL,
  valid_days INT UNSIGNED NULL,
  usage_limit_per_user INT UNSIGNED NOT NULL DEFAULT 1,
  status VARCHAR(32) NOT NULL DEFAULT 'ACTIVE',
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3)
    ON UPDATE CURRENT_TIMESTAMP(3),
  deleted_at DATETIME(3) NULL,
  CONSTRAINT fk_qipai_coupon_template_tenant
    FOREIGN KEY (tenant_id) REFERENCES qipai_tenants(id),
  CONSTRAINT fk_qipai_coupon_template_store
    FOREIGN KEY (store_id) REFERENCES qipai_stores(id),
  CONSTRAINT fk_qipai_coupon_template_room_category
    FOREIGN KEY (room_category_id) REFERENCES qipai_room_categories(id),
  CONSTRAINT fk_qipai_coupon_template_room
    FOREIGN KEY (room_id) REFERENCES qipai_rooms(id),
  KEY idx_qipai_coupon_template_scope
    (tenant_id, store_id, coupon_type, status, starts_at, ends_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS qipai_coupon_grants (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  tenant_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  template_id BIGINT UNSIGNED NOT NULL,
  coupon_code VARCHAR(64) NOT NULL,
  status VARCHAR(32) NOT NULL DEFAULT 'AVAILABLE',
  valid_from DATETIME(3) NOT NULL,
  valid_to DATETIME(3) NOT NULL,
  remaining_uses INT UNSIGNED NOT NULL DEFAULT 1,
  used_count INT UNSIGNED NOT NULL DEFAULT 0,
  frozen_order_id BIGINT UNSIGNED NULL,
  used_at DATETIME(3) NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3)
    ON UPDATE CURRENT_TIMESTAMP(3),
  CONSTRAINT fk_qipai_coupon_grant_tenant
    FOREIGN KEY (tenant_id) REFERENCES qipai_tenants(id),
  CONSTRAINT fk_qipai_coupon_grant_user
    FOREIGN KEY (user_id) REFERENCES qipai_users(id),
  CONSTRAINT fk_qipai_coupon_grant_template
    FOREIGN KEY (template_id) REFERENCES qipai_coupon_templates(id),
  CONSTRAINT fk_qipai_coupon_grant_order
    FOREIGN KEY (frozen_order_id) REFERENCES qipai_orders(id),
  UNIQUE KEY uq_qipai_coupon_grant_code (tenant_id, coupon_code),
  KEY idx_qipai_coupon_grant_user (tenant_id, user_id, status, valid_to)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS qipai_package_plans (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  tenant_id BIGINT UNSIGNED NOT NULL,
  store_id BIGINT UNSIGNED NULL,
  name VARCHAR(128) NOT NULL,
  price_cents INT UNSIGNED NOT NULL,
  minutes_total INT UNSIGNED NOT NULL DEFAULT 0,
  amount_cents_total INT UNSIGNED NOT NULL DEFAULT 0,
  room_category_id BIGINT UNSIGNED NULL,
  room_id BIGINT UNSIGNED NULL,
  weekdays_json JSON NULL,
  holiday_only TINYINT(1) NOT NULL DEFAULT 0,
  valid_days INT UNSIGNED NULL,
  status VARCHAR(32) NOT NULL DEFAULT 'ACTIVE',
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3)
    ON UPDATE CURRENT_TIMESTAMP(3),
  deleted_at DATETIME(3) NULL,
  CONSTRAINT fk_qipai_package_plan_tenant
    FOREIGN KEY (tenant_id) REFERENCES qipai_tenants(id),
  CONSTRAINT fk_qipai_package_plan_store
    FOREIGN KEY (store_id) REFERENCES qipai_stores(id),
  CONSTRAINT fk_qipai_package_plan_room_category
    FOREIGN KEY (room_category_id) REFERENCES qipai_room_categories(id),
  CONSTRAINT fk_qipai_package_plan_room
    FOREIGN KEY (room_id) REFERENCES qipai_rooms(id),
  KEY idx_qipai_package_plan_scope (tenant_id, store_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS qipai_package_holdings (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  tenant_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  plan_id BIGINT UNSIGNED NOT NULL,
  purchase_order_id BIGINT UNSIGNED NULL,
  status VARCHAR(32) NOT NULL DEFAULT 'ACTIVE',
  valid_from DATETIME(3) NOT NULL,
  valid_to DATETIME(3) NOT NULL,
  remaining_minutes INT UNSIGNED NOT NULL DEFAULT 0,
  remaining_amount_cents INT UNSIGNED NOT NULL DEFAULT 0,
  frozen_order_id BIGINT UNSIGNED NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3)
    ON UPDATE CURRENT_TIMESTAMP(3),
  CONSTRAINT fk_qipai_package_holding_tenant
    FOREIGN KEY (tenant_id) REFERENCES qipai_tenants(id),
  CONSTRAINT fk_qipai_package_holding_user
    FOREIGN KEY (user_id) REFERENCES qipai_users(id),
  CONSTRAINT fk_qipai_package_holding_plan
    FOREIGN KEY (plan_id) REFERENCES qipai_package_plans(id),
  CONSTRAINT fk_qipai_package_holding_purchase_order
    FOREIGN KEY (purchase_order_id) REFERENCES qipai_orders(id),
  CONSTRAINT fk_qipai_package_holding_frozen_order
    FOREIGN KEY (frozen_order_id) REFERENCES qipai_orders(id),
  KEY idx_qipai_package_holding_user (tenant_id, user_id, status, valid_to)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS qipai_benefit_usages (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  tenant_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  order_id BIGINT UNSIGNED NOT NULL,
  benefit_type VARCHAR(32) NOT NULL,
  benefit_id BIGINT UNSIGNED NOT NULL,
  client_request_id VARCHAR(128) NOT NULL,
  status VARCHAR(32) NOT NULL DEFAULT 'FROZEN',
  discount_cents INT UNSIGNED NOT NULL DEFAULT 0,
  package_credit_cents INT UNSIGNED NOT NULL DEFAULT 0,
  minutes INT UNSIGNED NOT NULL DEFAULT 0,
  trace_id VARCHAR(128) NOT NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3)
    ON UPDATE CURRENT_TIMESTAMP(3),
  CONSTRAINT fk_qipai_benefit_usage_tenant
    FOREIGN KEY (tenant_id) REFERENCES qipai_tenants(id),
  CONSTRAINT fk_qipai_benefit_usage_user
    FOREIGN KEY (user_id) REFERENCES qipai_users(id),
  CONSTRAINT fk_qipai_benefit_usage_order
    FOREIGN KEY (order_id) REFERENCES qipai_orders(id),
  UNIQUE KEY uq_qipai_benefit_usage_request (tenant_id, user_id, client_request_id),
  UNIQUE KEY uq_qipai_benefit_usage_order_benefit
    (tenant_id, order_id, benefit_type, benefit_id),
  KEY idx_qipai_benefit_usage_order (tenant_id, order_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO qipai_schema_migrations (version, name)
VALUES ('2026062423', 'm07c_benefits');