-- =============================================
-- তজিয়া গ্রুপ — Database Schema
-- MySQL 5.7+ / MariaDB 10.3+
-- =============================================

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

USE `tojiya_group`;

-- =============================================
-- Admins
-- =============================================
CREATE TABLE IF NOT EXISTS `admins` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `username` VARCHAR(50) NOT NULL UNIQUE,
  `password` VARCHAR(255) NOT NULL,
  `display_name` VARCHAR(100) NOT NULL DEFAULT 'Admin',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Default admin: admin / admin123 (change immediately in production!)
INSERT INTO `admins` (`username`, `password`, `display_name`) VALUES
('admin', '$2y$10$HyGAQfcDEPwtTt1s.AKiEemSZyWQo3kObcosKU.MGWy5Fwe2v0i0W', 'তজিয়া অ্যাডমিন');

-- =============================================
-- Couples
-- =============================================
CREATE TABLE IF NOT EXISTS `couples` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `boy_name` VARCHAR(150) NOT NULL,
  `girl_name` VARCHAR(150) NOT NULL,
  `nickname` VARCHAR(150) DEFAULT NULL,
  `marriage_date` DATE DEFAULT NULL,
  `district` VARCHAR(100) DEFAULT NULL,
  `upazila` VARCHAR(100) DEFAULT NULL,
  `short_story` TEXT DEFAULT NULL,
  `love_duration` VARCHAR(100) DEFAULT NULL,
  `facebook_link` VARCHAR(500) DEFAULT NULL,
  `photo` VARCHAR(255) DEFAULT NULL,
  `video_upload` VARCHAR(255) DEFAULT NULL,
  `press_video_link` VARCHAR(500) DEFAULT NULL,
  `funny_title` VARCHAR(255) DEFAULT NULL,
  `status_text` VARCHAR(255) DEFAULT NULL,
  `contact_number` VARCHAR(30) DEFAULT NULL,
  `status` ENUM('pending','approved','rejected') DEFAULT 'pending',
  `is_featured` TINYINT(1) DEFAULT 0,
  `views` INT UNSIGNED DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_status` (`status`),
  INDEX `idx_district` (`district`),
  INDEX `idx_featured` (`is_featured`),
  INDEX `idx_views` (`views`),
  INDEX `idx_marriage_date` (`marriage_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- Galleries (additional photos for couples)
-- =============================================
CREATE TABLE IF NOT EXISTS `galleries` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `couple_id` INT UNSIGNED NOT NULL,
  `image_path` VARCHAR(255) NOT NULL,
  `caption` VARCHAR(255) DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`couple_id`) REFERENCES `couples`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- Videos
-- =============================================
CREATE TABLE IF NOT EXISTS `videos` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `couple_id` INT UNSIGNED DEFAULT NULL,
  `title` VARCHAR(255) NOT NULL,
  `description` TEXT DEFAULT NULL,
  `video_type` ENUM('youtube','upload') DEFAULT 'youtube',
  `video_url` VARCHAR(500) NOT NULL,
  `thumbnail` VARCHAR(255) DEFAULT NULL,
  `views` INT UNSIGNED DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`couple_id`) REFERENCES `couples`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- Comments
-- =============================================
CREATE TABLE IF NOT EXISTS `comments` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `couple_id` INT UNSIGNED NOT NULL,
  `commenter_name` VARCHAR(100) NOT NULL,
  `comment_text` TEXT NOT NULL,
  `status` ENUM('pending','approved') DEFAULT 'approved',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`couple_id`) REFERENCES `couples`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- Settings
-- =============================================
CREATE TABLE IF NOT EXISTS `settings` (
  `setting_key` VARCHAR(100) PRIMARY KEY,
  `setting_value` TEXT DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Default settings
INSERT INTO `settings` (`setting_key`, `setting_value`) VALUES
('site_title', 'তজিয়া গ্রুপ'),
('site_description', 'পালিয়ে বিয়ে করা কাপলদের ভাইরাল আর্কাইভ'),
('hero_text_1', 'ভালোবাসা থামানো যায় না 😎'),
('hero_text_2', 'পরিবার মানেনি, তজিয়া গ্রুপ মানছে ❤️'),
('hero_text_3', 'পালিয়ে বিয়ে? ইতিহাসে নাম লিখান!'),
('ticker_text', '🔥 তজিয়া গ্রুপে স্বাগতম! পালিয়ে বিয়ে করেছেন? আপনার গল্প শেয়ার করুন! 💕 প্রতিদিন নতুন কাপল যোগ হচ্ছে! 🎉'),
('footer_text', '© ২০২৬ তজিয়া গ্রুপ — সকল স্বত্ব সংরক্ষিত'),
('about_text', 'তজিয়া গ্রুপ হলো বাংলাদেশের প্রথম পালিয়ে বিয়ে করা কাপলদের তথ্য সংরক্ষণ ও বিনোদনমূলক ওয়েবসাইট।');
