-- 012_fb_cover_rev.sql — F&B Cover & Revenue (WP-12C, Kontrak Sprint 12 / ADR-016)
-- fb_outlet/fb_meal_period = master SEBAGAI DATA (pola ADR-015a room_type/room_segment):
-- tambah outlet/meal period = INSERT baris, tanpa deploy. fb_cover = matriks version x
-- meal_period x bulan (PUNYA dimensi bulan, pola room_rev_detail.nights). fb_avg_check =
-- version x meal_period x revenue_type, SATU nilai TAHUNAN (TIDAK punya dimensi bulan —
-- penyimpangan SENGAJA dari pola room_rev_detail.price, ADR-016d: avg check terbukti
-- konstan sepanjang tahun per meal period x Food/Beverage, BUKAN bulanan). Revenue SELALU
-- dihitung (cover x avg_check), TIDAK PERNAH disimpan sbg kolom terpisah (pola ADR-015b).
-- version_id TANPA FK (ADR-003). food_account_id/beverage_account_id nullable (skema tahan
-- outlet yg kelak tak punya salah satu jenis akun).

CREATE TABLE fb_outlet (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    code VARCHAR(20) NOT NULL,
    name VARCHAR(80) NOT NULL,
    food_account_id INT UNSIGNED NULL,
    beverage_account_id INT UNSIGNED NULL,
    sort_order INT NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    PRIMARY KEY (id),
    UNIQUE KEY uq_fb_outlet_code (code),
    CONSTRAINT fk_fb_outlet_food_account FOREIGN KEY (food_account_id) REFERENCES coa_account (id) ON DELETE RESTRICT,
    CONSTRAINT fk_fb_outlet_bev_account FOREIGN KEY (beverage_account_id) REFERENCES coa_account (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE fb_meal_period (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    outlet_id INT UNSIGNED NOT NULL,
    code VARCHAR(40) NOT NULL,
    name VARCHAR(80) NOT NULL,
    is_breakfast TINYINT(1) 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_fb_meal_period (outlet_id, code),
    CONSTRAINT fk_fb_meal_period_outlet FOREIGN KEY (outlet_id) REFERENCES fb_outlet (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE fb_cover (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    version_id INT UNSIGNED NOT NULL,
    meal_period_id INT UNSIGNED NOT NULL,
    month TINYINT NOT NULL,
    cover DECIMAL(12,4) 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_fb_cover (version_id, meal_period_id, month),
    KEY idx_fb_cover_version (version_id),
    CONSTRAINT fk_fb_cover_period FOREIGN KEY (meal_period_id) REFERENCES fb_meal_period (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE fb_avg_check (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    version_id INT UNSIGNED NOT NULL,
    meal_period_id INT UNSIGNED NOT NULL,
    revenue_type ENUM('FOOD','BEVERAGE') NOT NULL,
    avg_check 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_fb_avg_check (version_id, meal_period_id, revenue_type),
    KEY idx_fb_avg_check_version (version_id),
    CONSTRAINT fk_fb_avg_check_period FOREIGN KEY (meal_period_id) REFERENCES fb_meal_period (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;