-- =============================================
--  POS SYSTEM — database.sql
--  Import file ini ke phpMyAdmin
-- =============================================

CREATE DATABASE IF NOT EXISTS pos_system
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE pos_system;

-- ───────────────────────────────────────────
-- TABEL: akun (admin & kasir)
-- ───────────────────────────────────────────
CREATE TABLE akun (
  id       INT AUTO_INCREMENT PRIMARY KEY,
  nama     VARCHAR(100)  NOT NULL,
  username VARCHAR(50)   NOT NULL UNIQUE,
  password VARCHAR(255)  NOT NULL,          -- bcrypt hash
  role     ENUM('admin','kasir') NOT NULL DEFAULT 'kasir',
  aktif    TINYINT(1) NOT NULL DEFAULT 1,
  dibuat   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ───────────────────────────────────────────
-- TABEL: kategori
-- ───────────────────────────────────────────
CREATE TABLE kategori (
  id    INT AUTO_INCREMENT PRIMARY KEY,
  nama  VARCHAR(100) NOT NULL UNIQUE
) ENGINE=InnoDB;

-- ───────────────────────────────────────────
-- TABEL: barang
-- ───────────────────────────────────────────
CREATE TABLE barang (
  id           INT AUTO_INCREMENT PRIMARY KEY,
  kode         VARCHAR(50)    NOT NULL UNIQUE,
  nama         VARCHAR(150)   NOT NULL,
  kategori_id  INT            NOT NULL,
  harga_beli   DECIMAL(15,2)  NOT NULL DEFAULT 0,
  harga_jual   DECIMAL(15,2)  NOT NULL DEFAULT 0,
  stok         INT            NOT NULL DEFAULT 0,
  satuan       VARCHAR(30)    NOT NULL DEFAULT 'pcs',
  aktif        TINYINT(1)     NOT NULL DEFAULT 1,
  dibuat       DATETIME       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (kategori_id) REFERENCES kategori(id)
) ENGINE=InnoDB;

-- ───────────────────────────────────────────
-- TABEL: transaksi (header)
-- ───────────────────────────────────────────
CREATE TABLE transaksi (
  id           INT AUTO_INCREMENT PRIMARY KEY,
  no_transaksi VARCHAR(30)   NOT NULL UNIQUE,
  kasir_id     INT           NOT NULL,
  total        DECIMAL(15,2) NOT NULL DEFAULT 0,
  bayar        DECIMAL(15,2) NOT NULL DEFAULT 0,
  kembalian    DECIMAL(15,2) NOT NULL DEFAULT 0,
  metode       ENUM('tunai','transfer','qris') NOT NULL DEFAULT 'tunai',
  tanggal      DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (kasir_id) REFERENCES akun(id)
) ENGINE=InnoDB;

-- ───────────────────────────────────────────
-- TABEL: detail_transaksi (item per transaksi)
-- ───────────────────────────────────────────
CREATE TABLE detail_transaksi (
  id             INT AUTO_INCREMENT PRIMARY KEY,
  transaksi_id   INT           NOT NULL,
  barang_id      INT           NOT NULL,
  nama_barang    VARCHAR(150)  NOT NULL,
  harga_satuan   DECIMAL(15,2) NOT NULL,
  qty            INT           NOT NULL,
  subtotal       DECIMAL(15,2) NOT NULL,
  FOREIGN KEY (transaksi_id) REFERENCES transaksi(id) ON DELETE CASCADE,
  FOREIGN KEY (barang_id)    REFERENCES barang(id)
) ENGINE=InnoDB;

-- =============================================
--  DATA AWAL (SEED)
-- =============================================

-- Akun: password semua = "password123" (bcrypt)
INSERT INTO akun (nama, username, password, role) VALUES
('Administrator', 'admin',  '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin'),
('Kasir Satu',    'kasir1', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'kasir'),
('Kasir Dua',     'kasir2', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'kasir');

INSERT INTO kategori (nama) VALUES
('Minuman'), ('Makanan'), ('Snack'), ('Rokok'), ('Lainnya');

INSERT INTO barang (kode, nama, kategori_id, harga_beli, harga_jual, stok, satuan) VALUES
('BRG001', 'Aqua 600ml',       1,  2000,  3000,  100, 'botol'),
('BRG002', 'Teh Botol Sosro',  1,  3500,  5000,   80, 'botol'),
('BRG003', 'Coca Cola 330ml',  1,  5000,  7000,   60, 'kaleng'),
('BRG004', 'Indomie Goreng',   2,  2500,  3500,  150, 'bungkus'),
('BRG005', 'Roti Tawar Sari',  2,  8000, 12000,   40, 'bungkus'),
('BRG006', 'Chitato 68g',      3,  7000, 10000,   50, 'bungkus'),
('BRG007', 'Oreo Vanila',      3,  5000,  7000,   60, 'bungkus'),
('BRG008', 'Marlboro Merah',   4, 22000, 26000,   30, 'bungkus'),
('BRG009', 'Surya Pro Mild',   4, 18000, 22000,   30, 'bungkus'),
('BRG010', 'Pulpen Pilot',     5,  2000,  3500,   80, 'pcs');

-- =============================================
--  PERBAIKAN: Pastikan kategori 'Lainnya' ada
--  Jalankan query ini jika barang tidak muncul
-- =============================================
INSERT IGNORE INTO kategori (id, nama) VALUES
(1, 'Minuman'), (2, 'Makanan'), (3, 'Snack'), (4, 'Rokok'), (5, 'Lainnya');
