CREATE DATABASE IF NOT EXISTS wendajia DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE wendajia;

CREATE TABLE IF NOT EXISTS admin_users (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  username VARCHAR(64) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  display_name VARCHAR(64) NOT NULL,
  role_code VARCHAR(32) NOT NULL DEFAULT 'operator',
  refund_payment_account_id INTEGER NULL,
  answer_public_token VARCHAR(96) NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  last_login_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_admin_users_username (username),
  UNIQUE KEY uk_admin_users_answer_public_token (answer_public_token),
  KEY idx_admin_users_role (role_code),
  KEY idx_admin_users_status (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS refund_recipients (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  target_kind VARCHAR(32) NOT NULL,
  target_id BIGINT NOT NULL,
  account VARCHAR(128) NOT NULL,
  payee_name VARCHAR(64) NOT NULL,
  amount_fen INTEGER NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uk_refund_recipient_target (target_kind, target_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS alipay_refund_orders (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  target_kind VARCHAR(32) NOT NULL,
  target_id BIGINT NOT NULL,
  amount_fen INT NOT NULL,
  account VARCHAR(128) NOT NULL,
  payee_name VARCHAR(64) NOT NULL,
  created_by_id BIGINT NOT NULL,
  status VARCHAR(32) NOT NULL DEFAULT 'pending',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_alipay_refund_target (target_kind, target_id),
  KEY idx_alipay_refund_orders_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS alipay_transfer_attempts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  target_kind VARCHAR(32) NOT NULL,
  target_id BIGINT NOT NULL,
  out_biz_no VARCHAR(64) NOT NULL,
  amount_fen INT NOT NULL,
  order_no VARCHAR(64) NOT NULL DEFAULT '',
  shop_name VARCHAR(255) NOT NULL DEFAULT '',
  owner_id BIGINT NULL,
  account VARCHAR(128) NOT NULL,
  payee_name VARCHAR(64) NOT NULL,
  payer_account VARCHAR(128) NOT NULL DEFAULT '',
  created_by_id BIGINT NOT NULL,
  operator_name VARCHAR(64) NOT NULL DEFAULT '',
  status VARCHAR(24) NOT NULL DEFAULT 'pending',
  alipay_trade_no VARCHAR(128) NOT NULL DEFAULT '',
  error_code VARCHAR(128) NOT NULL DEFAULT '',
  error_message VARCHAR(500) NOT NULL DEFAULT '',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uk_alipay_transfer_attempt_out_biz_no (out_biz_no),
  UNIQUE KEY uq_alipay_transfer_attempt_target (target_kind, target_id),
  KEY idx_alipay_transfer_attempt_target (target_kind, target_id),
  KEY idx_alipay_transfer_attempt_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS admin_login_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NULL,
  username VARCHAR(64) NOT NULL,
  success TINYINT(1) NOT NULL,
  ip_address VARCHAR(45) NULL,
  user_agent TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_admin_login_logs_user (user_id),
  KEY idx_admin_login_logs_username (username),
  KEY idx_admin_login_logs_created (created_at),
  CONSTRAINT fk_admin_login_logs_user FOREIGN KEY (user_id) REFERENCES admin_users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS admin_usage_entries (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT NOT NULL,
  username VARCHAR(64) NOT NULL,
  display_name VARCHAR(64) NOT NULL,
  department_name VARCHAR(255) NOT NULL DEFAULT '',
  entered_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_admin_usage_user_time (user_id, entered_at),
  KEY idx_admin_usage_entered_at (entered_at),
  KEY idx_admin_usage_department (department_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 提问任务归属运营账号；历史数据允许为空，超级管理员仍可查看全部任务。
-- 新环境建表时请将 owner_id、shop_name 一并加入 ask_tasks；已有环境由应用启动时自动补列。
-- public_token 是对外分享链接标识，不应使用 task_no 拼接用户链接。

CREATE TABLE IF NOT EXISTS qa_bulk_generation_jobs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_id VARCHAR(64) NOT NULL,
  owner_id BIGINT NULL,
  payload_json LONGTEXT NOT NULL,
  answers_per_question INT NOT NULL,
  status VARCHAR(24) NOT NULL DEFAULT 'queued',
  total_count INT NOT NULL DEFAULT 0,
  completed_count INT NOT NULL DEFAULT 0,
  failed_count INT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_qa_bulk_generation_jobs_public_id (public_id),
  KEY idx_qa_bulk_generation_jobs_owner (owner_id),
  KEY idx_qa_bulk_generation_jobs_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS refund_records (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  target_kind VARCHAR(32) NOT NULL,
  target_id INT NOT NULL,
  owner_id INT NULL,
  shop_name VARCHAR(255) NOT NULL DEFAULT '',
  order_no VARCHAR(64) NOT NULL,
  payee_name VARCHAR(64) NOT NULL DEFAULT '',
  payee_account VARCHAR(128) NOT NULL DEFAULT '',
  amount_fen INT NOT NULL,
  payer_account VARCHAR(128) NOT NULL DEFAULT '',
  operator_name VARCHAR(64) NOT NULL,
  alipay_trade_no VARCHAR(128) NOT NULL DEFAULT '',
  transferred_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_refund_record_target (target_kind, target_id),
  KEY ix_refund_records_owner_id (owner_id),
  KEY ix_refund_records_order_no (order_no),
  KEY ix_refund_records_transferred_at (transferred_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS refund_payment_accounts (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(128) NOT NULL,
  alipay_account VARCHAR(128) NOT NULL UNIQUE,
  balance_fen INT NOT NULL DEFAULT 0,
  single_limit_fen INT NOT NULL,
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS qa_bulk_generation_items (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  job_id BIGINT UNSIGNED NOT NULL,
  sequence_no INT NOT NULL,
  question_text TEXT NOT NULL,
  answers_json LONGTEXT NULL,
  status VARCHAR(24) NOT NULL DEFAULT 'pending',
  attempts INT NOT NULL DEFAULT 0,
  error_message VARCHAR(500) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_qa_bulk_generation_items_sequence (job_id, sequence_no),
  KEY idx_qa_bulk_generation_items_status (job_id, status),
  CONSTRAINT fk_qa_bulk_generation_items_job FOREIGN KEY (job_id) REFERENCES qa_bulk_generation_jobs(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;