-- =====================================================================
-- SKRIP DATABASE MYSQL UNTUK HOSTING RUMAHWEB (cPanel / phpMyAdmin)
-- Sistem Kalkulator & Manajemen Harga Jual Marketplace
-- =====================================================================

-- 1. Tabel Pengguna (Users)
CREATE TABLE IF NOT EXISTS `users` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `username` VARCHAR(100) NOT NULL UNIQUE,
  `password` VARCHAR(255) NOT NULL,
  `full_name` VARCHAR(150) NOT NULL,
  `birth_date` DATE NOT NULL COMMENT 'Digunakan untuk verifikasi lupa password (YYYY-MM-DD)',
  `role` VARCHAR(50) DEFAULT 'Owner / Admin',
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert akun default (Username: admin, Password: password123, Tanggal Lahir: 1990-01-01)
INSERT INTO `users` (`username`, `password`, `full_name`, `birth_date`, `role`)
VALUES ('admin', 'password123', 'Administrator Toko', '1990-01-01', 'Owner / Admin')
ON DUPLICATE KEY UPDATE `username` = `username`;

-- 2. Tabel Pengaturan Combobox (Marketplace & Nama Toko)
CREATE TABLE IF NOT EXISTS `combobox_options` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `category` ENUM('marketplace', 'store_name', 'packing_template', 'fee_template') NOT NULL,
  `label` VARCHAR(150) NOT NULL,
  `default_value` DECIMAL(15, 2) DEFAULT 0.00,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Data awal Marketplace (Shopee, Tiktok, blibli, lazada) & Nama Toko (mfs, cr)
INSERT INTO `combobox_options` (`category`, `label`, `default_value`) VALUES
('marketplace', 'Shopee', 0),
('marketplace', 'Tiktok', 0),
('marketplace', 'blibli', 0),
('marketplace', 'lazada', 0),
('store_name', 'mfs', 0),
('store_name', 'cr', 0);

-- 3. Tabel Perhitungan Harga Jual (PRIMARY KEY otomatis mulai dari 1 s/d 100+)
CREATE TABLE IF NOT EXISTS `price_calculations` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `nama_barang` VARCHAR(255) NOT NULL,
  `sku` VARCHAR(100) NOT NULL,
  `marketplace` VARCHAR(100) NOT NULL,
  `nama_toko` VARCHAR(100) NOT NULL,
  `hpp` DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
  `biaya_packing_json` JSON NOT NULL COMMENT 'Daftar rincian item packing [{nama_barang, harga}]',
  `total_biaya_packing` DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
  `fee_marketplace_json` JSON NOT NULL COMMENT 'Daftar program fee marketplace [{nama_program, persentase}]',
  `total_fee_marketplace_persen` DECIMAL(8, 2) NOT NULL DEFAULT 0.00,
  `biaya_iklan_persen` DECIMAL(8, 2) NOT NULL DEFAULT 0.00,
  `cadangan_resiko_persen` DECIMAL(8, 2) NOT NULL DEFAULT 0.00,
  `diskon_afiliate_persen` DECIMAL(8, 2) NOT NULL DEFAULT 0.00,
  `target_keuntungan_persen` DECIMAL(8, 2) NOT NULL DEFAULT 0.00,
  `saran_harga_jual` DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
  `tentukan_harga_jual` DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
  `created_by` VARCHAR(100) DEFAULT 'admin',
  `saved_at` VARCHAR(30) NOT NULL COMMENT 'Format waktu simpan ringkas dd-mm-yy hh:mm',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;