-- Create Database
CREATE DATABASE IF NOT EXISTS warung_ajib;
USE warung_ajib;

-- Table Products
CREATE TABLE IF NOT EXISTS products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    stock INT DEFAULT 0,
    image VARCHAR(255),
    category VARCHAR(100),
    total_sold INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Table Orders
CREATE TABLE IF NOT EXISTS orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    order_number VARCHAR(50) UNIQUE NOT NULL,
    total_price DECIMAL(10, 2) NOT NULL,
    shipping_cost DECIMAL(10, 2) DEFAULT 0,
    grand_total DECIMAL(10, 2) NOT NULL,
    shipping_address TEXT NOT NULL,
    shipping_method VARCHAR(100),
    payment_proof VARCHAR(255),
    status ENUM('pending', 'paid', 'processing', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
    notes TEXT,
    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
);

-- Table Order Details
CREATE TABLE IF NOT EXISTS order_details (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    product_name VARCHAR(255) NOT NULL,
    product_price DECIMAL(10, 2) NOT NULL,
    quantity INT NOT NULL,
    subtotal DECIMAL(10, 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 CASCADE
);

-- Table Payments
CREATE TABLE IF NOT EXISTS payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    payment_proof VARCHAR(255),
    payment_method VARCHAR(100),
    payment_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    verified_by INT,
    verified_at TIMESTAMP NULL,
    status ENUM('pending', 'verified', 'rejected') DEFAULT 'pending',
    notes TEXT,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (verified_by) REFERENCES users(id) ON DELETE SET NULL
);

-- Table Settings (for contact info)
CREATE TABLE IF NOT EXISTS settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) UNIQUE NOT NULL,
    setting_value TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
-- Password: password

-- Insert sample products
INSERT INTO products (name, description, price, stock, category, image) VALUES
('Es Teh Ajib', 'Es teh segar dengan rasa yang nikmat, cocok untuk menemani hari Anda', 5000, 100, 'Minuman', 'es_teh.jpg'),
('Lumpia Semarang', 'Lumpia Semarang isi rebung dan ayam yang gurih', 15000, 50, 'Makanan', 'lumpia.jpg'),
('Jaipuran Spesial', 'Jajanan tradisional khas Demak dengan rasa manis', 10000, 75, 'Makanan', 'jaipuran.jpg'),
('Kopi Susu Ajib', 'Kopi susu dengan campuran gula aren yang manis', 12000, 80, 'Minuman', 'kopi_susu.jpg'),
('Bakso Mercon', 'Bakso dengan sambal pedas yang menggigit', 18000, 40, 'Makanan', 'bakso.jpg'),
('Jus Alpukat', 'Jus alpukat segar tanpa tambahan gula', 15000, 60, 'Minuman', 'jus_alpukat.jpg');

-- Insert default settings
INSERT INTO settings (setting_key, setting_value) VALUES
('call_center', '081234567890'),
('sms_center', '081234567890'),
('whatsapp', '081234567890'),
('email', 'contact@warungajib.com'),
('address', 'Bandungrejo, Mranggen, Demak, Jawa Tengah'),
('maps_latitude', '-6.9774'),
('maps_longitude', '110.4204'),
('shipping_cost_per_km', '2000'),
('app_name', 'Warung Ajib'),
('app_tagline', 'Belanja Mudah, Hasil Nikmat');
