-- =====================================================================
-- Skema Database CMS Fakultas
-- Jalankan: mysql -u root -p < database/schema.sql
-- =====================================================================

CREATE DATABASE IF NOT EXISTS fakultas_cms CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE fakultas_cms;

-- ---------------------------------------------------------------------
-- 1. Identitas Kampus
-- ---------------------------------------------------------------------
CREATE TABLE identitas_kampus (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    nama_fakultas VARCHAR(150) NOT NULL,
    singkatan     VARCHAR(20)  NULL,
    tagline       VARCHAR(255) NULL,
    logo          VARCHAR(255) NULL,
    favicon       VARCHAR(255) NULL,
    alamat        TEXT NULL,
    telepon       VARCHAR(50) NULL,
    email         VARCHAR(100) NULL,
    facebook      VARCHAR(255) NULL,
    instagram     VARCHAR(255) NULL,
    youtube       VARCHAR(255) NULL,
    twitter       VARCHAR(255) NULL,
    hero_image    VARCHAR(255) NULL,
    hero_judul    VARCHAR(150) NULL,
    hero_subjudul VARCHAR(255) NULL,
    created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- 2. Menu Navbar Dinamis (mendukung submenu via parent_id)
-- ---------------------------------------------------------------------
CREATE TABLE menus (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    label       VARCHAR(100) NOT NULL,
    url         VARCHAR(255) NOT NULL,
    tipe        ENUM('halaman', 'berita', 'eksternal', 'custom') DEFAULT 'custom',
    parent_id   INT NULL,
    urutan      INT DEFAULT 0,
    target_blank TINYINT(1) DEFAULT 0,
    is_active   TINYINT(1) DEFAULT 1,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (parent_id) REFERENCES menus(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- 3. Halaman Statis (sambutan Dekan, Visi Misi, dll)
-- ---------------------------------------------------------------------
CREATE TABLE halaman_statis (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    judul       VARCHAR(150) NOT NULL,
    slug        VARCHAR(170) NOT NULL UNIQUE,
    konten      LONGTEXT NULL,
    gambar      VARCHAR(255) NULL,
    is_active   TINYINT(1) DEFAULT 1,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- 4. Modul Berita (konten dari TinyMCE)
-- ---------------------------------------------------------------------
CREATE TABLE kategori_berita (
    id    INT AUTO_INCREMENT PRIMARY KEY,
    nama  VARCHAR(100) NOT NULL,
    slug  VARCHAR(120) NOT NULL UNIQUE
) ENGINE=InnoDB;

CREATE TABLE berita (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    judul        VARCHAR(200) NOT NULL,
    slug         VARCHAR(220) NOT NULL UNIQUE,
    kategori_id  INT NULL,
    ringkasan    VARCHAR(300) NULL,
    konten       LONGTEXT NULL,       -- hasil editor TinyMCE
    thumbnail    VARCHAR(255) NULL,
    penulis      VARCHAR(100) NULL,
    status       ENUM('draft','terbit') DEFAULT 'draft',
    tanggal      DATETIME NOT NULL,
    dilihat      INT DEFAULT 0,
    created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (kategori_id) REFERENCES kategori_berita(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- 5. Modul Agenda
-- ---------------------------------------------------------------------
CREATE TABLE agenda (
    id             INT AUTO_INCREMENT PRIMARY KEY,
    judul          VARCHAR(200) NOT NULL,
    deskripsi      TEXT NULL,
    lokasi         VARCHAR(200) NULL,
    tanggal_mulai  DATETIME NOT NULL,
    tanggal_selesai DATETIME NULL,
    gambar         VARCHAR(255) NULL,
    created_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- 6. Galeri Foto & Video
-- ---------------------------------------------------------------------
CREATE TABLE galeri_foto (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    judul      VARCHAR(150) NOT NULL,
    deskripsi  TEXT NULL,
    cover      VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE galeri_foto_detail (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    galeri_id  INT NOT NULL,
    gambar     VARCHAR(255) NOT NULL,
    caption    VARCHAR(200) NULL,
    urutan     INT DEFAULT 0,
    FOREIGN KEY (galeri_id) REFERENCES galeri_foto(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE galeri_video (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    judul      VARCHAR(150) NOT NULL,
    url_video  VARCHAR(255) NOT NULL,   -- Youtube/Vimeo embed url
    thumbnail  VARCHAR(255) NULL,
    deskripsi  TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- 7. Fasilitas
-- ---------------------------------------------------------------------
CREATE TABLE fasilitas (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    nama       VARCHAR(150) NOT NULL,
    deskripsi  TEXT NULL,
    gambar     VARCHAR(255) NULL,
    icon       VARCHAR(100) NULL,
    urutan     INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- 8. Widget Sidebar (gambar / teks / tautan)
-- ---------------------------------------------------------------------
CREATE TABLE widgets (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    judul      VARCHAR(150) NULL,
    tipe       ENUM('gambar','teks','html','tautan') DEFAULT 'teks',
    konten     TEXT NULL,
    gambar     VARCHAR(255) NULL,
    url        VARCHAR(255) NULL,
    posisi     ENUM('sidebar','footer') DEFAULT 'sidebar',
    urutan     INT DEFAULT 0,
    is_active  TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- 9. Footer Dinamis
-- ---------------------------------------------------------------------
CREATE TABLE footer_columns (
    id      INT AUTO_INCREMENT PRIMARY KEY,
    judul   VARCHAR(100) NOT NULL,
    urutan  INT DEFAULT 0
) ENGINE=InnoDB;

CREATE TABLE footer_links (
    id                INT AUTO_INCREMENT PRIMARY KEY,
    footer_column_id  INT NOT NULL,
    label             VARCHAR(150) NOT NULL,
    url               VARCHAR(255) NOT NULL,
    urutan            INT DEFAULT 0,
    FOREIGN KEY (footer_column_id) REFERENCES footer_columns(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- 10. Admin / Pengguna Panel
-- ---------------------------------------------------------------------
CREATE TABLE admin_users (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    nama        VARCHAR(100) NOT NULL,
    username    VARCHAR(50) NOT NULL UNIQUE,
    email       VARCHAR(100) NOT NULL UNIQUE,
    password    VARCHAR(255) NOT NULL,
    role        ENUM('super_admin','editor') DEFAULT 'editor',
    last_login  DATETIME NULL,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;
