CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    phone VARCHAR(20),
    address TEXT,
    role ENUM('user', 'admin') DEFAULT 'user',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    image VARCHAR(255)
);

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category_id INT,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    stock INT NOT NULL,
    image VARCHAR(255),
    weight INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
);

CREATE TABLE cart (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    product_id INT,
    quantity INT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);

CREATE TABLE transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    total_price DECIMAL(10, 2) NOT NULL,
    status ENUM('pending', 'waiting_confirmation', 'processed', 'shipped', 'completed', 'cancelled') DEFAULT 'pending',
    payment_proof VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE transaction_details (
    id INT AUTO_INCREMENT PRIMARY KEY,
    transaction_id INT,
    product_id INT,
    quantity INT NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (transaction_id) REFERENCES transactions(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);

-- Seed Initial Data
INSERT INTO users (name, email, password, role) VALUES 
('Admin Kafe Sondana', 'admin@gmail.com', '$2y$10$857pa3XpZP31UFXhh0qYc.nYDLlJA5QZzcVx0bfBWdZCojJb0BFme', 'admin'), -- password: password
('Budi Santoso', 'user@gmail.com', '$2y$10$857pa3XpZP31UFXhh0qYc.nYDLlJA5QZzcVx0bfBWdZCojJb0BFme', 'user');

INSERT INTO categories (id, name) VALUES 
(1, 'Makanan'), 
(2, 'Minuman');

-- 6 Real Foods (Category 1: Makanan) and 6 Real Drinks (Category 2: Minuman)
INSERT INTO products (category_id, name, description, price, stock, weight) VALUES 
(1, 'Croissant Butter', 'Roti mentega khas Prancis yang renyah di luar, lembut dan kaya rasa mentega di dalam.', 18000, 30, 150),
(1, 'Nasi Goreng Kampung', 'Nasi goreng tradisional dengan telur mata sapi, ayam suwir, dan kerupuk renyah.', 25000, 25, 450),
(1, 'Spaghetti Bolognese', 'Pasta al dente dengan saus daging cincang sapi segar dan taburan keju parmesan.', 28000, 20, 350),
(1, 'Kentang Goreng', 'Kentang potong premium goreng renyah bumbu garam gurih, disajikan dengan saus sambal.', 15000, 40, 200),
(1, 'Roti Bakar Cokelat Keju', 'Roti bakar tebal dengan isian cokelat melimpah dan taburan keju parut gurih.', 18000, 35, 250),
(1, 'Club Sandwich', 'Roti panggang berlapis isi dada ayam fillet, telur dadar, selada segar, tomat, dan mayones.', 24000, 15, 300),

(2, 'Espresso', 'Espresso murni dari biji kopi Arabika pilihan dengan rasa yang kuat dan aroma pekat.', 15000, 50, 100),
(2, 'Americano', 'Kopi hitam klasik dengan espresso shot dan air panas, cita rasa bersih dan berani.', 18000, 60, 150),
(2, 'Caffe Latte', 'Perpaduan espresso premium dengan susu segar lembut dan lapisan busa halus.', 22000, 45, 200),
(2, 'Matcha Latte', 'Bubuk matcha Jepang premium berpadu dengan susu segar manis nan lembut.', 24000, 40, 200),
(2, 'Es Teh Manis', 'Teh melati seduh tradisional disajikan dingin dengan gula tebu asli.', 8000, 100, 250),
(2, 'Chocolate Signature', 'Cokelat hitam premium pekat yang diseduh dengan susu segar hangat.', 23000, 35, 200);