qipai/database/migrations/2026061601_m01b_core_schema.up.sql

-- M01-B core schema baseline.
-- Time columns are stored as UTC DATETIME(3). Amount columns use integer cents.

CREATE TABLE IF NOT EXISTS qipai_schema_migrations (
  version VARCHAR(32) NOT NULL PRIMARY KEY,
  name VARCHAR(128) NOT NULL,
  applied_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS qipai_tenants (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(64) NOT NULL,
  name VARCHAR(128) NOT NULL,
  status VARCHAR(32) NOT NULL DEFAULT 'ACTIVE',
  timezone VARCHAR(64) NOT NULL DEFAULT 'Asia/Shanghai',
  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,
  UNIQUE KEY uq_qipai_tenants_code (code),
  KEY idx_qipai_tenants_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS qipai_stores (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  tenant_id BIGINT UNSIGNED NOT NULL,
  legacy_store_id BIGINT UNSIGNED NULL,
  name VARCHAR(128) NOT NULL,
  address VARCHAR(255) NOT NULL DEFAULT '',
  contact_phone VARCHAR(32) NOT NULL DEFAULT '',
  timezone VARCHAR(64) NOT NULL DEFAULT 'Asia/Shanghai',
  business_status VARCHAR(32) NOT NULL DEFAULT 'OPEN',
  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_stores_tenant FOREIGN KEY (tenant_id) REFERENCES qipai_tenants(id),
  UNIQUE KEY uq_qipai_stores_tenant_legacy (tenant_id, legacy_store_id),
  KEY idx_qipai_stores_tenant_status (tenant_id, business_status),
  KEY idx_qipai_stores_deleted_at (deleted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS qipai_rooms (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  tenant_id BIGINT UNSIGNED NOT NULL,
  store_id BIGINT UNSIGNED NOT NULL,
  legacy_room_id BIGINT UNSIGNED NULL,
  name VARCHAR(128) NOT NULL,
  room_no VARCHAR(64) NOT NULL,
  capacity INT UNSIGNED NOT NULL DEFAULT 4,
  base_price_cents INT UNSIGNED NOT NULL DEFAULT 0,
  status VARCHAR(32) NOT NULL DEFAULT 'AVAILABLE',
  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_rooms_tenant FOREIGN KEY (tenant_id) REFERENCES qipai_tenants(id),
  CONSTRAINT fk_qipai_rooms_store FOREIGN KEY (store_id) REFERENCES qipai_stores(id),
  UNIQUE KEY uq_qipai_rooms_store_no (tenant_id, store_id, room_no),
  UNIQUE KEY uq_qipai_rooms_tenant_legacy (tenant_id, legacy_room_id),
  KEY idx_qipai_rooms_tenant_status (tenant_id, status),
  KEY idx_qipai_rooms_deleted_at (deleted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS qipai_members (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  tenant_id BIGINT UNSIGNED NOT NULL,
  legacy_member_id BIGINT UNSIGNED NULL,
  openid VARCHAR(128) NULL,
  unionid VARCHAR(128) NULL,
  nickname VARCHAR(128) NOT NULL DEFAULT '',
  phone VARCHAR(32) NOT NULL DEFAULT '',
  balance_cents INT UNSIGNED NOT NULL DEFAULT 0,
  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_members_tenant FOREIGN KEY (tenant_id) REFERENCES qipai_tenants(id),
  UNIQUE KEY uq_qipai_members_tenant_openid (tenant_id, openid),
  UNIQUE KEY uq_qipai_members_tenant_legacy (tenant_id, legacy_member_id),
  KEY idx_qipai_members_tenant_phone (tenant_id, phone),
  KEY idx_qipai_members_deleted_at (deleted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS qipai_orders (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  tenant_id BIGINT UNSIGNED NOT NULL,
  store_id BIGINT UNSIGNED NOT NULL,
  room_id BIGINT UNSIGNED NOT NULL,
  member_id BIGINT UNSIGNED NULL,
  legacy_order_id BIGINT UNSIGNED NULL,
  order_no VARCHAR(64) NOT NULL,
  status VARCHAR(32) NOT NULL DEFAULT 'DRAFT',
  start_at DATETIME(3) NOT NULL,
  end_at DATETIME(3) NOT NULL,
  total_amount_cents INT UNSIGNED NOT NULL DEFAULT 0,
  paid_amount_cents INT UNSIGNED NOT NULL DEFAULT 0,
  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_orders_tenant FOREIGN KEY (tenant_id) REFERENCES qipai_tenants(id),
  CONSTRAINT fk_qipai_orders_store FOREIGN KEY (store_id) REFERENCES qipai_stores(id),
  CONSTRAINT fk_qipai_orders_room FOREIGN KEY (room_id) REFERENCES qipai_rooms(id),
  CONSTRAINT fk_qipai_orders_member FOREIGN KEY (member_id) REFERENCES qipai_members(id),
  UNIQUE KEY uq_qipai_orders_tenant_order_no (tenant_id, order_no),
  UNIQUE KEY uq_qipai_orders_tenant_legacy (tenant_id, legacy_order_id),
  KEY idx_qipai_orders_room_time (tenant_id, room_id, start_at, end_at),
  KEY idx_qipai_orders_tenant_status (tenant_id, status),
  KEY idx_qipai_orders_deleted_at (deleted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS qipai_payments (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  tenant_id BIGINT UNSIGNED NOT NULL,
  order_id BIGINT UNSIGNED NOT NULL,
  legacy_pay_order_id BIGINT UNSIGNED NULL,
  payment_no VARCHAR(64) NOT NULL,
  channel VARCHAR(32) NOT NULL DEFAULT 'WECHAT',
  status VARCHAR(32) NOT NULL DEFAULT 'PENDING',
  amount_cents INT UNSIGNED NOT NULL,
  paid_at DATETIME(3) NULL,
  raw_notify JSON 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),
  deleted_at DATETIME(3) NULL,
  CONSTRAINT fk_qipai_payments_tenant FOREIGN KEY (tenant_id) REFERENCES qipai_tenants(id),
  CONSTRAINT fk_qipai_payments_order FOREIGN KEY (order_id) REFERENCES qipai_orders(id),
  UNIQUE KEY uq_qipai_payments_tenant_payment_no (tenant_id, payment_no),
  UNIQUE KEY uq_qipai_payments_tenant_legacy (tenant_id, legacy_pay_order_id),
  KEY idx_qipai_payments_tenant_status (tenant_id, status),
  KEY idx_qipai_payments_deleted_at (deleted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS qipai_devices (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  tenant_id BIGINT UNSIGNED NOT NULL,
  store_id BIGINT UNSIGNED NOT NULL,
  room_id BIGINT UNSIGNED NULL,
  legacy_device_id BIGINT UNSIGNED NULL,
  device_id VARCHAR(64) NOT NULL,
  imei VARCHAR(64) NOT NULL DEFAULT '',
  device_type VARCHAR(32) NOT NULL,
  status VARCHAR(32) NOT NULL DEFAULT 'OFFLINE',
  last_seen_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),
  deleted_at DATETIME(3) NULL,
  CONSTRAINT fk_qipai_devices_tenant FOREIGN KEY (tenant_id) REFERENCES qipai_tenants(id),
  CONSTRAINT fk_qipai_devices_store FOREIGN KEY (store_id) REFERENCES qipai_stores(id),
  CONSTRAINT fk_qipai_devices_room FOREIGN KEY (room_id) REFERENCES qipai_rooms(id),
  UNIQUE KEY uq_qipai_devices_tenant_device (tenant_id, device_id),
  UNIQUE KEY uq_qipai_devices_tenant_legacy (tenant_id, legacy_device_id),
  KEY idx_qipai_devices_tenant_type_status (tenant_id, device_type, status),
  KEY idx_qipai_devices_deleted_at (deleted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS qipai_audit_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  tenant_id BIGINT UNSIGNED NOT NULL,
  actor_type VARCHAR(32) NOT NULL,
  actor_id BIGINT UNSIGNED NULL,
  action VARCHAR(128) NOT NULL,
  resource_type VARCHAR(64) NOT NULL,
  resource_id BIGINT UNSIGNED NULL,
  trace_id VARCHAR(128) NOT NULL,
  ip VARCHAR(64) NOT NULL DEFAULT '',
  user_agent VARCHAR(255) NOT NULL DEFAULT '',
  metadata JSON NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  CONSTRAINT fk_qipai_audit_logs_tenant FOREIGN KEY (tenant_id) REFERENCES qipai_tenants(id),
  KEY idx_qipai_audit_logs_tenant_time (tenant_id, created_at),
  KEY idx_qipai_audit_logs_trace_id (trace_id),
  KEY idx_qipai_audit_logs_resource (tenant_id, resource_type, resource_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS qipai_legacy_table_mappings (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  legacy_table VARCHAR(128) NOT NULL,
  new_table VARCHAR(128) NOT NULL,
  strategy VARCHAR(32) NOT NULL,
  note VARCHAR(512) NOT NULL DEFAULT '',
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  UNIQUE KEY uq_qipai_legacy_table_mappings_pair (legacy_table, new_table)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO qipai_schema_migrations (version, name)
VALUES ('2026061601', 'm01b_core_schema');