-- The database itself must already exist before this runs.
--   • cPanel: create the DB via cPanel → MySQL Databases, attach a user with ALL PRIVILEGES,
--     and set DB_NAME / DB_USER / DB_PASSWORD env vars accordingly.
--   • Local dev: the migration runner will auto-create DB_NAME if the connecting
--     user has CREATE privileges (root usually does).
--
-- All statements below run in whichever database the connection is set to,
-- so we never hardcode a database name here.

-- ── Migration tracker ─────────────────────────────────────
CREATE TABLE IF NOT EXISTS schema_migrations (
  filename   VARCHAR(190) NOT NULL,
  applied_at TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (filename)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Users ─────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS users (
  id            BIGINT UNSIGNED   NOT NULL AUTO_INCREMENT,
  full_name     VARCHAR(120)      NOT NULL,
  email         VARCHAR(190)      NOT NULL,
  password_hash VARCHAR(255)      NOT NULL,
  phone         VARCHAR(32)       NULL,
  age           SMALLINT UNSIGNED NULL,
  is_admin      TINYINT(1)        NOT NULL DEFAULT 0,
  created_at    TIMESTAMP         NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    TIMESTAMP         NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_users_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Class slots: weekly recurring template ────────────────
-- days_mask is a 7-bit bitmask. Bit 0 = Mon, Bit 1 = Tue, ... Bit 6 = Sun.
-- Mon–Fri = 31, Mon–Sat = 63, Mon–Sun = 127.
CREATE TABLE IF NOT EXISTS class_slots (
  id           BIGINT UNSIGNED   NOT NULL AUTO_INCREMENT,
  discipline   VARCHAR(40)       NOT NULL,
  days_mask    SMALLINT UNSIGNED NOT NULL DEFAULT 31,
  start_time   TIME              NOT NULL,
  end_time     TIME              NOT NULL,
  capacity     SMALLINT UNSIGNED NOT NULL DEFAULT 15,
  instructor   VARCHAR(120)      NULL,
  intensive    TINYINT(1)        NOT NULL DEFAULT 0,
  active       TINYINT(1)        NOT NULL DEFAULT 1,
  sort_order   SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  created_at   TIMESTAMP         NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at   TIMESTAMP         NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_slot_active (active),
  KEY idx_slot_sort (sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Class instances: date-materialized from a slot ────────
CREATE TABLE IF NOT EXISTS class_instances (
  id          BIGINT UNSIGNED   NOT NULL AUTO_INCREMENT,
  slot_id     BIGINT UNSIGNED   NOT NULL,
  class_date  DATE              NOT NULL,
  capacity    SMALLINT UNSIGNED NOT NULL,
  status      ENUM('open','closed','cancelled') NOT NULL DEFAULT 'open',
  created_at  TIMESTAMP         NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_slot_date (slot_id, class_date),
  KEY idx_class_date (class_date),
  CONSTRAINT fk_inst_slot FOREIGN KEY (slot_id)
    REFERENCES class_slots(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Bookings: user ↔ class instance (capacity-aware) ─────
CREATE TABLE IF NOT EXISTS bookings (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id     BIGINT UNSIGNED NOT NULL,
  instance_id BIGINT UNSIGNED NOT NULL,
  status      ENUM('confirmed','cancelled') NOT NULL DEFAULT 'confirmed',
  notes       VARCHAR(255)    NULL,
  created_at  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_user_instance (user_id, instance_id),
  KEY idx_bk_user (user_id),
  KEY idx_bk_instance (instance_id),
  CONSTRAINT fk_bk_user FOREIGN KEY (user_id)
    REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_bk_inst FOREIGN KEY (instance_id)
    REFERENCES class_instances(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Belts: per-user, per-discipline rank progression ──────
CREATE TABLE IF NOT EXISTS belts (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id     BIGINT UNSIGNED NOT NULL,
  discipline  VARCHAR(40)     NOT NULL,
  rank_name   VARCHAR(60)     NOT NULL,
  awarded_on  DATE            NOT NULL,
  notes       VARCHAR(255)    NULL,
  created_at  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_belt_user (user_id),
  CONSTRAINT fk_belt_user FOREIGN KEY (user_id)
    REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;