-- Dadofy Database Schema
-- Compatible with MySQL 5.7+ and MySQL 8.0+

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

-- --------------------------------------------------------
-- Table structure for table `admins`
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `admins` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `email` VARCHAR(100) NOT NULL UNIQUE,
  `password` VARCHAR(255) NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `portfolios`
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `portfolios` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(150) NOT NULL,
  `slug` VARCHAR(150) NOT NULL UNIQUE,
  `category` VARCHAR(50) NOT NULL,
  `client_name` VARCHAR(100) NOT NULL,
  `description` TEXT NOT NULL,
  `image` VARCHAR(255) NOT NULL,
  `project_url` VARCHAR(255) DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `contact_messages`
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `contact_messages` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `email` VARCHAR(100) NOT NULL,
  `phone` VARCHAR(30) NOT NULL,
  `service` VARCHAR(100) NOT NULL,
  `message` TEXT NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `settings`
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `settings` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `setting_key` VARCHAR(100) NOT NULL UNIQUE,
  `setting_value` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Seeding Default Admin & Page Settings
-- --------------------------------------------------------

-- Default Admin: name = Admin Dadofy, email = admin@dadofy.id, password = admin123
-- (Bcrypt password hash for 'admin123')
INSERT INTO `admins` (`name`, `email`, `password`) VALUES
('Admin Dadofy', 'admin@dadofy.id', '$2a$10$oY75n6u/P5s9b8G2hT/0eeH7v6i8HvyB2jR9r4P56cWwbeU7U1fG.')
ON DUPLICATE KEY UPDATE `email`=`email`;

-- Default Settings
INSERT INTO `settings` (`setting_key`, `setting_value`) VALUES
('brand_name', 'Dadofy'),
('email', 'halo@dadofy.id'),
('whatsapp', '081234567890'),
('address', 'Gedung Cyber Lt 5, Jl. Kuningan Barat No. 8, Jakarta Selatan, Indonesia'),
('instagram', 'https://instagram.com/dadofy.id'),
('facebook', 'https://facebook.com/dadofy.id'),
('tiktok', 'https://tiktok.com/@dadofy.id'),
('meta_description', 'Dadofy membantu kamu membuat website modern, cepat, dan responsif — lengkap dengan hosting dan VPS agar project digital kamu bisa langsung online tanpa ribet.')
ON DUPLICATE KEY UPDATE `setting_key`=`setting_key`;

-- Default Portfolios
INSERT INTO `portfolios` (`title`, `slug`, `category`, `client_name`, `description`, `image`, `project_url`) VALUES
('Landing Page & CBT Madrasah Aliyah', 'landing-page-cbt-madrasah-aliyah', 'Landing Page', 'Madrasah Aliyah Hidayat', 'Aplikasi landing page informasi madrasah yang terintegrasi dengan CBT (Computer Based Test) online untuk penerimaan siswa baru dan ujian harian. Server menggunakan VPS berkecepatan tinggi agar tahan diakses ribuan siswa secara simultan.', '/uploads/portfolio-ma.jpg', 'https://madrasah-demo.dadofy.id'),
('E-Commerce Website UMKM Hijab', 'e-commerce-umkm-hijab', 'Toko Online', 'Zahra Hijab Style', 'Platform toko online kustom untuk UMKM hijab. Memiliki fitur shopping cart, checkout langsung ke WhatsApp CS, katalog produk terlengkap, dan integrasi ongkos kirim otomatis.', '/uploads/portfolio-hijab.jpg', 'https://zahrahijab-demo.dadofy.id'),
('Company Profile Jasa Logistik', 'company-profile-jasa-logistik', 'Company Profile', 'PT Cahaya Logistik Nusantara', 'Website company profile modern dan clean dengan fitur pelacakan nomor resi, kalkulator estimasi tarif pengiriman barang, dan halaman layanan komprehensif.', '/uploads/portfolio-logistic.jpg', 'https://cahayalogistik-demo.dadofy.id')
ON DUPLICATE KEY UPDATE `slug`=`slug`;

-- --------------------------------------------------------
-- Tables for Client Portal Feature Set
-- --------------------------------------------------------

-- 1. Table structure for table `users`
CREATE TABLE IF NOT EXISTS `users` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `email` VARCHAR(100) NOT NULL UNIQUE,
  `password` VARCHAR(255) DEFAULT NULL,
  `google_id` VARCHAR(100) DEFAULT NULL UNIQUE,
  `avatar` VARCHAR(255) DEFAULT NULL,
  `phone` VARCHAR(20) DEFAULT NULL,
  `status` ENUM('active', 'pending', 'suspended') DEFAULT 'pending',
  `verification_token` VARCHAR(100) DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. Table structure for table `orders`
CREATE TABLE IF NOT EXISTS `orders` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `service_type` VARCHAR(100) NOT NULL,
  `package_name` VARCHAR(100) NOT NULL,
  `price` DECIMAL(12, 2) NOT NULL,
  `brief_details` TEXT DEFAULT NULL,
  `status` ENUM('pending_payment', 'in_progress', 'review', 'completed', 'cancelled') DEFAULT 'pending_payment',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. Table structure for table `payments`
CREATE TABLE IF NOT EXISTS `payments` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `order_id` INT NOT NULL,
  `bank_name` VARCHAR(50) NOT NULL,
  `sender_name` VARCHAR(100) NOT NULL,
  `receipt_image` VARCHAR(255) NOT NULL,
  `status` ENUM('under_verification', 'approved', 'rejected') DEFAULT 'under_verification',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. Table structure for table `tickets`
CREATE TABLE IF NOT EXISTS `tickets` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `subject` VARCHAR(150) NOT NULL,
  `category` ENUM('revision', 'technical', 'billing', 'other') DEFAULT 'technical',
  `status` ENUM('open', 'answered', 'closed') DEFAULT 'open',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. Table structure for table `ticket_messages`
CREATE TABLE IF NOT EXISTS `ticket_messages` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `ticket_id` INT NOT NULL,
  `sender_type` ENUM('user', 'admin') NOT NULL,
  `sender_name` VARCHAR(100) NOT NULL,
  `message` TEXT NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`ticket_id`) REFERENCES `tickets`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `visitor_logs`
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `visitor_logs` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `ip_address` VARCHAR(45) NOT NULL,
  `user_agent` VARCHAR(255) NOT NULL,
  `page_url` VARCHAR(255) NOT NULL,
  `referrer` VARCHAR(255) DEFAULT NULL,
  `visited_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;