-- ============================================
-- Anzadiyah Store - Database Schema
-- Toko Online Oleh-Oleh Khas Desa Kalanganyar
-- ============================================

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+07:00";

-- Buat database
CREATE DATABASE IF NOT EXISTS `anzadiyah_store` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `anzadiyah_store`;

-- ============================================
-- 1. TABEL USERS
-- ============================================
CREATE TABLE `users` (
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(100) NOT NULL,
    `email` VARCHAR(100) NOT NULL,
    `phone` VARCHAR(20) DEFAULT NULL,
    `address` TEXT DEFAULT NULL,
    `password` VARCHAR(255) NOT NULL,
    `role` ENUM('customer','admin') NOT NULL DEFAULT 'customer',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 2. TABEL CATEGORIES
-- ============================================
CREATE TABLE `categories` (
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(100) NOT NULL,
    `slug` VARCHAR(100) NOT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 3. TABEL PRODUCTS
-- ============================================
CREATE TABLE `products` (
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `category_id` INT(11) UNSIGNED DEFAULT NULL,
    `name` VARCHAR(200) NOT NULL,
    `slug` VARCHAR(200) NOT NULL,
    `description` TEXT DEFAULT NULL,
    `price` DECIMAL(12,0) NOT NULL DEFAULT 0,
    `stock` INT(11) NOT NULL DEFAULT 0,
    `image` VARCHAR(255) DEFAULT NULL,
    `weight` INT(11) NOT NULL DEFAULT 0 COMMENT 'gram',
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `slug` (`slug`),
    KEY `category_id` (`category_id`),
    CONSTRAINT `fk_product_category` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 4. TABEL CARTS
-- ============================================
CREATE TABLE `carts` (
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` INT(11) UNSIGNED NOT NULL,
    `product_id` INT(11) UNSIGNED NOT NULL,
    `quantity` INT(11) NOT NULL DEFAULT 1,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `user_id` (`user_id`),
    KEY `product_id` (`product_id`),
    CONSTRAINT `fk_cart_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_cart_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 5. TABEL ORDERS
-- ============================================
CREATE TABLE `orders` (
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` INT(11) UNSIGNED NOT NULL,
    `order_number` VARCHAR(30) NOT NULL,
    `subtotal` DECIMAL(12,0) NOT NULL DEFAULT 0,
    `shipping_cost` DECIMAL(12,0) NOT NULL DEFAULT 0,
    `grand_total` DECIMAL(12,0) NOT NULL DEFAULT 0,
    `shipping_name` VARCHAR(100) NOT NULL,
    `shipping_phone` VARCHAR(20) NOT NULL,
    `shipping_address` TEXT NOT NULL,
    `notes` TEXT DEFAULT NULL,
    `payment_method` VARCHAR(30) NOT NULL DEFAULT 'QRIS',
    `payment_id` VARCHAR(100) DEFAULT NULL COMMENT 'Sakurupiah trx_id',
    `payment_status` ENUM('pending','paid','expired','failed') NOT NULL DEFAULT 'pending',
    `order_status` ENUM('pending','paid','processing','completed','cancelled') NOT NULL DEFAULT 'pending',
    `qris_image` VARCHAR(500) DEFAULT NULL,
    `payment_url` VARCHAR(500) DEFAULT NULL,
    `expires_at` DATETIME DEFAULT NULL,
    `paid_at` DATETIME DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `order_number` (`order_number`),
    KEY `user_id` (`user_id`),
    KEY `payment_status` (`payment_status`),
    KEY `order_status` (`order_status`),
    CONSTRAINT `fk_order_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 6. TABEL ORDER_ITEMS
-- ============================================
CREATE TABLE `order_items` (
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `order_id` INT(11) UNSIGNED NOT NULL,
    `product_id` INT(11) UNSIGNED DEFAULT NULL,
    `product_name` VARCHAR(200) NOT NULL,
    `product_image` VARCHAR(255) DEFAULT NULL,
    `price` DECIMAL(12,0) NOT NULL DEFAULT 0,
    `quantity` INT(11) NOT NULL DEFAULT 1,
    `subtotal` DECIMAL(12,0) NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`),
    KEY `order_id` (`order_id`),
    CONSTRAINT `fk_item_order` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 7. TABEL SETTINGS
-- ============================================
CREATE TABLE `settings` (
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `setting_key` VARCHAR(100) NOT NULL,
    `setting_value` TEXT DEFAULT NULL,
    `setting_label` VARCHAR(200) DEFAULT NULL,
    `setting_group` VARCHAR(50) DEFAULT 'general',
    PRIMARY KEY (`id`),
    UNIQUE KEY `setting_key` (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 8. TABEL WEBHOOK_LOGS
-- ============================================
CREATE TABLE `webhook_logs` (
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `event_type` VARCHAR(50) DEFAULT NULL,
    `payload` TEXT DEFAULT NULL,
    `signature` VARCHAR(255) DEFAULT NULL,
    `is_valid` TINYINT(1) NOT NULL DEFAULT 0,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 9. TABEL REVIEWS
-- ============================================
CREATE TABLE `reviews` (
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` INT(11) UNSIGNED NOT NULL,
    `order_id` INT(11) UNSIGNED DEFAULT NULL,
    `rating` TINYINT(1) NOT NULL DEFAULT 5,
    `comment` TEXT DEFAULT NULL,
    `is_approved` TINYINT(1) NOT NULL DEFAULT 0,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `user_id` (`user_id`),
    KEY `is_approved` (`is_approved`),
    CONSTRAINT `fk_review_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 10. TABEL BANNERS
-- ============================================
CREATE TABLE `banners` (
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `title` VARCHAR(200) DEFAULT NULL,
    `description` TEXT DEFAULT NULL,
    `image` VARCHAR(255) NOT NULL,
    `link_url` VARCHAR(500) DEFAULT NULL,
    `sort_order` INT(11) NOT NULL DEFAULT 0,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- SEED DATA
-- ============================================

-- Admin default (password: admin123)
INSERT INTO `users` (`name`, `email`, `phone`, `address`, `password`, `role`) VALUES
('Administrator', 'admin@anzadiyah.com', '081234567890', 'Jl. KH Romli Timur RT21 RW05, Desa Kalanganyar, Kecamatan Sedati, Sidoarjo', '$2y$10$EyVsQe1FcdSygX.oFWb2Ze0Dh/qOlTXPZJNh59elB19s3FjYcGvmu', 'admin');

-- Kategori
INSERT INTO `categories` (`name`, `slug`) VALUES
('Makanan', 'makanan'),
('Minuman', 'minuman'),
('Cemilan', 'cemilan'),
('Bumbu & Sambal', 'bumbu-sambal'),
('Oleh-Oleh Khas', 'oleh-oleh-khas');

-- Produk Contoh
INSERT INTO `products` (`category_id`, `name`, `slug`, `description`, `price`, `stock`, `image`, `weight`, `is_active`) VALUES
(1, 'Bandeng Presto Khas Sidoarjo', 'bandeng-presto-khas-sidoarjo', 'Bandeng presto premium khas Sidoarjo dengan tulang yang sangat lunak dan bumbu rempah rahasia turun-temurun. Diproses secara higienis dengan teknologi presto modern sehingga aman dikonsumsi langsung. Cocok sebagai oleh-oleh atau lauk makan sehari-hari.', 35000, 50, 'bandeng-presto.jpg', 500, 1),
(1, 'Otak-Otak Bandeng', 'otak-otak-bandeng', 'Otak-otak bandeng isi bumbu rempah nusantara yang gurih. Terbuat dari daging bandeng segar pilihan yang dihaluskan dan dibalut kembali dalam kulit bandeng utuh. Tekstur lembut dengan rasa yang kaya akan rempah.', 45000, 30, 'otak-otak-bandeng.jpg', 400, 1),
(3, 'Kerupuk Udang Sidoarjo', 'kerupuk-udang-sidoarjo', 'Kerupuk udang asli Sidoarjo yang renyah dan gurih. Dibuat dari udang segar hasil tambak lokal Kalanganyar dengan resep tradisional yang telah ada sejak puluhan tahun. Tersedia dalam kemasan siap goreng atau sudah digoreng.', 25000, 100, 'kerupuk-udang.jpg', 250, 1),
(4, 'Terasi Udang Premium', 'terasi-udang-premium', 'Terasi udang premium dari tambak udang Kalanganyar, difermentasi sempurna dengan aroma harum yang khas. Terasi ini dibuat secara tradisional tanpa bahan pengawet, menghasilkan rasa yang autentik untuk masakan sambal dan berbagai hidangan Nusantara.', 15000, 200, 'terasi-udang.jpg', 200, 1),
(4, 'Petis Udang Kalanganyar', 'petis-udang-kalanganyar', 'Petis udang khas Kalanganyar yang legit dan kaya rasa umami. Terbuat dari sari udang berkualitas tinggi yang dimasak perlahan hingga menghasilkan tekstur kental yang sempurna. Wajib ada di dapur setiap pecinta kuliner Jawa Timur.', 20000, 150, 'petis-udang.jpg', 300, 1),
(2, 'Sirup Mangrove Kalanganyar', 'sirup-mangrove-kalanganyar', 'Sirup unik berbahan dasar buah mangrove (bogem/pidada) yang tumbuh di kawasan konservasi mangrove Kalanganyar, Sidoarjo. Bercita rasa manis segar dengan sentuhan asam alami. Minuman sehat yang kaya antioksidan dan vitamin C.', 30000, 75, 'sirup-mangrove.jpg', 600, 1);

-- Settings
INSERT INTO `settings` (`setting_key`, `setting_value`, `setting_label`, `setting_group`) VALUES
('store_name', 'Anzadiyah Store', 'Nama Toko', 'general'),
('store_tagline', 'Oleh-Oleh Khas Desa Kalanganyar, Sidoarjo', 'Tagline Toko', 'general'),
('store_description', 'Menyediakan berbagai macam oleh-oleh khas Desa Kalanganyar, Sidoarjo. Mulai dari bandeng presto, kerupuk udang, terasi, petis, hingga sirup mangrove.', 'Deskripsi Toko', 'general'),
('store_address', 'Jl. KH Romli Timur RT21 RW05, Desa Kalanganyar, Kecamatan Sedati, Sidoarjo', 'Alamat Toko', 'general'),
('store_phone', '', 'No. Telepon / WhatsApp', 'general'),
('store_email', 'info@anzadiyah.com', 'Email Toko', 'general'),
('shipping_cost', '15000', 'Ongkir Statis (Rp)', 'general'),
('sakurupiah_api_id', '', 'Sakurupiah API ID', 'payment'),
('sakurupiah_api_key', '', 'Sakurupiah API Key', 'payment');

COMMIT;