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
);

CREATE TABLE ratings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    product_id INT,
    rating INT CHECK (rating >= 1 AND rating <= 5),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(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 Amanjaya', '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');

-- Menu Kafe Sondana
INSERT INTO products (category_id, name, description, price, stock, weight) VALUES
-- MAKANAN (Category 1)
(1, 'Roti Bakar Sondana', 'Roti bakar isi coklat keju dengan taburan meses, disajikan hangat.', 22000, 40, 200),
(1, 'Pisang Goreng Keju', 'Pisang goreng crispy dengan lelehan keju dan susu kental manis.', 18000, 50, 250),
(1, 'Croissant Coklat', 'Croissant renyah berlapis dengan isian coklat premium.', 25000, 30, 100),

-- MINUMAN (Category 2)
(2, 'Kopi Hitam Sondana', 'Racikan kopi hitam single origin, pahit nikmat tanpa gula tambahan.', 15000, 100, 250),
(2, 'Es Kopi Susu Gula Aren', 'Espresso, susu segar, dan gula aren asli, sajian favorit pelanggan.', 20000, 100, 350),
(2, 'Cappuccino', 'Espresso dengan foam susu lembut dan taburan bubuk coklat.', 23000, 80, 250);