-- =====================================================================
-- SANGGRALOKA ONE — Migration 0009_mkt_init
-- Modul SELL (HotelSell / Sales & Marketing) — semua tabel prefix mkt_.
-- Target: MySQL 8 — ENGINE=InnoDB, utf8mb4_unicode_ci.
-- Acuan: hotelsell/docs/SPEK-HOTELSELL-V1-SEDERHANA.md §1-§7,
--        hotelsell/app/data/hotelsell_mysql.sql (skema teruji reservasi/
--        snapshot/upload map, di-port ke pola property_id + FK bernama),
--        sanggraloka-one/docs/PM-PLAN-SANGGRALOKA-ONE.md §4.
-- Jalankan setelah 0008_hr_init.sql (hr_tasks dipakai sbg papan task
-- terpadu — SELL TIDAK membuat sistem task/reminder sendiri, PM-PLAN §1.2).
-- =====================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 1;
SET default_storage_engine = InnoDB;

-- =====================================================================
-- 1. M1 — MITRA & KONTRAK
-- =====================================================================

-- ---------------------------------------------------------------------
-- mkt_partners
-- ---------------------------------------------------------------------
CREATE TABLE mkt_partners (
    id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    property_id     INT UNSIGNED NOT NULL,
    name            VARCHAR(160) NOT NULL,
    pms_name        VARCHAR(160) NULL COMMENT 'nama persis di report PMS utk auto-match reservasi',
    type            ENUM('travel_agent','ota','wholesaler','corporate','community','local') NOT NULL DEFAULT 'travel_agent',
    pic_name        VARCHAR(120) NULL,
    contact_person  VARCHAR(120) NULL,
    phone           VARCHAR(60)  NULL,
    email           VARCHAR(160) NULL,
    notes           TEXT NULL,
    source          ENUM('manual','pms_auto') NOT NULL DEFAULT 'manual',
    active          TINYINT(1)   NOT NULL DEFAULT 1,
    created_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_mkt_partners_property_pms_name (property_id, pms_name),
    KEY idx_mkt_partners_property_type (property_id, type),
    CONSTRAINT fk_mkt_partners_property FOREIGN KEY (property_id) REFERENCES properties(id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- mkt_contracts — reminder H-60/H-30 (cron/n8n)
-- ---------------------------------------------------------------------
CREATE TABLE mkt_contracts (
    id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    property_id     INT UNSIGNED NOT NULL,
    partner_id      INT UNSIGNED NOT NULL,
    title           VARCHAR(200) NOT NULL,
    start_date      DATE NOT NULL,
    end_date        DATE NOT NULL,
    commission_pct  DECIMAL(5,2) NOT NULL DEFAULT 0,
    allotment       INT UNSIGNED NULL,
    file            VARCHAR(255) NULL,
    notes           TEXT NULL,
    status          ENUM('draft','active','expired','terminated') NOT NULL DEFAULT 'draft',
    created_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_mkt_contracts_property_end (property_id, end_date),
    KEY idx_mkt_contracts_partner (partner_id),
    CONSTRAINT fk_mkt_contracts_property FOREIGN KEY (property_id) REFERENCES properties(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_mkt_contracts_partner FOREIGN KEY (partner_id) REFERENCES mkt_partners(id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- 2. M2 — PRODUK, BOOKING, PEMBAYARAN
-- =====================================================================

-- ---------------------------------------------------------------------
-- mkt_products — 21 experiences + venue (harga diisi S&M, seed §0011)
-- ---------------------------------------------------------------------
CREATE TABLE mkt_products (
    id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    property_id     INT UNSIGNED NOT NULL,
    code            VARCHAR(40)  NOT NULL,
    name            VARCHAR(160) NOT NULL,
    category        ENUM('wedding','experience','daypass','retreat','mice') NOT NULL,
    public_price    DECIMAL(14,2) NULL COMMENT 'NULL = diisi S&M, jangan dikarang',
    partner_price   DECIMAL(14,2) NULL,
    capacity        INT UNSIGNED NULL,
    venue           VARCHAR(120) NULL,
    active          TINYINT(1)   NOT NULL DEFAULT 1,
    created_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_mkt_products_property_code (property_id, code),
    KEY idx_mkt_products_property_category (property_id, category),
    CONSTRAINT fk_mkt_products_property FOREIGN KEY (property_id) REFERENCES properties(id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- mkt_bookings — kalender venue (bentrok ditolak di level aplikasi)
-- ---------------------------------------------------------------------
CREATE TABLE mkt_bookings (
    id                      BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    property_id             INT UNSIGNED NOT NULL,
    product_id              INT UNSIGNED NOT NULL,
    event_date              DATE NOT NULL,
    event_time              TIME NULL,
    pax                     INT UNSIGNED NULL,
    guest_name              VARCHAR(160) NOT NULL,
    source                  ENUM('direct','partner','walk_in','web') NOT NULL DEFAULT 'direct',
    partner_id              INT UNSIGNED NULL,
    status                  ENUM('tentative','confirmed','executed','completed','cancelled','no_show') NOT NULL DEFAULT 'tentative',
    value                   DECIMAL(14,2) NOT NULL DEFAULT 0,
    deposit_paid            DECIMAL(14,2) NOT NULL DEFAULT 0,
    deposit_due_date        DATE NULL,
    theme                   VARCHAR(160) NULL COMMENT 'wedding: tema',
    vendor_notes            TEXT NULL COMMENT 'wedding: vendor/rundown (BEO penuh = fase 2)',
    rundown_file            VARCHAR(255) NULL,
    notes                   TEXT NULL,
    pms_ref                 VARCHAR(60)  NULL COMMENT 'batas tegas: HotelSell tidak pernah menulis reservasi kamar PMS',
    created_at              DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at              DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_mkt_bookings_property_date (property_id, event_date),
    KEY idx_mkt_bookings_property_status (property_id, status),
    KEY idx_mkt_bookings_product (product_id),
    KEY idx_mkt_bookings_partner (partner_id),
    CONSTRAINT fk_mkt_bookings_property FOREIGN KEY (property_id) REFERENCES properties(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_mkt_bookings_product FOREIGN KEY (product_id) REFERENCES mkt_products(id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_mkt_bookings_partner FOREIGN KEY (partner_id) REFERENCES mkt_partners(id)
        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- mkt_payments
-- ---------------------------------------------------------------------
CREATE TABLE mkt_payments (
    id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    booking_id      BIGINT UNSIGNED NOT NULL,
    paid_date       DATE NOT NULL,
    amount          DECIMAL(14,2) NOT NULL,
    proof_file      VARCHAR(255) NULL,
    created_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_mkt_payments_booking (booking_id),
    CONSTRAINT fk_mkt_payments_booking FOREIGN KEY (booking_id) REFERENCES mkt_bookings(id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- 3. M3 — PENAWARAN (KANBAN + reminder 3/5/7)
-- =====================================================================

-- ---------------------------------------------------------------------
-- mkt_quotations — last_status_change, lost_reason, pic_user_id (kontrak
-- PM-PLAN §4 tambahan di atas §3 SPEK-HOTELSELL-V1)
-- ---------------------------------------------------------------------
CREATE TABLE mkt_quotations (
    id                  INT UNSIGNED NOT NULL AUTO_INCREMENT,
    property_id         INT UNSIGNED NOT NULL,
    quote_no            VARCHAR(30)  NOT NULL,
    partner_id          INT UNSIGNED NULL,
    client              VARCHAR(160) NOT NULL DEFAULT '',
    product_id          INT UNSIGNED NULL,
    value               DECIMAL(15,2) NOT NULL DEFAULT 0,
    sent_date           DATE NOT NULL,
    valid_until         DATE NULL COMMENT 'default H+30 (EXPIRED otomatis lewat tanggal ini)',
    status              ENUM('draft','sent','follow_up','nego','won','lost','expired') NOT NULL DEFAULT 'draft',
    last_status_change  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'dasar aturan reminder 3/5/7 hari',
    lost_reason         ENUM('harga','tanggal_penuh','kompetitor','lainnya') NULL,
    lost_reason_detail  VARCHAR(255) NULL COMMENT 'nama kompetitor (Key set) atau catatan bebas',
    pic_user_id         INT UNSIGNED NULL,
    proposal_file       VARCHAR(255) NULL,
    created_at          DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_mkt_quotations_property_quote_no (property_id, quote_no),
    KEY idx_mkt_quotations_property_status (property_id, status, last_status_change),
    KEY idx_mkt_quotations_partner (partner_id),
    KEY idx_mkt_quotations_product (product_id),
    KEY idx_mkt_quotations_pic (pic_user_id),
    CONSTRAINT fk_mkt_quotations_property FOREIGN KEY (property_id) REFERENCES properties(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_mkt_quotations_partner FOREIGN KEY (partner_id) REFERENCES mkt_partners(id)
        ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_mkt_quotations_product FOREIGN KEY (product_id) REFERENCES mkt_products(id)
        ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_mkt_quotations_pic FOREIGN KEY (pic_user_id) REFERENCES users(id)
        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- mkt_quote_log — riwayat status & follow-up, immutable (append-only)
-- ---------------------------------------------------------------------
CREATE TABLE mkt_quote_log (
    id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    quotation_id    INT UNSIGNED NOT NULL,
    from_status     VARCHAR(20)  NULL,
    to_status       VARCHAR(20)  NOT NULL,
    note            VARCHAR(500) NULL COMMENT 'catatan singkat wajib saat pindah kartu kanban',
    changed_by      INT UNSIGNED NULL,
    created_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_mkt_quote_log_quotation (quotation_id, created_at),
    KEY idx_mkt_quote_log_changed_by (changed_by),
    CONSTRAINT fk_mkt_quote_log_quotation FOREIGN KEY (quotation_id) REFERENCES mkt_quotations(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_mkt_quote_log_changed_by FOREIGN KEY (changed_by) REFERENCES users(id)
        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- 4. M4 — KOL (visit, kontrak, photo release)
-- =====================================================================

-- ---------------------------------------------------------------------
-- mkt_kols — status hubungan ENUM SAMA dgn KOL-DATABASE.xlsx
-- ---------------------------------------------------------------------
CREATE TABLE mkt_kols (
    id                  INT UNSIGNED NOT NULL AUTO_INCREMENT,
    property_id         INT UNSIGNED NOT NULL,
    name                VARCHAR(160) NOT NULL,
    platform            VARCHAR(60)  NULL COMMENT 'Instagram/TikTok/YouTube dst',
    niche               VARCHAR(100) NULL,
    market              VARCHAR(60)  NULL COMMENT 'ID/EN/FR/KR dst',
    tier                ENUM('nano','micro','mid','macro','mega') NULL,
    relationship_status ENUM('belum_dihubungi','dihubungi','nego','deal','delivered','evaluasi','ditolak') NOT NULL DEFAULT 'belum_dihubungi' COMMENT '⬜→📞→🤝→✅→📦→⭐/❌ KOL-DATABASE.xlsx',
    rate_card           JSON NULL COMMENT 'per format, HANYA dari penawaran tertulis',
    contact             VARCHAR(160) NULL,
    created_at          DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_mkt_kols_property_status (property_id, relationship_status),
    CONSTRAINT fk_mkt_kols_property FOREIGN KEY (property_id) REFERENCES properties(id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- mkt_kol_visits — reminder H-7/H-1 kedatangan, H+7 deliverable (via
-- hr_tasks, TIDAK sistem reminder sendiri)
-- ---------------------------------------------------------------------
CREATE TABLE mkt_kol_visits (
    id                  INT UNSIGNED NOT NULL AUTO_INCREMENT,
    property_id         INT UNSIGNED NOT NULL,
    kol_id              INT UNSIGNED NOT NULL,
    arrival_date        DATE NOT NULL,
    departure_date      DATE NOT NULL,
    program             TEXT NULL COMMENT 'program/deliverable yang disepakati',
    status              ENUM('dijadwalkan','berlangsung','selesai','batal') NOT NULL DEFAULT 'dijadwalkan',
    room                VARCHAR(20)  NULL,
    host_user_id        INT UNSIGNED NULL,
    created_at          DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_mkt_kol_visits_property_arrival (property_id, arrival_date),
    KEY idx_mkt_kol_visits_kol (kol_id),
    KEY idx_mkt_kol_visits_host (host_user_id),
    CONSTRAINT fk_mkt_kol_visits_property FOREIGN KEY (property_id) REFERENCES properties(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_mkt_kol_visits_kol FOREIGN KEY (kol_id) REFERENCES mkt_kols(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_mkt_kol_visits_host FOREIGN KEY (host_user_id) REFERENCES users(id)
        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- mkt_kol_docs — photo/model release WAJIB sebelum visit "berlangsung"
-- (gerbang ditegakkan di aplikasi, bukan di skema)
-- ---------------------------------------------------------------------
CREATE TABLE mkt_kol_docs (
    id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    kol_id          INT UNSIGNED NOT NULL,
    visit_id        INT UNSIGNED NULL,
    type            ENUM('kontrak','photo_release','lainnya') NOT NULL,
    file            VARCHAR(255) NOT NULL,
    uploaded_by     INT UNSIGNED NULL,
    created_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_mkt_kol_docs_kol (kol_id),
    KEY idx_mkt_kol_docs_visit (visit_id),
    CONSTRAINT fk_mkt_kol_docs_kol FOREIGN KEY (kol_id) REFERENCES mkt_kols(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_mkt_kol_docs_visit FOREIGN KEY (visit_id) REFERENCES mkt_kol_visits(id)
        ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_mkt_kol_docs_uploaded_by FOREIGN KEY (uploaded_by) REFERENCES users(id)
        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- mkt_kol_results
-- ---------------------------------------------------------------------
CREATE TABLE mkt_kol_results (
    id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    kol_id          INT UNSIGNED NOT NULL,
    visit_id        INT UNSIGNED NULL,
    content_link    VARCHAR(255) NULL,
    reach           BIGINT UNSIGNED NULL,
    promo_code      VARCHAR(40)  NULL,
    created_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_mkt_kol_results_kol (kol_id),
    KEY idx_mkt_kol_results_visit (visit_id),
    CONSTRAINT fk_mkt_kol_results_kol FOREIGN KEY (kol_id) REFERENCES mkt_kols(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_mkt_kol_results_visit FOREIGN KEY (visit_id) REFERENCES mkt_kol_visits(id)
        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- 5. M5 — MENU & SPECIAL EVENT STUDIO
-- =====================================================================

-- ---------------------------------------------------------------------
-- mkt_event_ideas
-- ---------------------------------------------------------------------
CREATE TABLE mkt_event_ideas (
    id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    property_id     INT UNSIGNED NOT NULL,
    title           VARCHAR(200) NOT NULL,
    target_date     DATE NULL,
    concept         TEXT NULL,
    status          ENUM('ide','disetujui_gm','dieksekusi','evaluasi') NOT NULL DEFAULT 'ide',
    source_chat_id  BIGINT UNSIGNED NULL,
    created_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_mkt_event_ideas_property_status (property_id, status),
    KEY idx_mkt_event_ideas_source_chat (source_chat_id),
    CONSTRAINT fk_mkt_event_ideas_property FOREIGN KEY (property_id) REFERENCES properties(id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- mkt_menus
-- ---------------------------------------------------------------------
CREATE TABLE mkt_menus (
    id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    property_id     INT UNSIGNED NOT NULL,
    name            VARCHAR(160) NOT NULL,
    outlet          VARCHAR(100) NULL COMMENT 'Meditera Restaurant/Bar/Segening/Sunken Pool Bar',
    items           JSON NULL,
    proposed_price  DECIMAL(14,2) NULL,
    status          ENUM('draft','disetujui_gm','aktif','arsip') NOT NULL DEFAULT 'draft',
    source_chat_id  BIGINT UNSIGNED NULL,
    created_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_mkt_menus_property_status (property_id, status),
    KEY idx_mkt_menus_source_chat (source_chat_id),
    CONSTRAINT fk_mkt_menus_property FOREIGN KEY (property_id) REFERENCES properties(id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- 6. M6 — MARKETING & KEYWORD STRATEGY + AUTO-CREATION
-- =====================================================================

-- ---------------------------------------------------------------------
-- mkt_keywords
-- ---------------------------------------------------------------------
CREATE TABLE mkt_keywords (
    id                  INT UNSIGNED NOT NULL AUTO_INCREMENT,
    property_id         INT UNSIGNED NOT NULL,
    keyword             VARCHAR(200) NOT NULL,
    cluster             VARCHAR(100) NULL,
    market              ENUM('EN','FR','KR','ID') NOT NULL DEFAULT 'EN',
    intent              ENUM('informational','navigational','transactional') NULL,
    target_page         VARCHAR(255) NULL,
    last_position       SMALLINT UNSIGNED NULL,
    checked_at          DATE NULL,
    created_at          DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_mkt_keywords_property_market (property_id, market),
    KEY idx_mkt_keywords_cluster (cluster)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE mkt_keywords
    ADD CONSTRAINT fk_mkt_keywords_property FOREIGN KEY (property_id) REFERENCES properties(id)
        ON DELETE CASCADE ON UPDATE CASCADE;

-- ---------------------------------------------------------------------
-- mkt_generated_content — pipeline otomatis tgl 1 (blog/gads/ig)
-- ---------------------------------------------------------------------
CREATE TABLE mkt_generated_content (
    id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    property_id     INT UNSIGNED NOT NULL,
    type            ENUM('blog','gads','ig') NOT NULL,
    month           CHAR(7) NOT NULL COMMENT 'YYYY-MM',
    content         MEDIUMTEXT NULL,
    status          ENUM('draft','direview','disetujui','dipublikasikan') NOT NULL DEFAULT 'draft',
    reviewer_id     INT UNSIGNED NULL,
    notes           VARCHAR(500) NULL,
    created_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_mkt_generated_content_property_month (property_id, month, type),
    KEY idx_mkt_generated_content_status (property_id, status),
    KEY idx_mkt_generated_content_reviewer (reviewer_id),
    CONSTRAINT fk_mkt_generated_content_property FOREIGN KEY (property_id) REFERENCES properties(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_mkt_generated_content_reviewer FOREIGN KEY (reviewer_id) REFERENCES users(id)
        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- 7. M7 — AI CHATBOT STUDIO (dipakai M5 & M6)
-- =====================================================================

-- ---------------------------------------------------------------------
-- mkt_ai_chats
-- ---------------------------------------------------------------------
CREATE TABLE mkt_ai_chats (
    id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    property_id     INT UNSIGNED NOT NULL,
    user_id         INT UNSIGNED NOT NULL,
    persona         ENUM('event_menu','keyword_campaign') NOT NULL,
    title           VARCHAR(200) NULL,
    created_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_mkt_ai_chats_property_user (property_id, user_id),
    CONSTRAINT fk_mkt_ai_chats_property FOREIGN KEY (property_id) REFERENCES properties(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_mkt_ai_chats_user FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- FK susulan: mkt_event_ideas.source_chat_id / mkt_menus.source_chat_id -> mkt_ai_chats(id)
ALTER TABLE mkt_event_ideas
    ADD CONSTRAINT fk_mkt_event_ideas_source_chat
        FOREIGN KEY (source_chat_id) REFERENCES mkt_ai_chats(id)
        ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE mkt_menus
    ADD CONSTRAINT fk_mkt_menus_source_chat
        FOREIGN KEY (source_chat_id) REFERENCES mkt_ai_chats(id)
        ON DELETE SET NULL ON UPDATE CASCADE;

-- ---------------------------------------------------------------------
-- mkt_ai_messages
-- ---------------------------------------------------------------------
CREATE TABLE mkt_ai_messages (
    id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    chat_id         BIGINT UNSIGNED NOT NULL,
    role            ENUM('system','user','assistant') NOT NULL,
    content         MEDIUMTEXT NOT NULL,
    tokens_in       INT UNSIGNED NULL,
    tokens_out      INT UNSIGNED NULL,
    cost_idr        DECIMAL(12,2) NULL,
    created_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_mkt_ai_messages_chat (chat_id, created_at),
    CONSTRAINT fk_mkt_ai_messages_chat FOREIGN KEY (chat_id) REFERENCES mkt_ai_chats(id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- 8. RESERVASI PMS, SNAPSHOT OTB, UPLOAD MAP
-- Port dari hotelsell/app/data/hotelsell_mysql.sql (mkt_reservations,
-- mkt_uploads -> mkt_otb_snapshots, mkt_colmap -> mkt_upload_maps),
-- ditambah property_id + FK bernama sesuai konvensi Sanggraloka One.
-- READ-ONLY dari sisi kamar: HotelSell tidak pernah menulis reservasi PMS,
-- tabel ini adalah SALINAN report utk analitik OTB/pickup/forecast.
-- =====================================================================

-- ---------------------------------------------------------------------
-- mkt_reservations
-- ---------------------------------------------------------------------
CREATE TABLE mkt_reservations (
    id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    property_id     INT UNSIGNED NOT NULL,
    report_year     SMALLINT UNSIGNED NOT NULL,
    resv_no         VARCHAR(60)  NOT NULL,
    guest_name      VARCHAR(160) NOT NULL DEFAULT '',
    partner_id      INT UNSIGNED NULL,
    agent_raw       VARCHAR(160) NOT NULL DEFAULT '' COMMENT 'nama mitra persis di report PMS sebelum di-match',
    arrival         DATE NOT NULL,
    departure       DATE NULL,
    nights          DECIMAL(6,1) NOT NULL DEFAULT 0,
    room_type       VARCHAR(80)  NOT NULL DEFAULT '',
    pax             DECIMAL(5,1) NOT NULL DEFAULT 0,
    status_raw      VARCHAR(60)  NOT NULL DEFAULT '',
    cancelled       TINYINT(1)   NOT NULL DEFAULT 0,
    rate            DECIMAL(15,2) NOT NULL DEFAULT 0,
    revenue         DECIMAL(15,2) NOT NULL DEFAULT 0,
    segment         VARCHAR(80)  NOT NULL DEFAULT '',
    created_pms     DATE NULL,
    first_seen      DATE NOT NULL,
    last_seen       DATE NOT NULL,
    created_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_mkt_reservations_property_year_resv (property_id, report_year, resv_no),
    KEY idx_mkt_reservations_property_arrival (property_id, report_year, arrival),
    KEY idx_mkt_reservations_partner (partner_id, report_year),
    CONSTRAINT fk_mkt_reservations_property FOREIGN KEY (property_id) REFERENCES properties(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_mkt_reservations_partner FOREIGN KEY (partner_id) REFERENCES mkt_partners(id)
        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- mkt_otb_snapshots — port dari mkt_uploads (hotelsell prototipe)
-- ---------------------------------------------------------------------
CREATE TABLE mkt_otb_snapshots (
    id                  INT UNSIGNED NOT NULL AUTO_INCREMENT,
    property_id         INT UNSIGNED NOT NULL,
    report_year         SMALLINT UNSIGNED NOT NULL,
    report_date         DATE NOT NULL,
    filename            VARCHAR(255) NOT NULL,
    rows_total          INT UNSIGNED NOT NULL DEFAULT 0,
    rows_new            INT UNSIGNED NOT NULL DEFAULT 0,
    rows_changed        INT UNSIGNED NOT NULL DEFAULT 0,
    rows_cancelled      INT UNSIGNED NOT NULL DEFAULT 0,
    otb_json            MEDIUMTEXT NULL COMMENT 'snapshot OTB per bulan utk pickup/forecast',
    uploaded_by         INT UNSIGNED NULL,
    created_at          DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_mkt_otb_snapshots_property_year_date (property_id, report_year, report_date),
    KEY idx_mkt_otb_snapshots_uploaded_by (uploaded_by),
    CONSTRAINT fk_mkt_otb_snapshots_property FOREIGN KEY (property_id) REFERENCES properties(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_mkt_otb_snapshots_uploaded_by FOREIGN KEY (uploaded_by) REFERENCES users(id)
        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- mkt_upload_maps — port dari mkt_colmap (pemetaan kolom tersimpan/tahun)
-- ---------------------------------------------------------------------
CREATE TABLE mkt_upload_maps (
    id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    property_id     INT UNSIGNED NOT NULL,
    report_year     SMALLINT UNSIGNED NOT NULL,
    map_json        TEXT NOT NULL COMMENT 'pemetaan header kolom xlsx/csv -> field mkt_reservations',
    created_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_mkt_upload_maps_property_year (property_id, report_year),
    CONSTRAINT fk_mkt_upload_maps_property FOREIGN KEY (property_id) REFERENCES properties(id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- END 0009_mkt_init.sql
-- Catatan penyimpangan/keputusan:
-- 1. Semua tabel mkt_ ber-property_id eksplisit (multi-property sejak
--    Fase 1, PM-PLAN §4) — prototipe hotelsell/app single-tenant tidak
--    punya property_id; ditambahkan di sini sesuai kontrak Sanggraloka One.
-- 2. mkt_partners menggantikan mkt_agents prototipe (istilah "partner"
--    dipakai konsisten sesuai SPEK-HOTELSELL-V1 M1); pms_name dipertahankan
--    utk auto-match reservasi seperti agents.php prototipe.
-- 3. mkt_reservations.partner_id menggantikan agent_id prototipe (FK ke
--    mkt_partners, bukan mkt_agents).
-- 4. mkt_quotations: quote_no UNIQUE per property (bukan global) —
--    konsisten pola assets.asset_code/wo_number di 0001_init.sql.
-- 5. mkt_kols.relationship_status: ENUM value dalam Bahasa Indonesia
--    (belum_dihubungi..ditolak) merepresentasikan simbol KOL-DATABASE.xlsx
--    (⬜→📞→🤝→✅→📦→⭐/❌); pemetaan simbol->label ditangani di UI, bukan DB.
-- 6. mkt_products.public_price/partner_price dibiarkan NULL secara default
--    (bukan DEFAULT 0) — sesuai instruksi "harga NULL komentar diisi S&M",
--    membedakan "belum ditetapkan" dari "gratis".
-- =====================================================================