CREATE DATABASE IF NOT EXISTS al_qanuni_store
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE al_qanuni_store;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(120) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    username VARCHAR(80) NOT NULL UNIQUE,
    phone VARCHAR(30) NULL,
    address TEXT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('customer','admin','owner') NOT NULL DEFAULT 'customer',
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_users_role (role)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(120) NOT NULL,
    description VARCHAR(255) NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_categories_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category_id INT NULL,
    name VARCHAR(150) NOT NULL,
    slug VARCHAR(180) NOT NULL,
    description TEXT,
    price DECIMAL(12,2) NOT NULL,
    stock INT NOT NULL DEFAULT 0,
    weight_gram INT NULL,
    photo VARCHAR(255) NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_products_slug (slug),
    INDEX idx_products_category (category_id),
    INDEX idx_products_active (is_active),
    CONSTRAINT fk_products_category
        FOREIGN KEY (category_id) REFERENCES categories(id)
        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE carts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_carts_user (user_id),
    CONSTRAINT fk_carts_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE cart_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    cart_id INT NOT NULL,
    product_id INT NOT NULL,
    qty INT NOT NULL DEFAULT 1,
    price DECIMAL(12,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_cart_items (cart_id, product_id),
    INDEX idx_cart_items_product (product_id),
    CONSTRAINT fk_cart_items_cart
        FOREIGN KEY (cart_id) REFERENCES carts(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_cart_items_product
        FOREIGN KEY (product_id) REFERENCES products(id)
        ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE payment_methods (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(120) NOT NULL,
    account_name VARCHAR(120) NOT NULL,
    account_number VARCHAR(80) NOT NULL,
    instructions TEXT,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_payment_methods_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(30) NOT NULL,
    user_id INT NOT NULL,
    shipping_address TEXT NULL,
    shipping_cost DECIMAL(12,2) NOT NULL DEFAULT 0,
    subtotal DECIMAL(12,2) NOT NULL DEFAULT 0,
    total DECIMAL(12,2) NOT NULL DEFAULT 0,
    status ENUM('pending','paid','packed','shipped','done','canceled','menunggu_verifikasi','ditolak') NOT NULL DEFAULT 'pending',
    payment_method_id INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_orders_code (code),
    INDEX idx_orders_user (user_id),
    INDEX idx_orders_status (status),
    INDEX idx_orders_status_created (status, created_at),
    CONSTRAINT fk_orders_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_orders_payment
        FOREIGN KEY (payment_method_id) REFERENCES payment_methods(id)
        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE order_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    qty INT NOT NULL DEFAULT 1,
    price DECIMAL(12,2) NOT NULL,
    subtotal DECIMAL(12,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_order_items_order (order_id),
    INDEX idx_order_items_product (product_id),
    CONSTRAINT fk_order_items_order
        FOREIGN KEY (order_id) REFERENCES orders(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_order_items_product
        FOREIGN KEY (product_id) REFERENCES products(id)
        ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE payment_confirmations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    payment_method_id INT NULL,
    paid_at DATETIME NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    proof_file VARCHAR(255) NULL,
    note VARCHAR(255) NULL,
    status ENUM('pending','verified','rejected') NOT NULL DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_payment_confirm_order (order_id),
    INDEX idx_payment_confirm_status (status),
    CONSTRAINT fk_payment_confirm_order
        FOREIGN KEY (order_id) REFERENCES orders(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_payment_confirm_method
        FOREIGN KEY (payment_method_id) REFERENCES payment_methods(id)
        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE offline_sales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(30) NOT NULL,
    sold_at DATETIME NOT NULL,
    cashier_id INT NOT NULL,
    customer_name VARCHAR(120) NULL,
    subtotal DECIMAL(12,2) NOT NULL DEFAULT 0,
    total DECIMAL(12,2) NOT NULL DEFAULT 0,
    note VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_offline_sales_code (code),
    INDEX idx_offline_sales_cashier (cashier_id),
    INDEX idx_offline_sales_sold_at (sold_at),
    CONSTRAINT fk_offline_sales_cashier
        FOREIGN KEY (cashier_id) REFERENCES users(id)
        ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE offline_sale_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    offline_sale_id INT NOT NULL,
    product_id INT NOT NULL,
    qty INT NOT NULL DEFAULT 1,
    price DECIMAL(12,2) NOT NULL,
    subtotal DECIMAL(12,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_offline_items_sale (offline_sale_id),
    INDEX idx_offline_items_product (product_id),
    CONSTRAINT fk_offline_items_sale
        FOREIGN KEY (offline_sale_id) REFERENCES offline_sales(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_offline_items_product
        FOREIGN KEY (product_id) REFERENCES products(id)
        ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO categories (name, description, is_active) VALUES
('Kurma', 'Beragam kurma premium untuk oleh-oleh', 1),
('Minuman', 'Minuman khas haji dan umroh', 1),
('Camilan', 'Camilan khas Timur Tengah', 1);

INSERT INTO products (category_id, name, slug, description, price, stock, weight_gram, photo, is_active) VALUES
(1, 'Kurma Ajwa 500g', 'kurma-ajwa-500g', 'Kurma Ajwa premium khas Madinah.', 85000, 40, 500, 'ajwa.jpg', 1),
(1, 'Kurma Sukkari 500g', 'kurma-sukkari-500g', 'Kurma Sukkari lembut dan manis.', 65000, 55, 500, 'sukkari.jpg', 1),
(1, 'Kurma Medjool 500g', 'kurma-medjool-500g', 'Kurma Medjool ukuran jumbo.', 95000, 30, 500, 'medjool.jpg', 1),
(1, 'Kurma Khalas 500g', 'kurma-khalas-500g', 'Kurma Khalas pilihan rasa legit.', 70000, 50, 500, 'khalas.jpg', 1),
(2, 'Air Zam-zam 5L', 'air-zamzam-5l', 'Air zam-zam kemasan 5 liter.', 90000, 20, 5000, 'zamzam-5l.jpg', 1),
(3, 'Kismis Arab 250g', 'kismis-arab-250g', 'Kismis pilihan untuk cemilan.', 35000, 60, 250, 'kismis.jpg', 1),
(3, 'Madu Arab 250ml', 'madu-arab-250ml', 'Madu Arab murni botol 250ml.', 55000, 35, 250, 'madu.jpg', 1),
(3, 'Cokelat Arab 200g', 'cokelat-arab-200g', 'Cokelat khas Timur Tengah.', 45000, 45, 200, 'cokelat.jpg', 1),
(3, 'Permen Arab 250g', 'permen-arab-250g', 'Permen manis khas Arab.', 30000, 70, 250, 'permen.jpg', 1),
(3, 'Biskuit Timur Tengah 300g', 'biskuit-timur-tengah-300g', 'Biskuit renyah dengan rempah.', 40000, 38, 300, 'biskuit.jpg', 1);

INSERT INTO users (name, email, username, password_hash, role, is_active) VALUES
('Admin Al-Qanuni', 'admin@alqanuni.local', 'admin', '$2y$10$wH8sQvYc5z4yRphj9n.8NOBGJr3hYb9uWQ2y4S1Q8x0n0e8vQ1lNa', 'admin', 1),
('Owner Al-Qanuni', 'owner@alqanuni.local', 'owner', '$2y$10$wH8sQvYc5z4yRphj9n.8NOBGJr3hYb9uWQ2y4S1Q8x0n0e8vQ1lNa', 'owner', 1);

INSERT INTO payment_methods (name, account_name, account_number, instructions, is_active) VALUES
('Transfer Bank Syariah', 'Al-Qanuni Store', '1234567890', 'Silakan transfer sesuai total dan unggah bukti pembayaran.', 1);
