-- =============================================
-- Toko Baju Junata Database Setup
-- Fashion E-Commerce Application Database
-- =============================================

-- Create database
CREATE DATABASE IF NOT EXISTS junata_db;
USE junata_db;

-- =============================================
-- TABLES
-- =============================================

-- Users table
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nama VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    no_telp VARCHAR(20),
    alamat TEXT,
    foto_profil VARCHAR(255) DEFAULT 'default-avatar.png',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Products table
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nama VARCHAR(200) NOT NULL,
    deskripsi TEXT,
    harga DECIMAL(12, 2) NOT NULL,
    stok INT NOT NULL DEFAULT 0,
    kategori VARCHAR(50) NOT NULL,
    gambar VARCHAR(255) DEFAULT 'default-product.png',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_kategori (kategori),
    INDEX idx_nama (nama)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Carts table
CREATE TABLE carts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    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,
    UNIQUE KEY unique_user_product (user_id, product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Orders table
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    total_amount DECIMAL(12, 2) NOT NULL,
    nama_penerima VARCHAR(100) NOT NULL,
    no_telp_penerima VARCHAR(20) NOT NULL,
    alamat_pengiriman TEXT NOT NULL,
    note TEXT,
    payment_method VARCHAR(50) NOT NULL,
    status VARCHAR(50) DEFAULT 'Dikirim',
    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,
    INDEX idx_user_id (user_id),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Order items table
CREATE TABLE order_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    price DECIMAL(12, 2) NOT NULL,
    subtotal DECIMAL(12, 2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT,
    INDEX idx_order_id (order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Store info table
CREATE TABLE store_info (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nama_toko VARCHAR(100) NOT NULL,
    deskripsi TEXT,
    alamat TEXT,
    email VARCHAR(100),
    no_telp VARCHAR(20),
    logo VARCHAR(255) DEFAULT 'store-logo.png',
    jam_buka TIME DEFAULT '08:00:00',
    jam_tutup TIME DEFAULT '21:00:00',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- VIEWS
-- =============================================

-- View for cart details with product information
DROP VIEW IF EXISTS v_cart_details;
CREATE VIEW v_cart_details AS
SELECT 
    c.id,
    c.user_id,
    c.product_id,
    c.quantity,
    p.nama AS product_nama,
    p.deskripsi AS product_deskripsi,
    p.harga AS product_harga,
    p.stok AS product_stok,
    p.kategori AS product_kategori,
    p.gambar AS product_gambar,
    (c.quantity * p.harga) AS subtotal,
    c.created_at
FROM carts c
INNER JOIN products p ON c.product_id = p.id;

-- View for order details with items
DROP VIEW IF EXISTS v_order_details;
CREATE VIEW v_order_details AS
SELECT 
    o.id AS order_id,
    o.user_id,
    o.total_amount,
    o.nama_penerima,
    o.no_telp_penerima,
    o.alamat_pengiriman,
    o.note,
    o.payment_method,
    o.status,
    o.created_at AS order_date,
    oi.id AS order_item_id,
    oi.product_id,
    oi.quantity,
    oi.price,
    oi.subtotal,
    p.nama AS product_nama,
    p.gambar AS product_gambar,
    p.kategori AS product_kategori
FROM orders o
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id;

-- View for product catalog (only available products)
DROP VIEW IF EXISTS v_product_catalog;
CREATE VIEW v_product_catalog AS
SELECT 
    id,
    nama,
    deskripsi,
    harga,
    stok,
    kategori,
    gambar,
    CASE 
        WHEN stok > 0 THEN 'Tersedia'
        ELSE 'Habis'
    END AS status_stok,
    created_at
FROM products
ORDER BY created_at DESC;

-- =============================================
-- STORED PROCEDURES
-- =============================================

-- Procedure to create order from cart
DROP PROCEDURE IF EXISTS sp_create_order;
DELIMITER //
CREATE PROCEDURE sp_create_order(
    IN p_user_id INT,
    IN p_nama_penerima VARCHAR(100),
    IN p_no_telp_penerima VARCHAR(20),
    IN p_alamat_pengiriman TEXT,
    IN p_note TEXT,
    IN p_payment_method VARCHAR(50),
    OUT p_order_id INT,
    OUT p_message VARCHAR(255)
)
BEGIN
    DECLARE v_total DECIMAL(12, 2) DEFAULT 0;
    DECLARE v_cart_count INT DEFAULT 0;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_message = 'Error: Transaction failed';
        SET p_order_id = -1;
    END;
    
    START TRANSACTION;
    
    -- Check if cart has items
    SELECT COUNT(*) INTO v_cart_count
    FROM carts
    WHERE user_id = p_user_id;
    
    IF v_cart_count = 0 THEN
        SET p_message = 'Error: Cart is empty';
        SET p_order_id = -1;
        ROLLBACK;
    ELSE
        -- Calculate total
        SELECT SUM(c.quantity * p.harga) INTO v_total
        FROM carts c
        INNER JOIN products p ON c.product_id = p.id
        WHERE c.user_id = p_user_id;
        
        -- Create order
        INSERT INTO orders (user_id, total_amount, nama_penerima, no_telp_penerima, 
                          alamat_pengiriman, note, payment_method, status)
        VALUES (p_user_id, v_total, p_nama_penerima, p_no_telp_penerima, 
                p_alamat_pengiriman, p_note, p_payment_method, 'Dikirim');
        
        SET p_order_id = LAST_INSERT_ID();
        
        -- Insert order items from cart
        INSERT INTO order_items (order_id, product_id, quantity, price, subtotal)
        SELECT 
            p_order_id,
            c.product_id,
            c.quantity,
            p.harga,
            (c.quantity * p.harga)
        FROM carts c
        INNER JOIN products p ON c.product_id = p.id
        WHERE c.user_id = p_user_id;
        
        -- Update product stock
        UPDATE products p
        INNER JOIN carts c ON p.id = c.product_id
        SET p.stok = p.stok - c.quantity
        WHERE c.user_id = p_user_id;
        
        -- Clear cart
        DELETE FROM carts WHERE user_id = p_user_id;
        
        SET p_message = 'Order created successfully';
        COMMIT;
    END IF;
END //
DELIMITER ;

-- Procedure to get user orders
DROP PROCEDURE IF EXISTS sp_get_user_orders;
DELIMITER //
CREATE PROCEDURE sp_get_user_orders(
    IN p_user_id INT
)
BEGIN
    SELECT 
        o.id,
        o.total_amount,
        o.nama_penerima,
        o.no_telp_penerima,
        o.alamat_pengiriman,
        o.note,
        o.payment_method,
        o.status,
        o.created_at,
        COUNT(oi.id) AS total_items
    FROM orders o
    LEFT JOIN order_items oi ON o.id = oi.order_id
    WHERE o.user_id = p_user_id
    GROUP BY o.id
    ORDER BY o.created_at DESC;
END //
DELIMITER ;

-- Procedure to get order detail
DROP PROCEDURE IF EXISTS sp_get_order_detail;
DELIMITER //
CREATE PROCEDURE sp_get_order_detail(
    IN p_order_id INT
)
BEGIN
    -- Get order header
    SELECT 
        o.id,
        o.user_id,
        o.total_amount,
        o.nama_penerima,
        o.no_telp_penerima,
        o.alamat_pengiriman,
        o.note,
        o.payment_method,
        o.status,
        o.created_at,
        u.nama AS user_nama,
        u.email AS user_email
    FROM orders o
    INNER JOIN users u ON o.user_id = u.id
    WHERE o.id = p_order_id;
    
    -- Get order items
    SELECT 
        oi.id,
        oi.product_id,
        oi.quantity,
        oi.price,
        oi.subtotal,
        p.nama AS product_nama,
        p.gambar AS product_gambar,
        p.kategori AS product_kategori
    FROM order_items oi
    INNER JOIN products p ON oi.product_id = p.id
    WHERE oi.order_id = p_order_id;
END //
DELIMITER ;

-- Procedure to update stock
DROP PROCEDURE IF EXISTS sp_update_stock;
DELIMITER //
CREATE PROCEDURE sp_update_stock(
    IN p_product_id INT,
    IN p_quantity INT,
    IN p_operation VARCHAR(10), -- 'add' or 'subtract'
    OUT p_message VARCHAR(255)
)
BEGIN
    DECLARE v_current_stock INT;
    
    SELECT stok INTO v_current_stock
    FROM products
    WHERE id = p_product_id;
    
    IF p_operation = 'add' THEN
        UPDATE products
        SET stok = stok + p_quantity
        WHERE id = p_product_id;
        SET p_message = 'Stock added successfully';
    ELSEIF p_operation = 'subtract' THEN
        IF v_current_stock >= p_quantity THEN
            UPDATE products
            SET stok = stok - p_quantity
            WHERE id = p_product_id;
            SET p_message = 'Stock subtracted successfully';
        ELSE
            SET p_message = 'Error: Insufficient stock';
        END IF;
    ELSE
        SET p_message = 'Error: Invalid operation';
    END IF;
END //
DELIMITER ;

-- =============================================
-- SAMPLE DATA
-- =============================================

-- Insert store information
INSERT INTO store_info (nama_toko, deskripsi, alamat, email, no_telp, jam_buka, jam_tutup) VALUES
('Toko Baju Junata', 'Toko fashion online terpercaya dengan koleksi pakaian trendy dan berkualitas. Melayani penjualan berbagai jenis pakaian pria, wanita, dan anak-anak dengan harga terjangkau.', 
 'Jl. Fashion Street No. 88, Jakarta Pusat', 'info@bajujunata.com', '021-88776655', '09:00:00', '21:00:00');

-- Insert sample user
INSERT INTO users (nama, email, password, no_telp, alamat) VALUES
('John Doe', 'john@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', '081234567890', 'Jl. Contoh No. 123, Jakarta'),
('Jane Smith', 'jane@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', '081234567891', 'Jl. Sample No. 456, Bandung');

-- Insert sample products (Fashion focused)
INSERT INTO products (nama, deskripsi, harga, stok, kategori, gambar) VALUES
-- Pakaian Pria
('Kemeja Lengan Panjang Formal', 'Kemeja formal slim fit, bahan katun premium, tersedia warna putih, biru, hitam. Size M-XXL', 149000, 45, 'Pakaian Pria', 'kemeja-formal.jpg'),
('Kaos Polo Premium', 'Polo shirt dengan bahan lacoste PE, nyaman dan tidak mudah kusut. Berbagai pilihan warna', 89000, 60, 'Pakaian Pria', 'polo-shirt.jpg'),
('Celana Chino Pria', 'Celana chino casual dengan bahan stretch, cocok untuk formal maupun casual. Size 28-38', 179000, 50, 'Pakaian Pria', 'celana-chino.jpg'),
('Jaket Bomber Pria', 'Jaket bomber dengan bahan parasut, design sporty dan trendy. Tersedia size M-XXL', 249000, 35, 'Pakaian Pria', 'jaket-bomber.jpg'),
('Sweater Rajut Pria', 'Sweater rajut hangat dengan design modern, cocok untuk cuaca dingin. Material wool blend', 199000, 40, 'Pakaian Pria', 'sweater-pria.jpg'),

-- Pakaian Wanita
('Blouse Wanita Elegant', 'Blouse wanita dengan design elegant, bahan sifon lembut. Cocok untuk formal dan casual', 129000, 55, 'Pakaian Wanita', 'blouse-wanita.jpg'),
('Dress Midi Casual', 'Dress midi dengan bahan katun rayon, nyaman dan adem. Pattern floral dan polos tersedia', 189000, 40, 'Pakaian Wanita', 'dress-midi.jpg'),
('Rok Plisket A-Line', 'Rok plisket dengan model A-line, bahan premium. Panjang selutut, size S-XL', 139000, 48, 'Pakaian Wanita', 'rok-plisket.jpg'),
('Cardigan Rajut Wanita', 'Cardigan rajut dengan design feminine, cocok untuk layering. Berbagai warna pastel', 169000, 45, 'Pakaian Wanita', 'cardigan-wanita.jpg'),
('Celana Kulot Wanita', 'Celana kulot bahan linen, nyaman dan trendi. Cocok untuk berbagai acara', 159000, 50, 'Pakaian Wanita', 'celana-kulot.jpg'),

-- Pakaian Anak
('Kaos Anak Karakter', 'Kaos anak dengan print karakter favorit, bahan cotton combed 24s. Size 2-10 tahun', 59000, 70, 'Pakaian Anak', 'kaos-anak.jpg'),
('Dress Anak Perempuan', 'Dress anak lucu dengan bahan katun, nyaman untuk aktivitas. Size 2-8 tahun', 89000, 55, 'Pakaian Anak', 'dress-anak.jpg'),
('Celana Jeans Anak', 'Celana jeans anak dengan bahan denim stretch, awet dan nyaman. Size 2-12 tahun', 119000, 45, 'Pakaian Anak', 'jeans-anak.jpg'),

-- Aksesoris
('Tas Selempang Wanita', 'Tas selempang kulit sintetis dengan design minimalis, banyak sekat. Ukuran medium', 179000, 38, 'Aksesoris', 'tas-selempang.jpg'),
('Dompet Pria Kulit', 'Dompet pria kulit asli dengan banyak slot kartu, design simple dan elegan', 129000, 42, 'Aksesoris', 'dompet-pria.jpg'),
('Topi Baseball Cap', 'Topi baseball dengan bahan twill, adjustable strap. Berbagai warna tersedia', 69000, 65, 'Aksesoris', 'topi-baseball.jpg'),
('Syal Pashmina Wanita', 'Syal pashmina dengan bahan lembut, cocok untuk hijab atau fashion. Banyak pilihan warna', 49000, 80, 'Aksesoris', 'syal-pashmina.jpg'),
('Ikat Pinggang Kulit', 'Ikat pinggang kulit asli dengan buckle stainless, cocok untuk formal. Panjang adjustable', 99000, 50, 'Aksesoris', 'ikat-pinggang.jpg'),

-- Sepatu
('Sepatu Sneakers Pria', 'Sepatu sneakers casual dengan sol empuk, design sporty. Size 39-44', 249000, 35, 'Sepatu', 'sneakers-pria.jpg'),
('Sepatu Formal Pantofel', 'Sepatu pantofel kulit untuk acara formal, design classic. Size 39-44', 349000, 28, 'Sepatu', 'pantofel.jpg'),
('Sandal Flat Wanita', 'Sandal flat wanita dengan bahan synthetic leather, nyaman untuk daily. Size 36-40', 89000, 55, 'Sepatu', 'sandal-flat.jpg'),
('Sepatu Heels Wanita', 'Sepatu heels 5cm dengan design elegant, cocok untuk pesta. Size 36-40', 279000, 32, 'Sepatu', 'heels-wanita.jpg');

-- =============================================
-- INDEXES FOR OPTIMIZATION
-- =============================================

-- Additional indexes for better query performance
CREATE INDEX idx_orders_created ON orders(created_at DESC);
CREATE INDEX idx_products_harga ON products(harga);
CREATE INDEX idx_carts_user_product ON carts(user_id, product_id);

-- =============================================
-- COMPLETION MESSAGE
-- =============================================

SELECT 'Database setup completed successfully!' AS message;
SELECT COUNT(*) AS total_products FROM products;
SELECT COUNT(*) AS total_users FROM users;
SELECT * FROM store_info;
