-- 001_capacity_model.sql
-- Brings the legacy flat `bookings` table forward into the slot/instance model.
-- Safe to re-run: every change is guarded.

-- 1. Drop the old flat bookings table if it has the legacy `class_time` column.
--    Pre-launch: there is no production data to preserve.
DROP TABLE IF EXISTS bookings;

-- 2. class_slots template
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;

-- 3. class_instances (date-materialized)
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;

-- 4. New bookings: FK on instance, unique per user+instance
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;

-- 5. Belts (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;