-- Database Setup untuk Labushop
-- Jalankan script ini di phpMyAdmin untuk membuat database dan tabel yang diperlukan

-- 1. Buat database
CREATE DATABASE IF NOT EXISTS labushop_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE labushop_db;

-- 3. Tabel Products (untuk produk)
CREATE TABLE IF NOT EXISTS products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL,
    discount_percentage DECIMAL(5,2) DEFAULT 0,
    rating DECIMAL(3,2) DEFAULT 0,
    stock INT DEFAULT 0,
    brand VARCHAR(100),
    category VARCHAR(100),
    thumbnail VARCHAR(500),
    images JSON,
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 4. Tabel Orders (untuk pesanan)
CREATE TABLE IF NOT EXISTS orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    total_price DECIMAL(10,2) NOT NULL,
    status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
    payment_status ENUM('pending', 'paid', 'failed') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);

-- 5. Tabel Payments (untuk pembayaran)
CREATE TABLE IF NOT EXISTS payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    payment_method ENUM('credit_card', 'debit_card', 'bank_transfer', 'e_wallet') NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    status ENUM('pending', 'success', 'failed', 'cancelled') DEFAULT 'pending',
    transaction_id VARCHAR(255),
    payment_proof VARCHAR(500),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
);

-- Sample products
INSERT IGNORE INTO products (title, description, price, discount_percentage, rating, stock, brand, category, thumbnail, images, status) VALUES 
('iPhone 15 Pro', 'Latest iPhone with advanced features', 15000000.00, 5.00, 4.8, 50, 'Apple', 'Smartphone', 'https://picsum.photos/300/300', '["https://picsum.photos/300/300", "https://picsum.photos/301/301", "https://picsum.photos/302/302"]', 'active'),
('Samsung Galaxy S24', 'Premium Android smartphone', 12000000.00, 10.00, 4.7, 30, 'Samsung', 'Smartphone', 'https://picsum.photos/300/301', '["https://picsum.photos/300/301", "https://picsum.photos/303/303", "https://picsum.photos/304/304"]', 'active'),
('MacBook Pro M3', 'Powerful laptop for professionals', 25000000.00, 0.00, 4.9, 20, 'Apple', 'Laptop', 'https://picsum.photos/300/302', '["https://picsum.photos/300/302", "https://picsum.photos/305/305", "https://picsum.photos/306/306"]', 'active'),
('AirPods Pro', 'Wireless earbuds with noise cancellation', 3500000.00, 15.00, 4.6, 100, 'Apple', 'Audio', 'https://picsum.photos/300/303', '["https://picsum.photos/300/303", "https://picsum.photos/307/307", "https://picsum.photos/308/308"]', 'active'),
('Sony WH-1000XM5', 'Premium noise-cancelling headphones', 4500000.00, 8.00, 4.8, 75, 'Sony', 'Audio', 'https://picsum.photos/300/304', '["https://picsum.photos/300/304", "https://picsum.photos/309/309", "https://picsum.photos/310/310"]', 'active'),
('iPad Air', 'Versatile tablet for work and play', 8000000.00, 12.00, 4.7, 40, 'Apple', 'Tablet', 'https://picsum.photos/300/305', '["https://picsum.photos/300/305", "https://picsum.photos/311/311", "https://picsum.photos/312/312"]', 'active');

-- Sample orders untuk testing
INSERT IGNORE INTO orders (user_id, product_id, quantity, unit_price, total_price, status, payment_status, created_at) VALUES 
(1, 1, 1, 15000000.00, 15000000.00, 'delivered', 'paid', '2024-01-15 10:30:00'),
(2, 2, 2, 12000000.00, 24000000.00, 'processing', 'paid', '2024-01-16 14:20:00'),
(3, 3, 1, 25000000.00, 25000000.00, 'shipped', 'paid', '2024-01-17 09:15:00'),
(1, 4, 3, 3500000.00, 10500000.00, 'pending', 'pending', '2024-01-18 16:45:00'),
(4, 5, 1, 4500000.00, 4500000.00, 'delivered', 'paid', '2024-01-19 11:30:00');

-- Sample payments untuk testing
INSERT IGNORE INTO payments (order_id, payment_method, amount, status, transaction_id, created_at) VALUES 
(1, 'credit_card', 15000000.00, 'success', 'TXN001234567890', '2024-01-15 10:35:00'),
(2, 'bank_transfer', 24000000.00, 'success', 'TXN001234567891', '2024-01-16 14:25:00'),
(3, 'e_wallet', 25000000.00, 'success', 'TXN001234567892', '2024-01-17 09:20:00'),
(4, 'credit_card', 10500000.00, 'pending', 'TXN001234567893', '2024-01-18 16:50:00'),
(5, 'debit_card', 4500000.00, 'success', 'TXN001234567894', '2024-01-19 11:35:00');
CREATE INDEX IF NOT EXISTS idx_products_category ON products(category);
CREATE INDEX IF NOT EXISTS idx_products_status ON products(status);
CREATE INDEX IF NOT EXISTS idx_orders_user_id ON orders(user_id);
CREATE INDEX IF NOT EXISTS idx_orders_status ON orders(status);
CREATE INDEX IF NOT EXISTS idx_payments_order_id ON payments(order_id);

-- 9. Buat stored procedure untuk update stock (jika belum ada)
DELIMITER //
DROP PROCEDURE IF EXISTS UpdateProductStock //
CREATE PROCEDURE UpdateProductStock(IN product_id INT, IN quantity_sold INT)
BEGIN
    UPDATE products 
    SET stock = stock - quantity_sold 
    WHERE id = product_id AND stock >= quantity_sold;
    
    IF ROW_COUNT() = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient stock';
    END IF;
END //
DELIMITER ;

-- 10. Buat trigger untuk update stock otomatis (jika belum ada)
DELIMITER //
DROP TRIGGER IF EXISTS update_stock_after_order //
CREATE TRIGGER update_stock_after_order
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    CALL UpdateProductStock(NEW.product_id, NEW.quantity);
END //
DELIMITER ;
