-- =============================================
-- KASIR PRO DATABASE SCHEMA
-- =============================================
-- Run this SQL in phpMyAdmin or MySQL CLI

-- Create database
CREATE DATABASE IF NOT EXISTS kasir_pro CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE kasir_pro;

-- =============================================
-- USERS TABLE (Pegawai/Admin)
-- =============================================
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    phone VARCHAR(20) UNIQUE,
    email VARCHAR(100),
    pin VARCHAR(255) NOT NULL, -- Hashed PIN
    role ENUM('owner', 'admin', 'cashier') DEFAULT 'cashier',
    avatar VARCHAR(255),
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Default admin user (Phone: 081234567890, PIN: 123456)
-- PIN hash below is a placeholder. PLEASE RUN reset_pin.php TO GENERATE A VALID HASH.
INSERT INTO users (name, phone, pin, role) VALUES 
('Admin', '081234567890', '$2y$10$UnValidHashFor123456ButWillBeResetByScriptXXXXXXXXXXXXX', 'owner');

-- =============================================
-- OUTLETS TABLE
-- =============================================
CREATE TABLE outlets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    address TEXT,
    phone VARCHAR(20),
    logo VARCHAR(255),
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Default outlet
INSERT INTO outlets (name, address, phone) VALUES 
('Outlet Utama', 'Jl. Contoh No. 123', '08123456789');

-- =============================================
-- CATEGORIES TABLE
-- =============================================
CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    sort_order INT DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Default categories
INSERT INTO categories (name, sort_order) VALUES 
('Aksesoris', 1),
('Charger', 2),
('Headset', 3),
('Kabel Data', 4),
('Casing', 5),
('Tempered Glass', 6);

-- =============================================
-- PRODUCTS TABLE
-- =============================================
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category_id INT,
    name VARCHAR(200) NOT NULL,
    sku VARCHAR(50),
    barcode VARCHAR(50),
    price DECIMAL(15,2) NOT NULL DEFAULT 0,
    cost_price DECIMAL(15,2) DEFAULT 0,
    stock INT DEFAULT 0,
    min_stock INT DEFAULT 0,
    unit VARCHAR(20) DEFAULT 'pcs',
    image VARCHAR(255),
    is_favorite TINYINT(1) DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
);

-- Sample products
INSERT INTO products (category_id, name, sku, price, stock, is_favorite) VALUES 
(1, 'KABEL AUX 1+2', 'KA12', 10000, 100, 1),
(2, 'POWER CHARGER RT-K8', 'PCK8', 210000, 0, 1),
(2, 'Cas Rexi', 'CR01', 27000, 50, 0),
(2, 'Batok Robot', 'BR01', 120000, 0, 0),
(2, 'BATOK RT-K8', 'BTK8', 205000, 25, 0),
(2, 'CHARGER ROBOT RT-K4', 'CRK4', 25000, 31, 0),
(1, 'EARPHONE RE10', 'ER10', 17000, 15, 0),
(3, 'Headset Rexi', 'HR01', 35000, 0, 0),
(3, 'Headset Ss', 'HSS', 28000, 9, 0),
(3, 'Headset JBL 09', 'HJB09', 45000, 1, 0);

-- =============================================
-- CUSTOMERS TABLE
-- =============================================
CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    email VARCHAR(100),
    address TEXT,
    notes TEXT,
    total_transactions INT DEFAULT 0,
    total_spent DECIMAL(15,2) DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- =============================================
-- DISCOUNTS TABLE
-- =============================================
CREATE TABLE discounts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    type ENUM('percentage', 'fixed') DEFAULT 'percentage',
    value DECIMAL(15,2) NOT NULL,
    min_purchase DECIMAL(15,2) DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- =============================================
-- TRANSACTIONS TABLE
-- =============================================
CREATE TABLE transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    transaction_code VARCHAR(20) UNIQUE NOT NULL,
    outlet_id INT,
    user_id INT,
    customer_id INT,
    subtotal DECIMAL(15,2) NOT NULL DEFAULT 0,
    discount_id INT,
    discount_amount DECIMAL(15,2) DEFAULT 0,
    tax_amount DECIMAL(15,2) DEFAULT 0,
    total DECIMAL(15,2) NOT NULL DEFAULT 0,
    payment_method ENUM('cash', 'transfer', 'qris', 'debit', 'credit') DEFAULT 'cash',
    amount_paid DECIMAL(15,2) DEFAULT 0,
    change_amount DECIMAL(15,2) DEFAULT 0,
    notes TEXT,
    status ENUM('completed', 'pending', 'cancelled', 'refunded') DEFAULT 'completed',
    transaction_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (outlet_id) REFERENCES outlets(id) ON DELETE SET NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL,
    FOREIGN KEY (discount_id) REFERENCES discounts(id) ON DELETE SET NULL
);

-- =============================================
-- TRANSACTION ITEMS TABLE
-- =============================================
CREATE TABLE transaction_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    transaction_id INT NOT NULL,
    product_id INT,
    product_name VARCHAR(200) NOT NULL,
    product_price DECIMAL(15,2) NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    subtotal DECIMAL(15,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (transaction_id) REFERENCES transactions(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE SET NULL
);

-- =============================================
-- STOCK HISTORY TABLE
-- =============================================
CREATE TABLE stock_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT NOT NULL,
    user_id INT,
    type ENUM('in', 'out', 'adjustment') NOT NULL,
    quantity INT NOT NULL,
    stock_before INT NOT NULL,
    stock_after INT NOT NULL,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

-- =============================================
-- SETTINGS TABLE
-- =============================================
CREATE TABLE settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) UNIQUE NOT NULL,
    setting_value TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Default settings
INSERT INTO settings (setting_key, setting_value) VALUES 
('store_name', 'Kasir Pro'),
('store_address', 'Jl. Contoh No. 123'),
('store_phone', '08123456789'),
('tax_percentage', '0'),
('receipt_footer', 'Terima kasih atas kunjungan Anda!'),
('category_view', 'Compact'),
('catalog_view', 'List');

-- =============================================
-- INDEXES FOR PERFORMANCE
-- =============================================
CREATE INDEX idx_products_category ON products(category_id);
CREATE INDEX idx_products_sku ON products(sku);
CREATE INDEX idx_products_barcode ON products(barcode);
CREATE INDEX idx_transactions_code ON transactions(transaction_code);
CREATE INDEX idx_transactions_date ON transactions(transaction_date);
CREATE INDEX idx_transaction_items_transaction ON transaction_items(transaction_id);