-- ==============================================================================
-- DATABASE SCHEMA: TOKO MAKMUR - POINT OF SALES & INVENTORY MANAGEMENT
-- Kompatibel dengan MySQL 5.7 / 8.0+, MariaDB, cPanel, & phpMyAdmin
-- ==============================================================================
-- Petunjuk Impor di phpMyAdmin:
-- 1. Buka phpMyAdmin di hosting / cPanel Anda.
-- 2. Buat Database baru (misal: `u1234567_tokomakmur`).
-- 3. Pilih database tersebut, lalu klik tab "SQL" atau "Import".
-- 4. Copy-Paste seluruh isi file ini lalu klik "Go" / "Kirim".
-- ==============================================================================

SET FOREIGN_KEY_CHECKS = 0;
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET TIME_ZONE = "+07:00";

-- ------------------------------------------------------------------------------
-- 1. TABEL PENGATURAN TOKO (`store_settings`)
-- Menyimpan profil toko, nomor telp, alamat, & format cetak struk kasir
-- ------------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `store_settings` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `store_name` VARCHAR(255) NOT NULL DEFAULT 'Toko Makmur',
  `store_address` TEXT,
  `store_phone` VARCHAR(50) DEFAULT '0812-3456-7890',
  `cashier_name` VARCHAR(100) DEFAULT 'Kasir Utama',
  `receipt_footer` TEXT,
  `invoice_prefix` VARCHAR(20) DEFAULT 'INV',
  `version` VARCHAR(20) DEFAULT '1.0.0',
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Tabel Pengaturan Profil & Struk Toko';

-- ------------------------------------------------------------------------------
-- 2. TABEL MASTER KATEGORI PRODUK (`categories`)
-- Contoh: Sembako, Minuman, Makanan Ringan, Bumbu Dapur, dll.
-- ------------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `categories` (
  `id` VARCHAR(50) PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `description` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Master Kategori Produk';

-- ------------------------------------------------------------------------------
-- 3. TABEL MASTER SATUAN / UNIT (`units`)
-- Contoh: Pcs, Kg, Gram, Liter, Dus, Pack, Bungkus, Botol
-- ------------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `units` (
  `id` VARCHAR(50) PRIMARY KEY,
  `name` VARCHAR(50) NOT NULL,
  `description` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Master Satuan Barang';

-- ------------------------------------------------------------------------------
-- 4. TABEL MASTER UKURAN / SIZE (`sizes`)
-- Contoh: Kecil (100ml), Sedang (500ml), Besar (1kg)
-- ------------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `sizes` (
  `id` VARCHAR(50) PRIMARY KEY,
  `name` VARCHAR(50) NOT NULL,
  `description` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Master Ukuran Barang';

-- ------------------------------------------------------------------------------
-- 5. TABEL SUPPLIER / PEMASOK (`suppliers`)
-- Data distributor tempat pembelian barang masuk / restok
-- ------------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `suppliers` (
  `id` VARCHAR(50) PRIMARY KEY,
  `name` VARCHAR(150) NOT NULL,
  `phone` VARCHAR(50),
  `address` TEXT,
  `company_name` VARCHAR(150),
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Master Supplier / Distributor';

-- ------------------------------------------------------------------------------
-- 6. TABEL MASTER PRODUK (`products`)
-- Induk data barang (seperti Minyak Bimoli, Le Minerale, Beras Sania)
-- ------------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `products` (
  `id` VARCHAR(50) PRIMARY KEY,
  `name` VARCHAR(255) NOT NULL,
  `category` VARCHAR(100) NOT NULL,
  `description` TEXT,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_category` (`category`),
  INDEX `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Header Master Produk';

-- ------------------------------------------------------------------------------
-- 7. TABEL VARIAN PRODUK (`product_variants`)
-- Rincian stok, barcode scanner, harga beli, harga eceran, & harga grosir
-- ------------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `product_variants` (
  `id` VARCHAR(50) PRIMARY KEY,
  `product_id` VARCHAR(50) NOT NULL,
  `variant_name` VARCHAR(150) NOT NULL,
  `size` VARCHAR(50) DEFAULT '',
  `unit` VARCHAR(50) NOT NULL DEFAULT 'Pcs',
  `barcode` VARCHAR(100) DEFAULT NULL,
  `buy_price` DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
  `sell_price` DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
  `wholesale_price` DECIMAL(15, 2) DEFAULT NULL,
  `stock` INT NOT NULL DEFAULT 0,
  `min_stock` INT NOT NULL DEFAULT 5,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  INDEX `idx_barcode` (`barcode`),
  INDEX `idx_product` (`product_id`),
  INDEX `idx_stock` (`stock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Detail Varian Produk & Stok Barcode';

-- ------------------------------------------------------------------------------
-- 8. TABEL HARGA BERTINGKAT / PROMO KELIPATAN (`tier_prices`)
-- Skema promo (contoh: Beli 2 = Rp 5.000, Beli 5 = Rp 11.000)
-- ------------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `tier_prices` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `variant_id` VARCHAR(50) NOT NULL,
  `min_qty` INT NOT NULL DEFAULT 1,
  `price` DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
  `price_type` ENUM('package', 'unit') NOT NULL DEFAULT 'package',
  `label` VARCHAR(150) DEFAULT NULL,
  FOREIGN KEY (`variant_id`) REFERENCES `product_variants`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  INDEX `idx_variant_tier` (`variant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Skema Harga Bertingkat / Promo Grosir';

-- ------------------------------------------------------------------------------
-- 9. TABEL TRANSAKSI PENJUALAN KASIR (`transactions`)
-- Header invoice transaksi kasir POS
-- ------------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `transactions` (
  `id` VARCHAR(50) PRIMARY KEY,
  `transaction_code` VARCHAR(50) NOT NULL,
  `date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `customer_name` VARCHAR(150) DEFAULT 'Pelanggan Umum',
  `customer_phone` VARCHAR(50) DEFAULT NULL,
  `payment_method` ENUM('Tunai', 'QRIS', 'Transfer Bank') NOT NULL DEFAULT 'Tunai',
  `total_amount` DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
  `total_profit` DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
  `discount` DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
  `final_amount` DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
  `notes` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `uk_trx_code` (`transaction_code`),
  INDEX `idx_trx_date` (`date`),
  INDEX `idx_payment_method` (`payment_method`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Header Transaksi Penjualan Kasir POS';

-- ------------------------------------------------------------------------------
-- 10. TABEL DETAIL ITEM TRANSAKSI (`transaction_items`)
-- Item barang yang dibeli pada setiap nota transaksi
-- ------------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `transaction_items` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `transaction_id` VARCHAR(50) NOT NULL,
  `product_id` VARCHAR(50) NOT NULL,
  `product_name` VARCHAR(255) NOT NULL,
  `variant_id` VARCHAR(50) NOT NULL,
  `variant_name` VARCHAR(150) NOT NULL,
  `unit` VARCHAR(50) NOT NULL DEFAULT 'Pcs',
  `barcode` VARCHAR(100) DEFAULT NULL,
  `quantity` INT NOT NULL DEFAULT 1,
  `buy_price` DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
  `sell_price` DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
  `original_sell_price` DECIMAL(15, 2) DEFAULT NULL,
  `wholesale_price` DECIMAL(15, 2) DEFAULT NULL,
  `price_type` VARCHAR(50) DEFAULT 'Eceran',
  `price_note` VARCHAR(255) DEFAULT NULL,
  `applied_tier_note` VARCHAR(255) DEFAULT NULL,
  `saved_amount` DECIMAL(15, 2) DEFAULT 0.00,
  `subtotal` DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
  FOREIGN KEY (`transaction_id`) REFERENCES `transactions`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  INDEX `idx_trx_item_variant` (`variant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Detail Rincian Item Nota Transaksi Kasir';

-- ------------------------------------------------------------------------------
-- 11. TABEL BARANG MASUK / RESTOK (`inbounds`)
-- Catatan riwayat pembelian barang dari distributor/supplier atau Beli Sendiri
-- ------------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `inbounds` (
  `id` VARCHAR(50) PRIMARY KEY,
  `date` DATE NOT NULL,
  `source_type` VARCHAR(50) NOT NULL DEFAULT 'Supplier',
  `supplier_name` VARCHAR(150) NOT NULL DEFAULT '-',
  `invoice_number` VARCHAR(100) DEFAULT NULL,
  `product_id` VARCHAR(50) NOT NULL,
  `product_name` VARCHAR(255) NOT NULL,
  `variant_id` VARCHAR(50) NOT NULL,
  `variant_name` VARCHAR(150) NOT NULL,
  `quantity` INT NOT NULL DEFAULT 1,
  `unit` VARCHAR(50) NOT NULL DEFAULT 'Pcs',
  `buy_price` DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
  `total_cost` DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
  `notes` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_inbound_date` (`date`),
  INDEX `idx_inbound_supplier` (`supplier_name`),
  INDEX `idx_inbound_source` (`source_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Riwayat Barang Masuk / Restok Toko';

-- ------------------------------------------------------------------------------
-- 12. TABEL BARANG KELUAR / RUSAK / EXPIRED (`outbounds`)
-- Catatan kerugian stok rusak, kedaluwarsa, atau penyesuaian opname
-- ------------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `outbounds` (
  `id` VARCHAR(50) PRIMARY KEY,
  `date` DATE NOT NULL,
  `product_id` VARCHAR(50) NOT NULL,
  `product_name` VARCHAR(255) NOT NULL,
  `variant_id` VARCHAR(50) NOT NULL,
  `variant_name` VARCHAR(150) NOT NULL,
  `quantity` INT NOT NULL DEFAULT 1,
  `unit` VARCHAR(50) NOT NULL DEFAULT 'Pcs',
  `buy_price` DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
  `total_loss_cost` DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
  `reason` VARCHAR(100) NOT NULL DEFAULT 'Kedaluwarsa / Expired',
  `notes` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_outbound_date` (`date`),
  INDEX `idx_outbound_reason` (`reason`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Riwayat Barang Keluar / Expired / Rusak';

-- ------------------------------------------------------------------------------
-- 13. TABEL RETUR PENJUALAN & TUKAR BARANG (`returns`)
-- Catatan pengembalian/tukar barang dari pelanggan setelah transaksi
-- ------------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `returns` (
  `id` VARCHAR(50) PRIMARY KEY,
  `return_code` VARCHAR(50) NOT NULL,
  `date` DATETIME NOT NULL,
  `transaction_id` VARCHAR(50) NOT NULL,
  `transaction_code` VARCHAR(50) NOT NULL,
  `customer_name` VARCHAR(150) DEFAULT 'Pelanggan Umum',
  `return_type` VARCHAR(50) NOT NULL COMMENT 'Refund Uang / Tukar Barang',
  `returned_items` JSON NOT NULL COMMENT 'Detail barang yang dikembalikan & alasannya',
  `total_returned_value` DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
  `replacement_items` JSON DEFAULT NULL COMMENT 'Detail barang pengganti jika Tukar Barang',
  `replacement_total` DECIMAL(15, 2) DEFAULT 0.00,
  `price_difference` DECIMAL(15, 2) DEFAULT 0.00,
  `notes` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_return_date` (`date`),
  INDEX `idx_return_trx` (`transaction_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Riwayat Retur & Tukar Barang Pelanggan';

SET FOREIGN_KEY_CHECKS = 1;

-- ==============================================================================
-- SEED / DATA AWAL CONTOH
-- ==============================================================================

INSERT INTO `store_settings` (`id`, `store_name`, `store_address`, `store_phone`, `cashier_name`, `receipt_footer`, `invoice_prefix`)
VALUES (1, 'Toko Makmur', 'Jl. Veteran No. 45, Kota Makmur', '0812-3456-7890', 'Kasir Utama', 'Terima kasih telah berbelanja di Toko Makmur!', 'INV')
ON DUPLICATE KEY UPDATE `store_name`=VALUES(`store_name`);

INSERT INTO `categories` (`id`, `name`, `description`) VALUES
('cat_1', 'Sembako', 'Kebutuhan pokok sehari-hari'),
('cat_2', 'Minuman', 'Minuman ringan, susu, dan air mineral'),
('cat_3', 'Makanan Ringan', 'Camilan dan biskuit'),
('cat_4', 'Bumbu Dapur', 'Penyedap, minyak, dan bumbu masakan'),
('cat_5', 'Peralatan Rumah', 'Sabun, pembersih, dan kebutuhan rumah tangga')
ON DUPLICATE KEY UPDATE `name`=VALUES(`name`);

INSERT INTO `units` (`id`, `name`, `description`) VALUES
('u_1', 'Pcs', 'Buah / Biji'),
('u_2', 'Kg', 'Kilogram'),
('u_3', 'Gram', 'Gram'),
('u_4', 'Liter', 'Liter'),
('u_5', 'Dus', 'Kardus'),
('u_6', 'Pack', 'Paket / Pack'),
('u_7', 'Bungkus', 'Bungkus / Sachet'),
('u_8', 'Botol', 'Botol')
ON DUPLICATE KEY UPDATE `name`=VALUES(`name`);

INSERT INTO `sizes` (`id`, `name`, `description`) VALUES
('s_1', 'Kecil', 'Ukuran kemasan kecil'),
('s_2', 'Sedang', 'Ukuran kemasan standar'),
('s_3', 'Besar', 'Ukuran kemasan jumbo / besar')
ON DUPLICATE KEY UPDATE `name`=VALUES(`name`);