-- 009_room_rev.sql — Room Revenue detail matriks (WP-11C, Kontrak Sprint 11 / ADR-015)
-- room_type/room_segment = master SEBAGAI DATA (ADR-015a): tambah tipe kamar/segmen =
-- INSERT baris, tanpa deploy. room_rev_detail = matriks version x tipe x segmen x bulan,
-- hanya Nts & Price disimpan (Rev SELALU dihitung ulang, ADR-015b). FK room_type/room_segment
-- ON DELETE RESTRICT (master tidak boleh terhapus selama masih dipakai). version_id TANPA FK
-- (pola ADR-003, sama seperti budget_line/driver_value terhadap budget_version.id literal).

CREATE TABLE room_type (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    code VARCHAR(30) NOT NULL,
    name VARCHAR(80) NOT NULL,
    inventory INT NOT NULL DEFAULT 0,
    sort_order INT NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    PRIMARY KEY (id),
    UNIQUE KEY uq_room_type_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE room_segment (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    code VARCHAR(30) NOT NULL,
    name VARCHAR(80) NOT NULL,
    group_name VARCHAR(40) NOT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    PRIMARY KEY (id),
    UNIQUE KEY uq_room_segment_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE room_rev_detail (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    version_id INT UNSIGNED NOT NULL,
    room_type_id INT UNSIGNED NOT NULL,
    segment_id INT UNSIGNED NOT NULL,
    month TINYINT NOT NULL,
    nights DECIMAL(12,4) NOT NULL DEFAULT 0,
    price DECIMAL(18,2) NOT NULL DEFAULT 0,
    updated_by INT UNSIGNED NULL,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_room_rev_detail (version_id, room_type_id, segment_id, month),
    KEY idx_room_rev_detail_version (version_id),
    CONSTRAINT fk_room_rev_detail_type FOREIGN KEY (room_type_id) REFERENCES room_type (id) ON DELETE RESTRICT,
    CONSTRAINT fk_room_rev_detail_segment FOREIGN KEY (segment_id) REFERENCES room_segment (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;