"""migrations/tables.py — 建表迁移（所有表的 CREATE TABLE IF NOT EXISTS）"""


def migrate_create_tables():
    """确保所有业务表存在（幂等）"""
    from db import db, _exec, mysql, logger

    conn = db()
    try:
        tables = [
            """CREATE TABLE IF NOT EXISTS sales_leads (
  id INT AUTO_INCREMENT PRIMARY KEY,
  target_customer VARCHAR(1000) NOT NULL,
  priority VARCHAR(1000) NOT NULL DEFAULT 'P1',
  status VARCHAR(1000) NOT NULL DEFAULT '待跟进',
  created_at VARCHAR(30) NOT NULL DEFAULT '',
  updated_at VARCHAR(30) NOT NULL DEFAULT ''
)""",
            """CREATE TABLE IF NOT EXISTS follow_up_records (
  id INT AUTO_INCREMENT PRIMARY KEY,
  target_type VARCHAR(1000) NOT NULL,
  target_id INT NOT NULL,
  followed_at VARCHAR(1000) NOT NULL DEFAULT '',
  follower VARCHAR(1000) NOT NULL DEFAULT '慰心',
  follow_up_method VARCHAR(1000) NOT NULL DEFAULT '记录',
  content VARCHAR(1000) NOT NULL DEFAULT '',
  next_action VARCHAR(1000) NOT NULL DEFAULT '',
  next_follow_up_at VARCHAR(1000) NOT NULL DEFAULT '',
  created_at VARCHAR(30) NOT NULL DEFAULT '',
  updated_at VARCHAR(30) NOT NULL DEFAULT ''
)""",
            """CREATE TABLE IF NOT EXISTS business_proposals (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_or_project_name VARCHAR(1000) NOT NULL,
  version VARCHAR(1000) NOT NULL,
  description VARCHAR(1000) NOT NULL DEFAULT '',
  status VARCHAR(1000) NOT NULL DEFAULT '草稿',
  created_date VARCHAR(1000) NOT NULL DEFAULT '',
  created_at VARCHAR(30) NOT NULL DEFAULT '',
  updated_at VARCHAR(30) NOT NULL DEFAULT ''
)""",
            """CREATE TABLE IF NOT EXISTS operation_projects (
  id INT AUTO_INCREMENT PRIMARY KEY,
  project_name VARCHAR(1000) NOT NULL,
  project_version VARCHAR(1000) NOT NULL DEFAULT 'v1.0',
  project_type VARCHAR(1000) NOT NULL DEFAULT 'opportunity',
  project_status VARCHAR(1000) NOT NULL DEFAULT '',
  current_stage VARCHAR(1000) NOT NULL DEFAULT '',
  owner VARCHAR(1000) NOT NULL DEFAULT '慰心',
  start_date VARCHAR(1000) NOT NULL DEFAULT '',
  end_date VARCHAR(1000) NOT NULL DEFAULT '',
  target_customer VARCHAR(1000) NOT NULL DEFAULT '',
  customer_need VARCHAR(1000) NOT NULL DEFAULT '',
  expected_contract_amount DOUBLE NOT NULL DEFAULT 0,
  expected_sign_date VARCHAR(1000) NOT NULL DEFAULT '',
  sign_probability DOUBLE NOT NULL DEFAULT 0,
  next_action VARCHAR(1000) NOT NULL DEFAULT '',
  related_business_proposal_id INTEGER,
  sop_file_id INTEGER,
  sop_stage VARCHAR(1000) NOT NULL DEFAULT '',
  execution_progress DOUBLE NOT NULL DEFAULT 0,
  current_deliverable VARCHAR(1000) NOT NULL DEFAULT '',
  risks VARCHAR(1000) NOT NULL DEFAULT '',
  notes VARCHAR(1000) NOT NULL DEFAULT '',
  created_at VARCHAR(30) NOT NULL DEFAULT '',
  updated_at VARCHAR(30) NOT NULL DEFAULT ''
)""",
            """CREATE TABLE IF NOT EXISTS product_versions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  product_name VARCHAR(1000) NOT NULL,
  version VARCHAR(1000) NOT NULL,
  version_goal VARCHAR(1000) NOT NULL DEFAULT '',
  feature_list VARCHAR(1000) NOT NULL DEFAULT '',
  launch_date VARCHAR(1000) NOT NULL DEFAULT '',
  status VARCHAR(1000) NOT NULL DEFAULT '规划中',
  notes VARCHAR(1000) NOT NULL DEFAULT '',
  created_at VARCHAR(30) NOT NULL DEFAULT '',
  updated_at VARCHAR(30) NOT NULL DEFAULT ''
)""",
            """CREATE TABLE IF NOT EXISTS finance_records (
  id INT AUTO_INCREMENT PRIMARY KEY,
  month VARCHAR(1000) NOT NULL,
  project_name VARCHAR(1000) NOT NULL DEFAULT '科普（慰心斋）',
  record_type VARCHAR(1000) NOT NULL,
  category VARCHAR(1000) NOT NULL DEFAULT '',
  amount DOUBLE NOT NULL DEFAULT 0,
  occurred_date VARCHAR(1000) NOT NULL DEFAULT '',
  notes VARCHAR(1000) NOT NULL DEFAULT '',
  created_at VARCHAR(30) NOT NULL DEFAULT '',
  updated_at VARCHAR(30) NOT NULL DEFAULT ''
)""",
            """CREATE TABLE IF NOT EXISTS file_assets (
  id INT AUTO_INCREMENT PRIMARY KEY,
  module VARCHAR(1000) NOT NULL,
  owner_id INT NOT NULL,
  owner_version VARCHAR(1000) NOT NULL DEFAULT '',
  file_category VARCHAR(1000) NOT NULL DEFAULT '',
  file_name VARCHAR(1000) NOT NULL,
  file_type VARCHAR(1000) NOT NULL DEFAULT '',
  file_size INTEGER NOT NULL DEFAULT 0,
  file_path VARCHAR(1000) NOT NULL,
  is_external INTEGER NOT NULL DEFAULT 0,
  notes VARCHAR(1000) NOT NULL DEFAULT '',
  created_at VARCHAR(30) NOT NULL DEFAULT '',
  updated_at VARCHAR(30) NOT NULL DEFAULT ''
)""",
            """CREATE TABLE IF NOT EXISTS project_tasks (
  id INT AUTO_INCREMENT PRIMARY KEY,
  project_id INTEGER NOT NULL,
  phase VARCHAR(1000) NOT NULL DEFAULT '',
  milestone VARCHAR(1000) NOT NULL DEFAULT '',
  task VARCHAR(1000) NOT NULL DEFAULT '',
  owner VARCHAR(1000) NOT NULL DEFAULT '',
  due_date VARCHAR(1000) NOT NULL DEFAULT '',
  blockers VARCHAR(1000) NOT NULL DEFAULT '',
  notes VARCHAR(1000) NOT NULL DEFAULT '',
  created_at VARCHAR(30) NOT NULL DEFAULT '',
  updated_at VARCHAR(30) NOT NULL DEFAULT ''
)""",
            """CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(100) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  display_name VARCHAR(100) NOT NULL,
  role VARCHAR(50) NOT NULL DEFAULT 'opc_owner',
  created_at VARCHAR(30) NOT NULL DEFAULT ''
)""",
            """CREATE TABLE IF NOT EXISTS user_tenants (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  tenant VARCHAR(100) NOT NULL,
  UNIQUE KEY (user_id, tenant)
)""",
            """CREATE TABLE IF NOT EXISTS project_finances (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant VARCHAR(100) NOT NULL DEFAULT '科普·无界',
  project_id VARCHAR(100) NOT NULL DEFAULT '',
  business_type VARCHAR(100) NOT NULL DEFAULT '',
  customer_name VARCHAR(200) NOT NULL DEFAULT '',
  sign_amount DOUBLE NOT NULL DEFAULT 0,
  sign_month VARCHAR(20) NOT NULL DEFAULT '',
  status VARCHAR(50) NOT NULL DEFAULT '待签约',
  sales_person VARCHAR(100) NOT NULL DEFAULT '',
  total_rev DOUBLE NOT NULL DEFAULT 0,
  total_gross DOUBLE NOT NULL DEFAULT 0,
  budget_data TEXT,
  created_at VARCHAR(30) NOT NULL DEFAULT '',
  updated_at VARCHAR(30) NOT NULL DEFAULT ''
)""",
        ]

        for ddl in tables:
            try:
                _exec(conn, ddl)
                conn.commit()
            except mysql.connector.Error as e:
                logger.debug(f"create table skipped: {e}")
                conn.commit()

        print("[migrate] 所有业务表已就绪")
    finally:
        conn.close()