/**
 * media_images — PLANNING.md §6c, verbatim DDL.
 * `position` is page-scoped, not category-scoped (§6c): one editorial sequence
 * per page, category is a label/filter. Intrinsic width/height are probed at
 * upload — a row without them is not publishable (§1). Soft delete via deleted_at;
 * public reads filter status='ready'. idx_media_public covers the gallery query.
 */
import type { Knex } from 'knex';

export async function up(knex: Knex): Promise<void> {
  await knex.schema.raw(`
    CREATE TABLE media_images (
      id            CHAR(26) NOT NULL PRIMARY KEY,
      page          ENUM('genesis','hive') NOT NULL,
      category_id   INT UNSIGNED NULL,
      alt           VARCHAR(255) NOT NULL,
      caption       VARCHAR(500) NULL,
      position      INT UNSIGNED NOT NULL,
      is_visible    TINYINT(1) NOT NULL DEFAULT 1,
      width         INT UNSIGNED NOT NULL,
      height        INT UNSIGNED NOT NULL,
      content_hash  CHAR(64) NOT NULL,
      base_path     VARCHAR(255) NOT NULL,
      original_name VARCHAR(255) NOT NULL,
      original_bytes INT UNSIGNED NOT NULL,
      status        ENUM('ready','processing','failed') NOT NULL DEFAULT 'processing',
      created_by    CHAR(26) NULL,
      created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      deleted_at    DATETIME NULL,
      KEY idx_media_public (page, is_visible, deleted_at, position),
      KEY idx_media_category (page, category_id, position),
      CONSTRAINT fk_media_category FOREIGN KEY (category_id) REFERENCES media_categories(id) ON DELETE SET NULL,
      CONSTRAINT fk_media_creator  FOREIGN KEY (created_by)  REFERENCES admin_users(id)
    ) ENGINE=InnoDB
  `);
}

export async function down(knex: Knex): Promise<void> {
  await knex.schema.raw('DROP TABLE IF EXISTS media_images');
}