-- ====================================================================
-- BASABARI - MYMENSINGH (বাসাবাড়ি – ময়মনসিংহ)
-- Complete Production MySQL Database Schema & Seed Data (Light Mode)
-- Target Platform: cPanel / phpMyAdmin / MySQL 5.7+ & 8.0+ / MariaDB
-- Character Set: utf8mb4 / Collation: utf8mb4_unicode_ci
-- ====================================================================

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+06:00";

-- --------------------------------------------------------
-- 1. Table: users
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `users` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `phone` VARCHAR(20) NOT NULL UNIQUE,
  `email` VARCHAR(100) NOT NULL UNIQUE,
  `password_hash` VARCHAR(255) NOT NULL,
  `role` ENUM('user', 'admin', 'moderator') DEFAULT 'user',
  `avatar_url` VARCHAR(255) DEFAULT NULL,
  `is_verified` TINYINT(1) DEFAULT 1,
  `is_active` TINYINT(1) DEFAULT 1,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_phone` (`phone`),
  KEY `idx_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- 2. Table: locations (Mymensingh Areas)
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `locations` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `district` VARCHAR(50) NOT NULL DEFAULT 'ময়মনসিংহ',
  `upazila` VARCHAR(50) NOT NULL,
  `area_name` VARCHAR(100) NOT NULL,
  `ward_or_thana` VARCHAR(100) DEFAULT NULL,
  `latitude` DECIMAL(10, 7) DEFAULT NULL,
  `longitude` DECIMAL(10, 7) DEFAULT NULL,
  `is_active` TINYINT(1) DEFAULT 1,
  `display_order` INT(11) DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_upazila` (`upazila`),
  KEY `idx_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- 3. Table: property_categories (6 Exact Categories)
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `property_categories` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name_bn` VARCHAR(100) NOT NULL,
  `name_en` VARCHAR(100) NOT NULL,
  `slug` VARCHAR(50) NOT NULL UNIQUE,
  `icon_name` VARCHAR(50) DEFAULT 'Home',
  `description` TEXT DEFAULT NULL,
  `is_active` TINYINT(1) DEFAULT 1,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- 4. Table: properties (Includes 'archived' status)
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `properties` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) UNSIGNED NOT NULL,
  `category_id` INT(11) UNSIGNED NOT NULL,
  `location_id` INT(11) UNSIGNED NOT NULL,
  `rental_type` ENUM('family', 'boys_bachelor', 'girls_bachelor', 'flat', 'seat', 'sublet', 'commercial') NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `address` TEXT NOT NULL,
  `rent_type` ENUM('fixed', 'negotiable') NOT NULL DEFAULT 'fixed',
  `rent_amount` DECIMAL(10, 2) DEFAULT 0.00,
  `description` TEXT NOT NULL,
  `bedrooms` TINYINT(3) UNSIGNED DEFAULT 1,
  `bathrooms` TINYINT(3) UNSIGNED DEFAULT 1,
  `floor_number` VARCHAR(50) DEFAULT NULL,
  `contact_name` VARCHAR(100) NOT NULL,
  `contact_phone` VARCHAR(20) NOT NULL,
  `whatsapp_number` VARCHAR(20) DEFAULT NULL,
  `google_maps_url` VARCHAR(255) DEFAULT NULL,
  `latitude` DECIMAL(10, 7) DEFAULT NULL,
  `longitude` DECIMAL(10, 7) DEFAULT NULL,
  `status` ENUM('available', 'rented', 'pending', 'inactive', 'rejected', 'archived') DEFAULT 'available',
  `views_count` INT(11) UNSIGNED DEFAULT 0,
  `is_featured` TINYINT(1) DEFAULT 0,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_category_id` (`category_id`),
  KEY `idx_location_id` (`location_id`),
  KEY `idx_status` (`status`),
  CONSTRAINT `fk_prop_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_prop_cat` FOREIGN KEY (`category_id`) REFERENCES `property_categories` (`id`),
  CONSTRAINT `fk_prop_loc` FOREIGN KEY (`location_id`) REFERENCES `locations` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- 5. Table: property_images (Multiple Carousel Images)
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `property_images` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `property_id` INT(11) UNSIGNED NOT NULL,
  `image_url` VARCHAR(500) NOT NULL,
  `is_cover` TINYINT(1) DEFAULT 0,
  `sort_order` TINYINT(3) DEFAULT 0,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_property_id` (`property_id`),
  CONSTRAINT `fk_prop_images` FOREIGN KEY (`property_id`) REFERENCES `properties` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- 6. Table: property_facilities
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `property_facilities` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `property_id` INT(11) UNSIGNED NOT NULL,
  `facility_name` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_property_facility` (`property_id`),
  CONSTRAINT `fk_prop_facility` FOREIGN KEY (`property_id`) REFERENCES `properties` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- 7. Table: banner_ads (Directly Under Bottom Nav)
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `banner_ads` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(255) NOT NULL,
  `image_url` VARCHAR(500) NOT NULL,
  `destination_url` VARCHAR(500) NOT NULL,
  `advertiser_name` VARCHAR(150) NOT NULL,
  `start_date` DATE NOT NULL,
  `end_date` DATE NOT NULL,
  `is_active` TINYINT(1) DEFAULT 1,
  `priority` INT(11) DEFAULT 0,
  `placement` VARCHAR(50) DEFAULT 'bottom_nav_banner',
  `impressions` INT(11) UNSIGNED DEFAULT 0,
  `clicks` INT(11) UNSIGNED DEFAULT 0,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- 8. Table: reports (User complaints)
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `reports` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `property_id` INT(11) UNSIGNED NOT NULL,
  `property_title` VARCHAR(255) NOT NULL,
  `reporter_name` VARCHAR(100) NOT NULL,
  `reporter_phone` VARCHAR(20) NOT NULL,
  `reason` VARCHAR(255) NOT NULL,
  `message` TEXT DEFAULT NULL,
  `status` ENUM('pending', 'reviewed', 'dismissed', 'resolved') DEFAULT 'pending',
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_report_prop` (`property_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- 9. Table: app_settings (AdMob & SMTP Configuration)
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `app_settings` (
  `key_name` VARCHAR(100) NOT NULL,
  `key_value` TEXT DEFAULT NULL,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`key_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ====================================================================
-- SEED DATA INSERTION
-- ====================================================================

-- Users (Seed Admin & Landlords)
INSERT INTO `users` (`id`, `name`, `phone`, `email`, `password_hash`, `role`) VALUES
(1, 'মোঃ রফিকুল ইসলাম (বাড়িওয়ালা)', '01711223344', 'rafiq.mymen@gmail.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin'),
(2, 'তাহমিদ হাসান (মেস ম্যানেজার)', '01822334455', 'tahmid.ananda@gmail.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'user'),
(3, 'সুমাইয়া জাহান', '01933445566', 'sumaiya.med@gmail.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'user');

-- Locations (Mymensingh Areas)
INSERT INTO `locations` (`id`, `district`, `upazila`, `area_name`, `ward_or_thana`, `latitude`, `longitude`, `display_order`) VALUES
(1, 'ময়মনসিংহ', 'ময়মনসিংহ সদর', 'চরপাড়া (Charpara)', 'ওয়ার্ড নং ১৩ (মেডিকেল কলেজ সংলগ্ন)', 24.7431000, 90.4125000, 1),
(2, 'ময়মনসিংহ', 'ময়মনসিংহ সদর', 'গাঙ্গিনার পাড় (Ganginar Par)', 'ওয়ার্ড নং ৮ (ষ্টেশন রোড)', 24.7554000, 90.4072000, 2),
(3, 'ময়মনসিংহ', 'ময়মনসিংহ সদর', 'নতুন বাজার (Notun Bazar)', 'ওয়ার্ড নং ১০', 24.7578000, 90.4021000, 3),
(4, 'ময়মনসিংহ', 'ময়মনসিংহ সদর', 'সাঙ্কিপাড়া (Sankipara)', 'ওয়ার্ড নং ৫ (রেলগেট সংলগ্ন)', 24.7612000, 90.3958000, 4),
(5, 'ময়মনসিংহ', 'ময়মনসিংহ সদর', 'আকুয়া (Akua)', 'ওয়ার্ড নং ১৪ (বাইপাস মোড়)', 24.7389000, 90.3892000, 5),
(6, 'ময়মনসিংহ', 'ময়মনসিংহ সদর', 'মাসকান্দা (Maskanda)', 'ওয়ার্ড নং ১১ (বাস টার্মিনাল সংলগ্ন)', 24.7482000, 90.3985000, 6),
(7, 'ময়মনসিংহ', 'ময়মনসিংহ সদর', 'কৃষ্ঠপুর (Krishtopur)', 'ওয়ার্ড নং ৪', 24.7667000, 90.4118000, 7),
(8, 'ময়মনসিংহ', 'ময়মনসিংহ সদর', 'শেহড়া (Shehora)', 'ওয়ার্ড নং ৯ (ডিবি রোড)', 24.7495000, 90.4150000, 8),
(9, 'ময়মনসিংহ', 'ময়মনসিংহ সদর', 'কাঁচিঝুলি (Kachijhuli)', 'ওয়ার্ড নং ৭ (টাউন হল সংলগ্ন)', 24.7589000, 90.4098000, 9);

-- 6 Exact Categories as Requested
INSERT INTO `property_categories` (`id`, `name_bn`, `name_en`, `slug`, `icon_name`, `description`) VALUES
(1, 'ফ্যামিলি বাসা ভাড়া', 'Family House Rent', 'family', 'Home', 'পারিবারিক পরিবেশের সম্পূর্ণ ফ্যামিলি ফ্ল্যাট ও স্বতন্ত্র বাসা'),
(2, '👨‍🎓 ছেলেদের ব্যাচেলর বাসা', 'Boys Bachelor Mess', 'boys-bachelor', 'GraduationCap', 'ছাত্র ও চাকুরিজীবী ছেলেদের জন্য রুম ও মেস সিট'),
(3, '👩‍🎓 মেয়েদের ব্যাচেলর বাসা', 'Girls Bachelor Hostel', 'girls-bachelor', 'Users', 'ছাত্রী ও চাকুরিজীবী নারীদের জন্য নিরাপদ ও সুসজ্জিত মেস বা হোস্টেল'),
(4, '🏢 ফ্ল্যাট ভাড়া', 'Apartment / Flat', 'flat', 'Building', 'আধুনিক নাগরিক সুযোগ-সুবিধা সম্বলিত রেডি এপার্টমেন্ট ফ্ল্যাট'),
(5, '🛏️ সিট ভাড়া', 'Seat Rent', 'seat', 'Bed', 'একক সিট বা শেয়ারিং বেড ভাড়া'),
(6, '🏘️ সাবলেট ভাড়া', 'Sublet Room', 'sublet', 'Layers', 'একটি পরিবারের সাথে আলাদা একক রুম বা সাবলেট ভাড়া');

-- Properties
INSERT INTO `properties` (`id`, `user_id`, `category_id`, `location_id`, `rental_type`, `title`, `address`, `rent_type`, `rent_amount`, `description`, `bedrooms`, `bathrooms`, `floor_number`, `contact_name`, `contact_phone`, `whatsapp_number`, `google_maps_url`, `status`, `views_count`, `is_featured`) VALUES
(1, 1, 1, 1, 'family', 'চরপাড়ায় মেডিকেল মোড় সংলগ্ন মনোরম ফ্যামিলি বাসা ভাড়া হবে', 'রোড নং ৪, বাসা নং ১২/এ, চরপাড়া নয়াপাড়া, ময়মনসিংহ', 'fixed', 12500.00, 'সম্পূর্ণ টাইলস করা আলো-বাতাসপূর্ণ দক্ষিণমুখী পারিবারিক বাসা। ময়মনসিংহ মেডিকেল কলেজ হাসপাতাল থেকে মাত্র ৩ মিনিটের হাঁটা দূরত্ব। সার্বক্ষণিক তিতাস গ্যাস, নিরবচ্ছিন্ন পানির ব্যবস্থা ও প্রিপেইড বিদ্যুৎ মিটার রয়েছে। সিসি ক্যামেরা নিয়ন্ত্রিত নিরাপদ পরিবেশ। ছোট বা মাঝারি পরিবারের জন্য উপযুক্ত।', 3, 2, '৩য় তলা', 'মোঃ রফিকুল ইসলাম', '01711223344', '01711223344', 'https://maps.google.com/?q=24.7431,90.4125', 'available', 284, 1),
(2, 2, 2, 2, 'boys_bachelor', 'গাঙ্গিনার পাড়ে আনন্দ মোহন কলেজ সংলগ্ন নিরিবিলি ব্যাচেলর মেস সিট', 'ষ্টেশন রোড, কলেজ গেট গলি, গাঙ্গিনার পাড়, ময়মনসিংহ', 'fixed', 2800.00, 'আনন্দ মোহন কলেজের ছাত্রদের জন্য সুশৃঙ্খল স্টুডেন্ট মেস। পড়াশোনার চমৎকার পরিবেশ। হাই-স্পিড ওয়াইফাই, ফিল্টারের বিশুদ্ধ পানি, খালার রান্না খাবার ব্যবস্থা রয়েছে। প্রতিটি রুমে সর্বোচ্চ ২ জন।', 1, 1, '২য় তলা', 'তাহমিদ হাসান', '01822334455', '01822334455', 'https://maps.google.com/?q=24.7554,90.4072', 'available', 195, 1),
(3, 3, 3, 1, 'girls_bachelor', 'চরপাড়া মেডিকেল হোস্টেল সংলগ্ন সুসজ্জিত ছাত্রী মেস সিট', 'মেডিকেল পূর্ব গেট, চরপাড়া, ময়মনসিংহ', 'fixed', 3200.00, 'মেডিকেল ও নার্সিং ছাত্রীদের জন্য সম্পূর্ণ নিরাপদ লেডিস মেস। সিসিটিভি ক্যামেরা ও সার্বক্ষণিক কেয়ারটেকার নিয়ন্ত্রিত। ওয়াইফাই, ফিল্টার পানি ও খাবার সুবিধা আছে।', 1, 1, '৪র্থ তলা', 'সুমাইয়া জাহান', '01933445566', '01933445566', 'https://maps.google.com/?q=24.7420,90.4140', 'available', 312, 1);

-- Property Images (Multi-Image Carousel)
INSERT INTO `property_images` (`property_id`, `image_url`, `is_cover`, `sort_order`) VALUES
(1, 'https://images.unsplash.com/photo-1502672260266-1c1ef2d93688?auto=format&fit=crop&w=1200&q=80', 1, 0),
(1, 'https://images.unsplash.com/photo-1560448204-e02f11c3d0e2?auto=format&fit=crop&w=1200&q=80', 0, 1),
(1, 'https://images.unsplash.com/photo-1484154218962-a197022b5858?auto=format&fit=crop&w=1200&q=80', 0, 2),
(2, 'https://images.unsplash.com/photo-1522771739844-6a9f6d5f14af?auto=format&fit=crop&w=1200&q=80', 1, 0),
(2, 'https://images.unsplash.com/photo-1586023492125-27b2c045efd7?auto=format&fit=crop&w=1200&q=80', 0, 1),
(3, 'https://images.unsplash.com/photo-1540518614846-7ede433c4b69?auto=format&fit=crop&w=1200&q=80', 1, 0),
(3, 'https://images.unsplash.com/photo-1595526114035-0d45ed16cfbf?auto=format&fit=crop&w=1200&q=80', 0, 1);

-- Property Facilities
INSERT INTO `property_facilities` (`property_id`, `facility_name`) VALUES
(1, 'পানি'), (1, 'বিদ্যুৎ'), (1, 'গ্যাস'), (1, 'Wi-Fi'), (1, 'রান্নাঘর'), (1, 'অ্যাটাচড বাথরুম'), (1, 'ব্যালকনি'), (1, 'নিরাপত্তা'),
(2, 'পানি'), (2, 'বিদ্যুৎ'), (2, 'Wi-Fi'), (2, 'রান্নাঘর'), (2, 'নিরাপত্তা'),
(3, 'পানি'), (3, 'বিদ্যুৎ'), (3, 'Wi-Fi'), (3, 'রান্নাঘর'), (3, 'সিসিটিভি'), (3, 'ফিল্টার পানি');

-- Banner Ads (Under Bottom Nav)
INSERT INTO `banner_ads` (`title`, `image_url`, `destination_url`, `advertiser_name`, `start_date`, `end_date`, `is_active`, `placement`) VALUES
('ময়মনসিংহ হোম শিফটিং ও পিকআপ সার্ভিস – স্বল্প খরচে বাসা বদল', 'https://images.unsplash.com/photo-1600585154340-be6161a56a0c?auto=format&fit=crop&w=1200&q=80', 'https://wa.me/8801711223344?text=HomeShifting', 'ব্রহ্মপুত্র শিফটিং ও লজিস্টিকস', '2026-01-01', '2026-12-31', 1, 'bottom_nav_banner');

-- App Settings (AdMob + SMTP Defaults)
INSERT INTO `app_settings` (`key_name`, `key_value`) VALUES
('app_name', 'বাসাবাড়ি – ময়মনসিংহ'),
('admob_app_id', 'ca-app-pub-3940256099942544~3347511713'),
('admob_interstitial_id', 'ca-app-pub-3940256099942544/1033173712'),
('admob_banner_id', 'ca-app-pub-3940256099942544/6300978111'),
('interstitial_countdown_seconds', '10'),
('app_download_url', 'https://basabarimthemedokan.com/download/basabari-mymensingh-v1.0.apk'),
('app_version', '1.0.0'),
('developer_name', 'Rakib Hussain'),
('developer_title', 'Web & Software Developer'),
('smtp_host', 'mail.basabarimthemedokan.com'),
('smtp_port', '465'),
('smtp_user', 'no-reply@basabarimthemedokan.com'),
('smtp_pass', 'MymensinghPass2026!'),
('smtp_encryption', 'ssl'),
('smtp_from_name', 'বাসাবাড়ি ময়মনসিংহ'),
('smtp_from_email', 'no-reply@basabarimthemedokan.com');

COMMIT;