-- POS Coffee Shop Database Schema
-- Database: pos_coffee

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

-- Categories table
CREATE TABLE IF NOT EXISTS categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Products table
CREATE TABLE IF NOT EXISTS products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(50) UNIQUE NOT NULL,
    name VARCHAR(100) NOT NULL,
    category_id INT,
    price DECIMAL(10,2) NOT NULL,
    stock INT DEFAULT 0,
    description TEXT,
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Transactions table
CREATE TABLE IF NOT EXISTS transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    invoice_number VARCHAR(50) UNIQUE NOT NULL,
    user_id INT NOT NULL,
    total DECIMAL(10,2) NOT NULL,
    payment_method ENUM('cash', 'card', 'qris') DEFAULT 'cash',
    cash_received DECIMAL(10,2) DEFAULT 0,
    change_amount DECIMAL(10,2) DEFAULT 0,
    status ENUM('pending', 'completed', 'cancelled') DEFAULT 'completed',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Transaction items table
CREATE TABLE IF NOT EXISTS transaction_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    transaction_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    subtotal DECIMAL(10,2) NOT NULL,
    status ENUM('pending', 'preparing', 'ready', 'completed') DEFAULT 'pending',
    completed_at DATETIME NULL,
    FOREIGN KEY (transaction_id) REFERENCES transactions(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Insert sample categories
INSERT INTO categories (name, description) VALUES 
('Minuman Panas', 'Kopi, teh, coklat panas'),
('Minuman Dingin', 'Es kopi, es teh, jus, mocktail'),
('Makanan', 'Nasi goreng, mie, snack, roti'),
('Kue & Dessert', 'Cake, cookies, pudding, ice cream');

-- Insert sample products
INSERT INTO products (code, name, category_id, price, stock, description) VALUES 
('KOPI01', 'Espresso', 1, 15000, 100, 'Espresso shot'),
('KOPI02', 'Cappuccino', 1, 20000, 100, 'Espresso dengan susu foam'),
('KOPI03', 'Latte', 1, 22000, 100, 'Espresso dengan susu steamed'),
('KOPI04', 'Americano', 1, 18000, 100, 'Espresso dengan air panas'),
('KOPI05', 'Mocha', 1, 25000, 100, 'Espresso dengan coklat dan susu'),
('ES01', 'Es Kopi Susu', 2, 18000, 100, 'Es kopi dengan susu'),
('ES02', 'Es Teh Manis', 2, 8000, 100, 'Teh manis dingin'),
('ES03', 'Jus Alpukat', 2, 15000, 100, 'Jus alpukat segar'),
('MAK01', 'Nasi Goreng Spesial', 3, 25000, 50, 'Nasi goreng dengan telur dan ayam'),
('MAK02', 'Mie Ayam', 3, 20000, 50, 'Mie ayam dengan bakso'),
('MAK03', 'Keripik Kentang', 3, 12000, 100, 'Keripik kentang gurih'),
('DES01', 'Cheesecake', 4, 25000, 30, 'Cheesecake lembut'),
('DES02', 'Brownies', 4, 15000, 50, 'Brownies coklat'),
('DES03', 'Ice Cream Vanilla', 4, 12000, 50, 'Ice cream vanilla');

