-- =====================================================
-- DATABASE SCHEMA: Apps Kasir Griya Finance
-- Studi Kasus: Warmindo Pejoeang Comal
-- =====================================================
-- Framework: CodeIgniter 4
-- Database: MySQL
-- =====================================================

-- Hapus database jika sudah ada (HATI-HATI!)
-- DROP DATABASE IF EXISTS apps_kasir_griya_finance;

-- Buat database
CREATE DATABASE IF NOT EXISTS apps_kasir_griya_finance 
CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

USE apps_kasir_griya_finance;

-- =====================================================
-- 1. TABEL ROLES (Master Role)
-- =====================================================
CREATE TABLE roles (
    id_role INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    nama_role VARCHAR(50) NOT NULL,
    deskripsi TEXT NULL,
    created_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id_role),
    UNIQUE KEY unique_nama_role (nama_role)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- =====================================================
-- 2. TABEL USERS (User Kasir & Owner)
-- =====================================================
CREATE TABLE users (
    id_user INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    password VARCHAR(255) NOT NULL,
    nama_lengkap VARCHAR(100) NOT NULL,
    email VARCHAR(100) NULL,
    no_telepon VARCHAR(20) NULL,
    id_role INT(11) UNSIGNED NOT NULL,
    status_aktif ENUM('aktif', 'nonaktif') NOT NULL DEFAULT 'aktif',
    foto_profil VARCHAR(255) NULL,
    last_login DATETIME NULL,
    created_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id_user),
    UNIQUE KEY unique_username (username),
    KEY idx_role (id_role),
    KEY idx_status (status_aktif),
    CONSTRAINT fk_users_role FOREIGN KEY (id_role) REFERENCES roles (id_role) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- =====================================================
-- 3. TABEL KATEGORI_BARANG (Kategori Menu/Barang)
-- =====================================================
CREATE TABLE kategori_barang (
    id_kategori INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    nama_kategori VARCHAR(100) NOT NULL,
    deskripsi TEXT NULL,
    icon VARCHAR(50) NULL,
    urutan INT(11) NULL DEFAULT 0,
    status_aktif ENUM('aktif', 'nonaktif') NOT NULL DEFAULT 'aktif',
    created_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id_kategori),
    UNIQUE KEY unique_nama_kategori (nama_kategori),
    KEY idx_status (status_aktif)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- =====================================================
-- 4. TABEL BARANG (Master Barang/Menu)
-- =====================================================
CREATE TABLE barang (
    id_barang INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    kode_barang VARCHAR(50) NULL,
    nama_barang VARCHAR(200) NOT NULL,
    deskripsi TEXT NULL,
    harga DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    stok INT(11) NOT NULL DEFAULT 0,
    stok_minimum INT(11) NULL DEFAULT 0,
    id_kategori INT(11) UNSIGNED NULL,
    satuan VARCHAR(20) NULL DEFAULT 'pcs',
    foto_barang VARCHAR(255) NULL,
    status_aktif ENUM('aktif', 'nonaktif') NOT NULL DEFAULT 'aktif',
    created_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by INT(11) UNSIGNED NULL,
    PRIMARY KEY (id_barang),
    UNIQUE KEY unique_kode_barang (kode_barang),
    KEY idx_kategori (id_kategori),
    KEY idx_status (status_aktif),
    KEY idx_nama (nama_barang),
    CONSTRAINT fk_barang_kategori FOREIGN KEY (id_kategori) REFERENCES kategori_barang (id_kategori) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_barang_created_by FOREIGN KEY (created_by) REFERENCES users (id_user) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- =====================================================
-- 5. TABEL TRANSAKSI (Header Transaksi/Penjualan)
-- =====================================================
CREATE TABLE transaksi (
    id_transaksi INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    kode_pembayaran VARCHAR(50) NOT NULL,
    tanggal_transaksi DATE NOT NULL,
    jam_transaksi TIME NOT NULL,
    total_item INT(11) NOT NULL DEFAULT 0,
    total_harga DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    diskon DECIMAL(12,2) NULL DEFAULT 0.00,
    total_bayar DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    status_pembayaran ENUM('menunggu', 'dibayar', 'batal') NOT NULL DEFAULT 'menunggu',
    metode_pembayaran VARCHAR(50) NULL,
    catatan TEXT NULL,
    dibuat_oleh INT(11) UNSIGNED NULL COMMENT 'User yang membuat transaksi (boleh null jika User Non Login)',
    diacc_kasir INT(11) UNSIGNED NULL COMMENT 'User kasir yang ACC transaksi',
    tanggal_acc DATETIME NULL,
    created_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id_transaksi),
    UNIQUE KEY unique_kode_pembayaran (kode_pembayaran),
    KEY idx_tanggal (tanggal_transaksi),
    KEY idx_status (status_pembayaran),
    KEY idx_dibuat_oleh (dibuat_oleh),
    KEY idx_diacc_kasir (diacc_kasir),
    CONSTRAINT fk_transaksi_dibuat_oleh FOREIGN KEY (dibuat_oleh) REFERENCES users (id_user) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_transaksi_diacc_kasir FOREIGN KEY (diacc_kasir) REFERENCES users (id_user) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- =====================================================
-- 6. TABEL TRANSAKSI_DETAIL (Detail Item Transaksi)
-- =====================================================
CREATE TABLE transaksi_detail (
    id_detail INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    id_transaksi INT(11) UNSIGNED NOT NULL,
    id_barang INT(11) UNSIGNED NOT NULL,
    jumlah INT(11) NOT NULL DEFAULT 1,
    harga_satuan DECIMAL(12,2) NOT NULL,
    diskon_item DECIMAL(12,2) NULL DEFAULT 0.00,
    subtotal DECIMAL(12,2) NOT NULL,
    catatan_item TEXT NULL,
    created_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id_detail),
    KEY idx_transaksi (id_transaksi),
    KEY idx_barang (id_barang),
    CONSTRAINT fk_detail_transaksi FOREIGN KEY (id_transaksi) REFERENCES transaksi (id_transaksi) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_detail_barang FOREIGN KEY (id_barang) REFERENCES barang (id_barang) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- =====================================================
-- 7. TABEL STOK_LOG (Log Pergerakan Stok)
-- =====================================================
CREATE TABLE stok_log (
    id_log INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    id_barang INT(11) UNSIGNED NOT NULL,
    jenis_transaksi ENUM('masuk', 'keluar', 'penyesuaian') NOT NULL,
    jumlah INT(11) NOT NULL,
    stok_sebelum INT(11) NOT NULL,
    stok_sesudah INT(11) NOT NULL,
    keterangan TEXT NULL,
    id_transaksi INT(11) UNSIGNED NULL COMMENT 'Jika terkait transaksi penjualan',
    id_user INT(11) UNSIGNED NULL COMMENT 'User yang melakukan perubahan stok',
    created_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id_log),
    KEY idx_barang (id_barang),
    KEY idx_transaksi (id_transaksi),
    KEY idx_user (id_user),
    KEY idx_tanggal (created_at),
    CONSTRAINT fk_stok_log_barang FOREIGN KEY (id_barang) REFERENCES barang (id_barang) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_stok_log_transaksi FOREIGN KEY (id_transaksi) REFERENCES transaksi (id_transaksi) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_stok_log_user FOREIGN KEY (id_user) REFERENCES users (id_user) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- =====================================================
-- 8. TABEL LAPORAN_BARANG_TERLARIS (Materialized View)
-- =====================================================
-- Tabel ini bisa diisi via trigger atau cron job
-- untuk mempermudah query laporan barang terlaris
CREATE TABLE laporan_barang_terlaris (
    id_laporan INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    id_barang INT(11) UNSIGNED NOT NULL,
    periode_bulan INT(2) NOT NULL,
    periode_tahun INT(4) NOT NULL,
    total_terjual INT(11) NOT NULL DEFAULT 0,
    total_pendapatan DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id_laporan),
    UNIQUE KEY unique_barang_periode (id_barang, periode_bulan, periode_tahun),
    KEY idx_periode (periode_tahun, periode_bulan),
    KEY idx_barang (id_barang),
    CONSTRAINT fk_laporan_barang FOREIGN KEY (id_barang) REFERENCES barang (id_barang) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- =====================================================
-- 9. TABEL LAPORAN_KEUANGAN (Materialized View)
-- =====================================================
-- Tabel untuk menyimpan ringkasan keuangan per hari/bulan
CREATE TABLE laporan_keuangan (
    id_laporan INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    tanggal DATE NOT NULL,
    total_transaksi INT(11) NOT NULL DEFAULT 0,
    total_pendapatan DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    total_diskon DECIMAL(12,2) NULL DEFAULT 0.00,
    metode_pembayaran_tunai DECIMAL(12,2) NULL DEFAULT 0.00,
    metode_pembayaran_qris DECIMAL(12,2) NULL DEFAULT 0.00,
    metode_pembayaran_lainnya DECIMAL(12,2) NULL DEFAULT 0.00,
    updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id_laporan),
    UNIQUE KEY unique_tanggal (tanggal),
    KEY idx_tanggal (tanggal)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- =====================================================
-- END OF SCHEMA
-- =====================================================