-- --------------------------------------------------------
-- Database: loundri
-- Import via phpMyAdmin: Create DB `loundri` (utf8mb4) lalu import file ini.
-- --------------------------------------------------------

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

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

DROP TABLE IF EXISTS payments;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS reservations;
DROP TABLE IF EXISTS outlets;
DROP TABLE IF EXISTS pages;
DROP TABLE IF EXISTS services;
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS users;

CREATE TABLE users (
	id INT UNSIGNED NOT NULL AUTO_INCREMENT,
	username VARCHAR(50) NOT NULL,
	name VARCHAR(100) NOT NULL,
	password_hash VARCHAR(255) NOT NULL,
	is_active TINYINT(1) NOT NULL DEFAULT 1,
	created_at DATETIME NOT NULL,
	PRIMARY KEY (id),
	UNIQUE KEY uq_users_username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE customers (
	id INT UNSIGNED NOT NULL AUTO_INCREMENT,
	name VARCHAR(100) NOT NULL,
	phone VARCHAR(30) NULL,
	address VARCHAR(255) NULL,
	created_at DATETIME NOT NULL,
	PRIMARY KEY (id),
	KEY idx_customers_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE services (
	id INT UNSIGNED NOT NULL AUTO_INCREMENT,
	name VARCHAR(100) NOT NULL,
	unit VARCHAR(20) NOT NULL DEFAULT 'kg',
	price DECIMAL(12,2) NOT NULL DEFAULT 0.00,
	created_at DATETIME NOT NULL,
	PRIMARY KEY (id),
	KEY idx_services_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE outlets (
	id INT UNSIGNED NOT NULL AUTO_INCREMENT,
	name VARCHAR(100) NOT NULL,
	address VARCHAR(255) NULL,
	hours VARCHAR(60) NULL,
	phone VARCHAR(30) NULL,
	whatsapp VARCHAR(30) NULL,
	notes VARCHAR(255) NULL,
	map_url VARCHAR(255) NULL,
	is_active TINYINT(1) NOT NULL DEFAULT 1,
	created_at DATETIME NOT NULL,
	PRIMARY KEY (id),
	KEY idx_outlets_active (is_active),
	KEY idx_outlets_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE pages (
	id INT UNSIGNED NOT NULL AUTO_INCREMENT,
	slug VARCHAR(50) NOT NULL,
	title VARCHAR(150) NOT NULL,
	content_html MEDIUMTEXT NOT NULL,
	created_at DATETIME NOT NULL,
	updated_at DATETIME NOT NULL,
	PRIMARY KEY (id),
	UNIQUE KEY uq_pages_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE orders (
	id INT UNSIGNED NOT NULL AUTO_INCREMENT,
	code VARCHAR(30) NOT NULL,
	customer_id INT UNSIGNED NOT NULL,
	service_id INT UNSIGNED NOT NULL,
	qty DECIMAL(10,2) NOT NULL DEFAULT 0.00,
	pickup_date DATE NULL,
	due_date DATE NULL,
	status ENUM('baru','proses','selesai','diambil','batal') NOT NULL DEFAULT 'baru',
	notes VARCHAR(255) NULL,
	total DECIMAL(12,2) NOT NULL DEFAULT 0.00,
	paid_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
	created_at DATETIME NOT NULL,
	updated_at DATETIME NOT NULL,
	PRIMARY KEY (id),
	UNIQUE KEY uq_orders_code (code),
	KEY idx_orders_customer (customer_id),
	KEY idx_orders_service (service_id),
	KEY idx_orders_status (status),
	CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers(id)
		ON UPDATE CASCADE ON DELETE RESTRICT,
	CONSTRAINT fk_orders_service FOREIGN KEY (service_id) REFERENCES services(id)
		ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE payments (
	id INT UNSIGNED NOT NULL AUTO_INCREMENT,
	order_id INT UNSIGNED NOT NULL,
	amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
	method VARCHAR(30) NOT NULL DEFAULT 'cash',
	note VARCHAR(255) NULL,
	paid_at DATETIME NOT NULL,
	created_at DATETIME NOT NULL,
	PRIMARY KEY (id),
	KEY idx_payments_order (order_id),
	CONSTRAINT fk_payments_order FOREIGN KEY (order_id) REFERENCES orders(id)
		ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE reservations (
	id INT UNSIGNED NOT NULL AUTO_INCREMENT,
	code VARCHAR(30) NOT NULL,
	name VARCHAR(100) NOT NULL,
	address VARCHAR(255) NULL,
	whatsapp VARCHAR(30) NULL,
	service_id INT UNSIGNED NULL,
	pickup_date DATE NULL,
	pickup_time TIME NULL,
	notes VARCHAR(255) NULL,
	status ENUM('baru','dikonfirmasi','dipickup','selesai','batal') NOT NULL DEFAULT 'baru',
	created_at DATETIME NOT NULL,
	PRIMARY KEY (id),
	UNIQUE KEY uq_reservations_code (code),
	KEY idx_reservations_status (status),
	KEY idx_reservations_service (service_id),
	CONSTRAINT fk_reservations_service FOREIGN KEY (service_id) REFERENCES services(id)
		ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO users (username, name, password_hash, is_active, created_at) VALUES
('admin', 'Administrator', '$2y$10$7XikuQIwC1c3PxEy3xrUtuaTYxmNMUQI72M649tX4yu.zlIMcHhue', 1, NOW());

INSERT INTO services (name, unit, price, created_at) VALUES
('Cuci Kering', 'kg', 7000.00, NOW()),
('Cuci Setrika', 'kg', 9000.00, NOW()),
('Setrika Saja', 'kg', 6000.00, NOW()),
('Laundry Sepatu', 'pasang', 25000.00, NOW()),
('Laundry Tas', 'pcs', 30000.00, NOW()),
('Laundry Karpet', 'm2', 15000.00, NOW()),
('Laundry Gorden', 'm2', 12000.00, NOW()),
('Laundry Stroller', 'unit', 50000.00, NOW()),
('Laundry Boneka', 'pcs', 20000.00, NOW());

INSERT INTO outlets (name, address, hours, phone, whatsapp, notes, map_url, is_active, created_at) VALUES
('Outlet Utama', 'Isi alamat outlet utama Anda di sini', '08.00 - 21.00', '+62 812-3456-7890', '6281234567890', 'Pickup & drop-off tersedia', 'https://www.google.com/maps?q=Jakarta&output=embed', 1, NOW()),
('Outlet Cabang', 'Isi alamat outlet cabang Anda di sini', '09.00 - 20.00', '+62 812-0000-0000', '6281200000000', 'Layanan satuan & express', 'https://www.google.com/maps?q=Bandung&output=embed', 1, NOW());

INSERT INTO pages (slug, title, content_html, created_at, updated_at) VALUES
(
	'terms',
	'Syarat & Ketentuan',
	CONCAT(
		'<h2>Ketentuan Umum</h2>',
		'<ul>',
		'<li>Dengan melakukan reservasi/order, Anda dianggap menyetujui syarat & ketentuan ini.</li>',
		'<li>Estimasi selesai mengikuti layanan yang dipilih dan antrian pengerjaan.</li>',
		'<li>Kebijakan dapat berubah sewaktu-waktu dan versi terbaru berlaku saat reservasi/order dibuat.</li>',
		'</ul>',
		'<h2>Reservasi & Penjemputan</h2>',
		'<ul>',
		'<li>Pastikan nomor WhatsApp aktif untuk konfirmasi penjemputan.</li>',
		'<li>Waktu penjemputan bersifat estimasi dan bergantung pada ketersediaan kurir serta kondisi lalu lintas.</li>',
		'</ul>',
		'<h2>Barang Berharga</h2>',
		'<ul>',
		'<li>Harap tidak menyertakan barang berharga (uang, perhiasan, elektronik) di dalam cucian.</li>',
		'<li>Komplain maksimal 1x24 jam setelah pesanan diterima.</li>',
		'</ul>',
		'<h2>Privasi</h2>',
		'<p>Data pelanggan digunakan untuk proses layanan & konfirmasi, dan tidak diperjualbelikan.</p>'
	),
	NOW(),
	NOW()
);

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
