-- =====================================================================
-- KATALIS - Skema Database MySQL
-- Migrasi dari Google Sheets (Google Apps Script) ke MySQL
-- Jalankan file ini SEBELUM seed.sql
-- =====================================================================

SET NAMES utf8mb4;

-- ---------------------------------------------------------------------
-- Tabel: users
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS users (
    id          VARCHAR(10)  NOT NULL PRIMARY KEY,
    nama        VARCHAR(150) NOT NULL,
    email       VARCHAR(150) NOT NULL UNIQUE,
    telepon     VARCHAR(30)  DEFAULT NULL,
    kategori    VARCHAR(50)  DEFAULT NULL,
    role        VARCHAR(20)  NOT NULL DEFAULT 'PEMOHON',
    status      VARCHAR(20)  NOT NULL DEFAULT 'PENDING',
    password    VARCHAR(255) NOT NULL,
    opd         VARCHAR(150) DEFAULT NULL,
    created_at  VARCHAR(40)  DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- Tabel: assets
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS assets (
    id                      VARCHAR(10)  NOT NULL PRIMARY KEY,
    nama                    VARCHAR(255) NOT NULL,
    kategori                VARCHAR(100) DEFAULT NULL,
    kecamatan               VARCHAR(100) DEFAULT NULL,
    desa                    VARCHAR(100) DEFAULT NULL,
    alamat                  VARCHAR(255) DEFAULT NULL,
    luas                    VARCHAR(50)  DEFAULT NULL,
    kondisi                 VARCHAR(50)  DEFAULT NULL,
    opd                     VARCHAR(150) DEFAULT NULL,
    status_ketersediaan     VARCHAR(50)  DEFAULT 'Tersedia',
    klasifikasi             VARCHAR(50)  DEFAULT NULL,
    harga_est               VARCHAR(100) DEFAULT NULL,
    lat                     DECIMAL(10,6) DEFAULT NULL,
    lng                     DECIMAL(10,6) DEFAULT NULL,
    deskripsi               TEXT,
    youtube_url             TEXT,
    foto_json               TEXT,
    batas_utara             VARCHAR(255) DEFAULT NULL,
    batas_timur             VARCHAR(255) DEFAULT NULL,
    batas_selatan           VARCHAR(255) DEFAULT NULL,
    batas_barat             VARCHAR(255) DEFAULT NULL,
    status_jalan            VARCHAR(255) DEFAULT NULL,
    status_tanah            VARCHAR(255) DEFAULT NULL,
    eksisting               VARCHAR(255) DEFAULT NULL,
    kondisi_tanah           TEXT,
    kondisi_bangunan        VARCHAR(255) DEFAULT NULL,
    jaringan_utilitas       VARCHAR(255) DEFAULT NULL,
    rekomendasi_penggunaan  VARCHAR(255) DEFAULT NULL,
    pusat_kegiatan          VARCHAR(255) DEFAULT NULL,
    swot_json               TEXT,
    url_video               TEXT,
    url_denah               TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- Tabel: applications (Permohonan Sewa)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS applications (
    id                   VARCHAR(10)  NOT NULL PRIMARY KEY,
    asset_id             VARCHAR(10)  DEFAULT NULL,
    asset_nama           VARCHAR(255) DEFAULT NULL,
    applicant_id         VARCHAR(10)  DEFAULT NULL,
    applicant_nama       VARCHAR(150) DEFAULT NULL,
    applicant_telepon    VARCHAR(30)  DEFAULT NULL,
    applicant_email      VARCHAR(150) DEFAULT NULL,
    rencana_penggunaan   VARCHAR(150) DEFAULT NULL,
    durasi_sewa          VARCHAR(100) DEFAULT NULL,
    catatan              TEXT,
    status               VARCHAR(30)  DEFAULT 'Permohonan',
    created_at           VARCHAR(40)  DEFAULT NULL,
    updated_at           VARCHAR(40)  DEFAULT NULL,
    KEY idx_asset_id (asset_id),
    KEY idx_applicant_email (applicant_email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- Tabel: visitor_logs (riwayat kunjungan)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS visitor_logs (
    log_id        INT AUTO_INCREMENT PRIMARY KEY,
    log_timestamp VARCHAR(40) DEFAULT NULL,
    session_id    VARCHAR(50) DEFAULT NULL,
    KEY idx_session (session_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- Tabel: online_sessions (tracking user online real-time)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS online_sessions (
    session_id  VARCHAR(50) NOT NULL PRIMARY KEY,
    last_active DATETIME    NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;