-- SQL Dump File untuk Toko Kue Edelys
-- Basis Data: edelys_db (XAMPP / phpMyAdmin)

CREATE DATABASE IF NOT EXISTS `edelys_db` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `edelys_db`;

-- --------------------------------------------------------

-- Struktur dari tabel `users`
CREATE TABLE IF NOT EXISTS `users` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `username` VARCHAR(100) UNIQUE NOT NULL,
    `password` VARCHAR(255) NOT NULL,
    `role` VARCHAR(50) NOT NULL,
    `name` VARCHAR(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Data default user admin (Password: admin123)
INSERT INTO `users` (`username`, `password`, `role`, `name`) VALUES
('admin', '$2y$10$e.Y.n/fHk/.0z0X7wA0uO.8Wb1K05rG0V5t0W0X0Y0Z0A0B0C0D0E', 'admin', 'Owner Edelys')
ON DUPLICATE KEY UPDATE `id`=`id`;

-- --------------------------------------------------------

-- Struktur dari tabel `categories`
CREATE TABLE IF NOT EXISTS `categories` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) UNIQUE NOT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO `categories` (`name`) VALUES
('Cake'),
('Cheesecake'),
('Crepe'),
('Kue Basah'),
('Kue Kering');

-- --------------------------------------------------------

-- Struktur dari tabel `products`
CREATE TABLE IF NOT EXISTS `products` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(255) NOT NULL,
    `description` TEXT NOT NULL,
    `price` DECIMAL(12,2) NOT NULL,
    `stock` INT NOT NULL DEFAULT 0,
    `image_url` VARCHAR(255) DEFAULT NULL,
    `category` VARCHAR(100) NOT NULL,
    `is_active` TINYINT(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

-- Struktur dari tabel `orders`
CREATE TABLE IF NOT EXISTS `orders` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `customer_name` VARCHAR(150) NOT NULL,
    `customer_phone` VARCHAR(50) NOT NULL,
    `customer_address` TEXT NOT NULL,
    `total_price` DECIMAL(12,2) NOT NULL,
    `status` VARCHAR(50) NOT NULL DEFAULT 'pending',
    `payment_method` VARCHAR(100) DEFAULT 'Transfer Bank (BCA)',
    `payment_proof` VARCHAR(255) DEFAULT NULL,
    `payment_status` VARCHAR(50) DEFAULT 'unverified',
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

-- Struktur dari tabel `order_items`
CREATE TABLE IF NOT EXISTS `order_items` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `order_id` INT NOT NULL,
    `product_id` INT NOT NULL,
    `quantity` INT NOT NULL,
    `price_at_purchase` DECIMAL(12,2) NOT NULL,
    FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

-- Struktur dari tabel `stock_logs`
CREATE TABLE IF NOT EXISTS `stock_logs` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `product_id` INT NOT NULL,
    `quantity_change` INT NOT NULL,
    `type` VARCHAR(20) NOT NULL,
    `notes` TEXT DEFAULT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
