import 'dotenv/config'
import { query, exec } from './pool';
import { queryOne } from './pool'
import { logger } from '../lib/logger';
import dotenv from 'dotenv';
dotenv.config();

// MariaDB migration — no PostgreSQL-specific syntax.
// ENUMs defined inline on columns, UUIDs generated in app code via uuid package,
// JSONB → JSON, no table partitioning, no RETURNING clause.

const migrations: { name: string; sql: string[] }[] = [
  {
    name: '001_schema_migrations_table',
    sql: [`
      CREATE TABLE IF NOT EXISTS schema_migrations (
        name       VARCHAR(255) NOT NULL PRIMARY KEY,
        applied_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `],
  },
  {
    name: '002_users',
    sql: [`
      CREATE TABLE IF NOT EXISTS users (
        id                      CHAR(36)     NOT NULL PRIMARY KEY,
        email                   VARCHAR(255) NOT NULL,
        password_hash           VARCHAR(255),
        oauth_provider          VARCHAR(50),
        oauth_id                VARCHAR(255),
        username                VARCHAR(50)  NOT NULL,
        status                  ENUM('active','suspended','banned') NOT NULL DEFAULT 'active',
        gold_balance            BIGINT       NOT NULL DEFAULT 0,
        premium_balance         INT          NOT NULL DEFAULT 0,
        prestige_points         INT          NOT NULL DEFAULT 0,
        market_restricted_until DATETIME,
        last_login_at           DATETIME,
        created_at              DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at              DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        UNIQUE KEY uq_users_email    (email),
        UNIQUE KEY uq_users_username (username),
        KEY idx_users_status (status),
        CONSTRAINT chk_gold_balance CHECK (gold_balance >= 0),
        CONSTRAINT chk_premium_balance CHECK (premium_balance >= 0)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `],
  },
  {
    name: '003_refresh_tokens',
    sql: [`
      CREATE TABLE IF NOT EXISTS refresh_tokens (
        id         CHAR(36)     NOT NULL PRIMARY KEY,
        user_id    CHAR(36)     NOT NULL,
        token_hash VARCHAR(255) NOT NULL,
        expires_at DATETIME     NOT NULL,
        created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
        UNIQUE KEY uq_token_hash (token_hash),
        KEY idx_rt_user (user_id),
        CONSTRAINT fk_rt_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `],
  },
  {
    name: '004_ranches',
    sql: [`
      CREATE TABLE IF NOT EXISTS ranches (
        id             CHAR(36)     NOT NULL PRIMARY KEY,
        owner_id       CHAR(36)     NOT NULL,
        name           VARCHAR(100) NOT NULL,
        description    TEXT,
        plot_count     INT          NOT NULL DEFAULT 4,
        max_plot_count INT          NOT NULL DEFAULT 20,
        last_upkeep_at DATE         NOT NULL DEFAULT (CURRENT_DATE),
        debt_flag      TINYINT(1)   NOT NULL DEFAULT 0,
        prestige_level INT          NOT NULL DEFAULT 1,
        created_at     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        UNIQUE KEY uq_ranch_owner (owner_id),
        CONSTRAINT fk_ranch_owner FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE CASCADE,
        CONSTRAINT chk_plot_count CHECK (plot_count >= 1)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `],
  },
  {
    name: '005_buildings',
    sql: [`
      CREATE TABLE IF NOT EXISTS buildings (
        id            CHAR(36)    NOT NULL PRIMARY KEY,
        ranch_id      CHAR(36)    NOT NULL,
        building_type VARCHAR(50) NOT NULL,
        plot_position INT         NOT NULL,
        status        ENUM('constructing','active','suspended') NOT NULL DEFAULT 'constructing',
        complete_at   DATETIME,
        built_at      DATETIME,
        created_at    DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
        UNIQUE KEY uq_building_plot (ranch_id, plot_position),
        CONSTRAINT fk_building_ranch FOREIGN KEY (ranch_id) REFERENCES ranches(id) ON DELETE CASCADE
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `],
  },
  {
    name: '006_animals',
    sql: [`
      CREATE TABLE IF NOT EXISTS animals (
        id                     CHAR(36)     NOT NULL PRIMARY KEY,
        owner_id               CHAR(36)     NOT NULL,
        ranch_id               CHAR(36)     NOT NULL,
        name                   VARCHAR(100) NOT NULL,
        breed                  VARCHAR(50)  NOT NULL,
        sex                    ENUM('male','female') NOT NULL,
        born_at                DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
        died_at                DATETIME,
        stage                  ENUM('calf','yearling','adult','elder') NOT NULL DEFAULT 'calf',
        health                 DECIMAL(5,2) NOT NULL DEFAULT 100.00,
        \`condition\`          DECIMAL(5,2) NOT NULL DEFAULT 100.00,
        status                 ENUM('healthy','pregnant','recovering','neglected','retired','dead') NOT NULL DEFAULT 'healthy',
        pregnancy_end_at       DATETIME,
        recovery_end_at        DATETIME,
        sire_id                CHAR(36),
        dam_id                 CHAR(36),
        inbreeding_coefficient DECIMAL(8,6) NOT NULL DEFAULT 0.000000,
        rarity_tier            ENUM('common','uncommon','rare','legendary') NOT NULL DEFAULT 'common',
        last_fed_at            DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
        created_at             DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at             DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        KEY idx_animals_owner  (owner_id),
        KEY idx_animals_ranch  (ranch_id),
        KEY idx_animals_status (status),
        KEY idx_animals_stage  (stage),
        KEY idx_animals_breed  (breed),
        KEY idx_animals_alive  (owner_id, died_at),
        CONSTRAINT fk_animal_owner FOREIGN KEY (owner_id) REFERENCES users(id),
        CONSTRAINT fk_animal_ranch FOREIGN KEY (ranch_id) REFERENCES ranches(id),
        CONSTRAINT fk_animal_sire  FOREIGN KEY (sire_id)  REFERENCES animals(id),
        CONSTRAINT fk_animal_dam   FOREIGN KEY (dam_id)   REFERENCES animals(id),
        CONSTRAINT chk_health     CHECK (health BETWEEN 0 AND 100),
        CONSTRAINT chk_condition  CHECK (\`condition\` BETWEEN 0 AND 100)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `],
  },
  {
    name: '007_animal_phenotypes',
    sql: [`
      CREATE TABLE IF NOT EXISTS animal_phenotypes (
        animal_id            CHAR(36)     NOT NULL PRIMARY KEY,
        weight_score         DECIMAL(5,3) NOT NULL,
        milk_yield_score     DECIMAL(5,3) NOT NULL,
        growth_rate_score    DECIMAL(5,3) NOT NULL,
        temperament_score    DECIMAL(5,3) NOT NULL,
        coat_quality_score   DECIMAL(5,3) NOT NULL,
        fertility_score      DECIMAL(5,3) NOT NULL,
        hardiness_score      DECIMAL(5,3) NOT NULL,
        show_potential_score DECIMAL(5,3) NOT NULL,
        CONSTRAINT fk_pheno_animal FOREIGN KEY (animal_id) REFERENCES animals(id) ON DELETE CASCADE
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `],
  },
  {
    name: '008_animal_genotypes',
    sql: [`
      CREATE TABLE IF NOT EXISTS animal_genotypes (
        animal_id        CHAR(36)     NOT NULL PRIMARY KEY,
        g_weight         DECIMAL(5,3) NOT NULL,
        g_milk_yield     DECIMAL(5,3) NOT NULL,
        g_growth_rate    DECIMAL(5,3) NOT NULL,
        g_temperament    DECIMAL(5,3) NOT NULL,
        g_coat_quality   DECIMAL(5,3) NOT NULL,
        g_fertility      DECIMAL(5,3) NOT NULL,
        g_hardiness      DECIMAL(5,3) NOT NULL,
        g_show_potential DECIMAL(5,3) NOT NULL,
        genotype_tested  TINYINT(1)   NOT NULL DEFAULT 0,
        CONSTRAINT fk_geno_animal FOREIGN KEY (animal_id) REFERENCES animals(id) ON DELETE CASCADE
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `],
  },
  {
    name: '009_lineage',
    sql: [`
      CREATE TABLE IF NOT EXISTS lineage (
        id          CHAR(36) NOT NULL PRIMARY KEY,
        animal_id   CHAR(36) NOT NULL,
        ancestor_id CHAR(36) NOT NULL,
        generation  INT      NOT NULL,
        UNIQUE KEY uq_lineage (animal_id, ancestor_id),
        KEY idx_lineage_animal   (animal_id),
        KEY idx_lineage_ancestor (ancestor_id),
        CONSTRAINT fk_lineage_animal   FOREIGN KEY (animal_id)   REFERENCES animals(id) ON DELETE CASCADE,
        CONSTRAINT fk_lineage_ancestor FOREIGN KEY (ancestor_id) REFERENCES animals(id),
        CONSTRAINT chk_generation CHECK (generation BETWEEN 1 AND 10)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `],
  },
  {
    name: '010_market',
    sql: [`
      CREATE TABLE IF NOT EXISTS market_listings (
        id                CHAR(36)    NOT NULL PRIMARY KEY,
        seller_id         CHAR(36)    NOT NULL,
        animal_id         CHAR(36),
        item_type         VARCHAR(50),
        listing_type      ENUM('fixed','auction') NOT NULL DEFAULT 'fixed',
        price             BIGINT      NOT NULL,
        buyout_price      BIGINT,
        current_bid       BIGINT,
        current_bidder_id CHAR(36),
        expires_at        DATETIME    NOT NULL,
        status            ENUM('active','sold','expired','cancelled') NOT NULL DEFAULT 'active',
        created_at        DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at        DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        KEY idx_listings_status  (status),
        KEY idx_listings_expires (expires_at, status),
        KEY idx_listings_seller  (seller_id),
        KEY idx_listings_animal  (animal_id),
        CONSTRAINT fk_listing_seller FOREIGN KEY (seller_id) REFERENCES users(id),
        CONSTRAINT fk_listing_animal FOREIGN KEY (animal_id) REFERENCES animals(id),
        CONSTRAINT chk_price CHECK (price > 0)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `],
  },
  {
    name: '011_transactions',
    sql: [`
      CREATE TABLE IF NOT EXISTS transactions (
        id            CHAR(36)     NOT NULL PRIMARY KEY,
        user_id       CHAR(36)     NOT NULL,
        amount        BIGINT       NOT NULL,
        currency      ENUM('gold','premium') NOT NULL DEFAULT 'gold',
        category      ENUM('market_sale','market_buy','upkeep','fee','reward','admin_grant','refund','starter') NOT NULL,
        ref_id        CHAR(36),
        balance_after BIGINT       NOT NULL,
        description   TEXT,
        created_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
        KEY idx_tx_user     (user_id),
        KEY idx_tx_created  (created_at),
        KEY idx_tx_category (category),
        CONSTRAINT fk_tx_user FOREIGN KEY (user_id) REFERENCES users(id)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `],
  },
  {
    name: '012_seasons_shows',
    sql: [
      `CREATE TABLE IF NOT EXISTS seasons (
        id         CHAR(36)     NOT NULL PRIMARY KEY,
        name       VARCHAR(100) NOT NULL,
        start_date DATETIME     NOT NULL,
        end_date   DATETIME     NOT NULL,
        is_active  TINYINT(1)   NOT NULL DEFAULT 1,
        created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci`,
      `CREATE TABLE IF NOT EXISTS shows (
        id              CHAR(36)    NOT NULL PRIMARY KEY,
        name            VARCHAR(100) NOT NULL,
        tier            ENUM('rookie','pro','elite') NOT NULL DEFAULT 'rookie',
        entry_fee       INT         NOT NULL DEFAULT 0,
        entry_closes_at DATETIME    NOT NULL,
        judged_at       DATETIME,
        status          ENUM('open','judging','completed') NOT NULL DEFAULT 'open',
        season_id       CHAR(36),
        created_at      DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
        KEY idx_shows_status (status),
        KEY idx_shows_closes (entry_closes_at, status),
        CONSTRAINT fk_show_season FOREIGN KEY (season_id) REFERENCES seasons(id)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci`,
      `CREATE TABLE IF NOT EXISTS show_entries (
        id             CHAR(36) NOT NULL PRIMARY KEY,
        show_id        CHAR(36) NOT NULL,
        player_id      CHAR(36) NOT NULL,
        animal_id      CHAR(36) NOT NULL,
        score          DECIMAL(8,4),
        \`rank\`       INT,
        reward_claimed TINYINT(1) NOT NULL DEFAULT 0,
        created_at     DATETIME   NOT NULL DEFAULT CURRENT_TIMESTAMP,
        UNIQUE KEY uq_show_animal (show_id, animal_id),
        KEY idx_se_show   (show_id),
        KEY idx_se_player (player_id),
        CONSTRAINT fk_se_show   FOREIGN KEY (show_id)   REFERENCES shows(id),
        CONSTRAINT fk_se_player FOREIGN KEY (player_id) REFERENCES users(id),
        CONSTRAINT fk_se_animal FOREIGN KEY (animal_id) REFERENCES animals(id)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci`,
    ],
  },
  {
    name: '013_clubs',
    sql: [
      `CREATE TABLE IF NOT EXISTS clubs (
        id           CHAR(36)     NOT NULL PRIMARY KEY,
        name         VARCHAR(100) NOT NULL,
        owner_id     CHAR(36)     NOT NULL,
        description  TEXT,
        gold_vault   BIGINT       NOT NULL DEFAULT 0,
        member_count INT          NOT NULL DEFAULT 1,
        created_at   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        UNIQUE KEY uq_club_name (name),
        CONSTRAINT fk_club_owner FOREIGN KEY (owner_id) REFERENCES users(id),
        CONSTRAINT chk_vault CHECK (gold_vault >= 0)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci`,
      `CREATE TABLE IF NOT EXISTS club_members (
        club_id   CHAR(36)   NOT NULL,
        user_id   CHAR(36)   NOT NULL,
        role      ENUM('owner','officer','member') NOT NULL DEFAULT 'member',
        joined_at DATETIME   NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (club_id, user_id),
        KEY idx_cm_user (user_id),
        CONSTRAINT fk_cm_club FOREIGN KEY (club_id) REFERENCES clubs(id)  ON DELETE CASCADE,
        CONSTRAINT fk_cm_user FOREIGN KEY (user_id) REFERENCES users(id)  ON DELETE CASCADE
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci`,
    ],
  },
  {
    name: '014_messaging',
    sql: [
      `CREATE TABLE IF NOT EXISTS messages (
        id           CHAR(36) NOT NULL PRIMARY KEY,
        sender_id    CHAR(36) NOT NULL,
        recipient_id CHAR(36) NOT NULL,
        content      TEXT     NOT NULL,
        read_at      DATETIME,
        deleted_at   DATETIME,
        created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        KEY idx_msg_sender    (sender_id),
        KEY idx_msg_recipient (recipient_id),
        CONSTRAINT fk_msg_sender    FOREIGN KEY (sender_id)    REFERENCES users(id),
        CONSTRAINT fk_msg_recipient FOREIGN KEY (recipient_id) REFERENCES users(id)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci`,
      `CREATE TABLE IF NOT EXISTS blocks (
        blocker_id CHAR(36) NOT NULL,
        blocked_id CHAR(36) NOT NULL,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (blocker_id, blocked_id),
        CONSTRAINT fk_block_blocker FOREIGN KEY (blocker_id) REFERENCES users(id) ON DELETE CASCADE,
        CONSTRAINT fk_block_blocked FOREIGN KEY (blocked_id) REFERENCES users(id) ON DELETE CASCADE
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci`,
    ],
  },
  {
    name: '015_audit_log',
    sql: [`
      CREATE TABLE IF NOT EXISTS audit_log (
        id          CHAR(36)    NOT NULL PRIMARY KEY,
        actor_id    CHAR(36),
        action      VARCHAR(100) NOT NULL,
        target_type VARCHAR(50)  NOT NULL,
        target_id   CHAR(36),
        payload     JSON        NOT NULL,
        ip_address  VARCHAR(45),
        created_at  DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
        KEY idx_audit_action  (action),
        KEY idx_audit_actor   (actor_id),
        KEY idx_audit_target  (target_type, target_id),
        KEY idx_audit_created (created_at)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `],
  },
  {
    name: '016_reports',
    sql: [`
      CREATE TABLE IF NOT EXISTS reports (
        id           CHAR(36)     NOT NULL PRIMARY KEY,
        reporter_id  CHAR(36)     NOT NULL,
        target_type  VARCHAR(50)  NOT NULL,
        target_id    CHAR(36)     NOT NULL,
        reason       VARCHAR(100) NOT NULL,
        description  TEXT,
        status       ENUM('pending','resolved','dismissed') NOT NULL DEFAULT 'pending',
        resolved_by  CHAR(36),
        resolved_at  DATETIME,
        action_taken VARCHAR(100),
        created_at   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
        KEY idx_reports_status (status),
        KEY idx_reports_target (target_type, target_id),
        CONSTRAINT fk_report_reporter FOREIGN KEY (reporter_id) REFERENCES users(id)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `],
  },
];

// ─── Runner ───────────────────────────────────────────────────────────────────

async function runMigrations() {
  logger.info('Starting MariaDB migrations...');

  // Ensure migrations table exists first (migration 001 handles it)
  await exec(`
    CREATE TABLE IF NOT EXISTS schema_migrations (
      name       VARCHAR(255) NOT NULL PRIMARY KEY,
      applied_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  `);

  for (const migration of migrations) {
    const existing = await queryOne(
      'SELECT name FROM schema_migrations WHERE name = ?',
      [migration.name]
    );

    if (existing) {
      logger.debug(`Already applied: ${migration.name}`);
      continue;
    }

    logger.info(`Applying: ${migration.name}`);
    for (const sql of migration.sql) {
      await exec(sql);
    }
    await exec('INSERT INTO schema_migrations (name) VALUES (?)', [migration.name]);
    logger.info(`Applied: ${migration.name}`);
  }

  logger.info('All migrations complete.');
}

runMigrations()
  .then(() => process.exit(0))
  .catch((err) => {
    logger.error('Migration failed', err);
    process.exit(1);
  });