-- Database setup for Warung Klontong Rakha E-commerce

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

-- Create tables
CREATE TABLE IF NOT EXISTS admins (
    admin_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    address TEXT,
    phone VARCHAR(15),
    reset_token VARCHAR(255) DEFAULT NULL,
    reset_expiry DATETIME DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS categories (
    category_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT
);

CREATE TABLE IF NOT EXISTS products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    category_id INT,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    stock INT NOT NULL DEFAULT 0,
    image_path VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(category_id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    total_amount DECIMAL(10, 2) NOT NULL,
    status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
    payment_proof VARCHAR(255) NULL,
    shipping_method VARCHAR(50) DEFAULT 'regular',
    payment_method VARCHAR(50) DEFAULT 'transfer',
    payment_verified_at DATETIME DEFAULT NULL,
    payment_rejected_at DATETIME DEFAULT NULL,
    payment_notes TEXT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS order_items (
    order_item_id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE SET NULL
);

-- Insert default admin account
-- Default password is 'admin123'
-- Note: In production, generate a fresh hash with PHP's password_hash() function
INSERT INTO admins (username, password, name, email) 
VALUES ('admin', '$2y$10$eCc2HS3tCPBGBE29wlJ6NOkz9Nz2h.9WLB5J0rOsKnxQzKXM7ot9C', 'Admin Default', 'admin@warungrakha.com');

-- Insert sample categories
INSERT INTO categories (name, description) VALUES 
('Makanan Pokok', 'Bahan makanan pokok seperti beras, mie, dan tepung'),
('Minuman', 'Berbagai jenis minuman dalam kemasan'),
('Snack', 'Makanan ringan dan camilan'),
('Kebutuhan Rumah Tangga', 'Peralatan dan perlengkapan rumah tangga'),
('Toiletries', 'Peralatan mandi dan kebersihan'); 