-- ============================================================
-- DATABASE: KASIR MULTI-TENANT
-- Arsitektur: Superadmin → Toko (Owner) → Kasir
-- ============================================================

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

-- Toko (tenant)
CREATE TABLE toko (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    nama          VARCHAR(150) NOT NULL,
    slug          VARCHAR(100) NOT NULL UNIQUE,
    alamat        TEXT,
    telepon       VARCHAR(30),
    logo          VARCHAR(255),
    struk_footer  VARCHAR(255) DEFAULT 'Terima kasih sudah mampir!',
    pajak_persen  DECIMAL(5,2) DEFAULT 0,
    is_active     TINYINT(1) DEFAULT 1,
    expired_at    DATE NULL,
    created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Users semua level
CREATE TABLE users (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    toko_id    INT NULL,
    nama       VARCHAR(100) NOT NULL,
    username   VARCHAR(80) NOT NULL UNIQUE,
    email      VARCHAR(150),
    password   VARCHAR(255) NOT NULL,
    role       ENUM('superadmin','owner','kasir') NOT NULL DEFAULT 'kasir',
    is_active  TINYINT(1) DEFAULT 1,
    last_login TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (toko_id) REFERENCES toko(id) ON DELETE CASCADE
);

-- Kategori per toko
CREATE TABLE kategori (
    id       INT AUTO_INCREMENT PRIMARY KEY,
    toko_id  INT NOT NULL,
    nama     VARCHAR(100) NOT NULL,
    urutan   INT DEFAULT 0,
    FOREIGN KEY (toko_id) REFERENCES toko(id) ON DELETE CASCADE
);

-- Produk per toko
CREATE TABLE produk (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    toko_id       INT NOT NULL,
    kategori_id   INT,
    nama          VARCHAR(150) NOT NULL,
    deskripsi     TEXT,
    barcode       VARCHAR(100),
    harga         DECIMAL(10,2) NOT NULL DEFAULT 0,
    stok          INT DEFAULT 0,
    stok_minimum  INT DEFAULT 5,
    satuan        VARCHAR(20) DEFAULT 'pcs',
    gambar        VARCHAR(255),
    is_active     TINYINT(1) DEFAULT 1,
    created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (toko_id)     REFERENCES toko(id) ON DELETE CASCADE,
    FOREIGN KEY (kategori_id) REFERENCES kategori(id) ON DELETE SET NULL,
    UNIQUE KEY unique_barcode_per_toko (toko_id, barcode)
);

-- Transaksi
CREATE TABLE transaksi (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    toko_id         INT NOT NULL,
    kasir_id        INT NOT NULL,
    kode_transaksi  VARCHAR(30) NOT NULL UNIQUE,
    total_harga     DECIMAL(12,2) NOT NULL DEFAULT 0,
    diskon          DECIMAL(12,2) DEFAULT 0,
    pajak           DECIMAL(12,2) DEFAULT 0,
    total_bayar     DECIMAL(12,2) NOT NULL DEFAULT 0,
    uang_bayar      DECIMAL(12,2) DEFAULT 0,
    kembalian       DECIMAL(12,2) DEFAULT 0,
    metode_bayar    ENUM('tunai','qris','transfer') DEFAULT 'tunai',
    status          ENUM('pending','lunas','batal') DEFAULT 'lunas',
    catatan         TEXT,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (toko_id)  REFERENCES toko(id) ON DELETE CASCADE,
    FOREIGN KEY (kasir_id) REFERENCES users(id)
);

-- Detail transaksi
CREATE TABLE transaksi_detail (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    transaksi_id  INT NOT NULL,
    produk_id     INT NOT NULL,
    nama_produk   VARCHAR(150) NOT NULL,
    harga         DECIMAL(10,2) NOT NULL,
    qty           INT NOT NULL DEFAULT 1,
    subtotal      DECIMAL(12,2) NOT NULL,
    FOREIGN KEY (transaksi_id) REFERENCES transaksi(id) ON DELETE CASCADE,
    FOREIGN KEY (produk_id)    REFERENCES produk(id)
);

-- Log stok
CREATE TABLE stok_log (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    toko_id       INT NOT NULL,
    produk_id     INT NOT NULL,
    user_id       INT,
    tipe          ENUM('masuk','keluar','adjustment') NOT NULL,
    jumlah        INT NOT NULL,
    stok_sebelum  INT,
    stok_sesudah  INT,
    keterangan    VARCHAR(255),
    created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (toko_id)   REFERENCES toko(id) ON DELETE CASCADE,
    FOREIGN KEY (produk_id) REFERENCES produk(id),
    FOREIGN KEY (user_id)   REFERENCES users(id) ON DELETE SET NULL
);

-- ============================================================
-- SEED DATA
-- ============================================================

-- Superadmin  (password: superadmin123)
INSERT INTO users (toko_id, nama, username, email, password, role) VALUES
(NULL, 'Super Admin', 'superadmin', 'admin@kasir.app',
 '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'superadmin');

-- Contoh toko demo
INSERT INTO toko (nama, slug, alamat, telepon) VALUES
('Angkringan Barokah', 'angkringan-barokah', 'Jl. Malioboro No. 1, Yogyakarta', '08123456789');

-- Owner toko 1  (password: owner123)
INSERT INTO users (toko_id, nama, username, email, password, role) VALUES
(1, 'Pak Budi', 'owner1', 'budi@email.com',
 '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'owner');

-- Kasir toko 1  (password: kasir123)
INSERT INTO users (toko_id, nama, username, email, password, role) VALUES
(1, 'Siti Kasir', 'kasir1', '',
 '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'kasir');

-- Kategori & produk toko 1
INSERT INTO kategori (toko_id, nama, urutan) VALUES
(1,'Minuman',1),(1,'Makanan',2),(1,'Rokok',3),(1,'Snack',4);

INSERT INTO produk (toko_id, kategori_id, nama, barcode, harga, stok, satuan) VALUES
(1,1,'Es Teh Manis','8991234560001',3000,100,'gelas'),
(1,1,'Es Jeruk','8991234560002',4000,80,'gelas'),
(1,1,'Kopi Hitam','8991234560003',3000,50,'gelas'),
(1,1,'Kopi Susu','8991234560004',5000,50,'gelas'),
(1,2,'Nasi Kucing Ayam','8991234560006',4000,30,'bungkus'),
(1,2,'Nasi Kucing Tempe','8991234560007',3000,30,'bungkus'),
(1,2,'Gorengan','8991234560008',1000,50,'pcs'),
(1,3,'Rokok Surya 12','8991234560010',18000,20,'bungkus'),
(1,4,'Keripik Singkong','8991234560012',3000,25,'bungkus');