

-- =============================================
-- LOCATIONS
-- =============================================

CREATE TABLE `locations` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(255) NOT NULL,
  `slug` VARCHAR(255) UNIQUE NOT NULL,
  `type` ENUM('city', 'area', 'neighborhood') DEFAULT 'city',
  `parent_id` BIGINT UNSIGNED NULL,
  `latitude` DECIMAL(10, 8),
  `longitude` DECIMAL(11, 8),
  `description` TEXT,
  `image` VARCHAR(255),
  `property_count` INT DEFAULT 0,
  `is_featured` BOOLEAN DEFAULT FALSE,
  `status` ENUM('active', 'inactive') DEFAULT 'active',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`parent_id`) REFERENCES `locations`(`id`) ON DELETE SET NULL,
  INDEX `idx_slug` (`slug`),
  INDEX `idx_parent` (`parent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- PROPERTY TYPES
-- =============================================

CREATE TABLE `property_types` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `slug` VARCHAR(100) UNIQUE NOT NULL,
  `icon` VARCHAR(255),
  `description` TEXT,
  `property_count` INT DEFAULT 0,
  `status` ENUM('active', 'inactive') DEFAULT 'active',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- AGENTS
-- =============================================

CREATE TABLE `agents` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` BIGINT UNSIGNED UNIQUE,
  `name` VARCHAR(255) NOT NULL,
  `email` VARCHAR(255) UNIQUE NOT NULL,
  `phone` VARCHAR(20),
  `whatsapp` VARCHAR(20),
  `designation` VARCHAR(100),
  `bio` TEXT,
  `avatar` VARCHAR(255),
  `facebook` VARCHAR(255),
  `instagram` VARCHAR(255),
  `linkedin` VARCHAR(255),
  `twitter` VARCHAR(255),
  `license_number` VARCHAR(100),
  `experience_years` INT,
  `properties_sold` INT DEFAULT 0,
  `is_featured` BOOLEAN DEFAULT FALSE,
  `status` ENUM('active', 'inactive') DEFAULT 'active',
  `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,
  INDEX `idx_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- PROPERTIES
-- =============================================

CREATE TABLE `properties` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(255) NOT NULL,
  `slug` VARCHAR(255) UNIQUE NOT NULL,
  `property_type_id` BIGINT UNSIGNED NOT NULL,
  `location_id` BIGINT UNSIGNED NOT NULL,
  `agent_id` BIGINT UNSIGNED,
  
  -- Property Details
  `description` LONGTEXT,
  `short_description` TEXT,
  `address` VARCHAR(500),
  `latitude` DECIMAL(10, 8),
  `longitude` DECIMAL(11, 8),
  `zip_code` VARCHAR(20),
  
  -- Pricing
  `price` DECIMAL(15, 2) NOT NULL,
  `price_type` ENUM('fixed', 'negotiable', 'from') DEFAULT 'fixed',
  `currency` VARCHAR(10) DEFAULT 'BDT',
  
  -- Status
  `status` ENUM('available', 'sold', 'rented', 'pending') DEFAULT 'available',
  `purpose` ENUM('sale', 'rent', 'both') DEFAULT 'sale',
  
  -- Specifications
  `bedrooms` INT,
  `bathrooms` INT,
  `area_sqft` DECIMAL(10, 2),
  `area_type` VARCHAR(50),
  `floors` INT,
  `year_built` INT,
  `parking_spaces` INT,
  
  -- Media
  `featured_image` VARCHAR(255),
  `video_url` VARCHAR(500),
  `virtual_tour_url` VARCHAR(500),
  `floor_plan` VARCHAR(255),
  
  -- SEO
  `meta_title` VARCHAR(255),
  `meta_description` TEXT,
  `meta_keywords` VARCHAR(500),
  
  -- Features
  `is_featured` BOOLEAN DEFAULT FALSE,
  `is_verified` BOOLEAN DEFAULT FALSE,
  `is_urgent` BOOLEAN DEFAULT FALSE,
  `views` INT DEFAULT 0,
  `favorites` INT DEFAULT 0,
  `inquiries` INT DEFAULT 0,
  
  -- Publishing
  `published_at` TIMESTAMP NULL,
  `expires_at` TIMESTAMP NULL,
  
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  FOREIGN KEY (`property_type_id`) REFERENCES `property_types`(`id`) ON DELETE RESTRICT,
  FOREIGN KEY (`location_id`) REFERENCES `locations`(`id`) ON DELETE RESTRICT,
  FOREIGN KEY (`agent_id`) REFERENCES `agents`(`id`) ON DELETE SET NULL,
  
  INDEX `idx_slug` (`slug`),
  INDEX `idx_status` (`status`),
  INDEX `idx_purpose` (`purpose`),
  INDEX `idx_price` (`price`),
  INDEX `idx_featured` (`is_featured`),
  INDEX `idx_published` (`published_at`),
  FULLTEXT INDEX `idx_search` (`title`, `description`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- PROPERTY IMAGES
-- =============================================

CREATE TABLE `property_images` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `property_id` BIGINT UNSIGNED NOT NULL,
  `image_path` VARCHAR(255) NOT NULL,
  `title` VARCHAR(255),
  `is_featured` BOOLEAN DEFAULT FALSE,
  `sort_order` INT DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`property_id`) REFERENCES `properties`(`id`) ON DELETE CASCADE,
  INDEX `idx_property` (`property_id`),
  INDEX `idx_sort` (`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- PROPERTY FEATURES
-- =============================================

CREATE TABLE `features` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `icon` VARCHAR(100),
  `category` VARCHAR(50),
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `property_features` (
  `property_id` BIGINT UNSIGNED NOT NULL,
  `feature_id` BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (`property_id`, `feature_id`),
  FOREIGN KEY (`property_id`) REFERENCES `properties`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`feature_id`) REFERENCES `features`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- LEADS & INQUIRIES
-- =============================================

CREATE TABLE `leads` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `property_id` BIGINT UNSIGNED NULL,
  `agent_id` BIGINT UNSIGNED NULL,
  
  -- Contact Info
  `name` VARCHAR(255) NOT NULL,
  `email` VARCHAR(255) NOT NULL,
  `phone` VARCHAR(20),
  
  -- Inquiry Details
  `subject` VARCHAR(255),
  `message` TEXT NOT NULL,
  `inquiry_type` ENUM('general', 'property', 'viewing', 'callback') DEFAULT 'general',
  
  -- Additional Info
  `budget_min` DECIMAL(15, 2),
  `budget_max` DECIMAL(15, 2),
  `preferred_location` VARCHAR(255),
  `property_type` VARCHAR(100),
  
  -- Status
  `status` ENUM('new', 'contacted', 'qualified', 'converted', 'lost') DEFAULT 'new',
  `priority` ENUM('low', 'medium', 'high') DEFAULT 'medium',
  `notes` TEXT,
  
  -- Tracking
  `source` VARCHAR(100),
  `ip_address` VARCHAR(45),
  `user_agent` TEXT,
  `assigned_to` BIGINT UNSIGNED NULL,
  `contacted_at` TIMESTAMP NULL,
  
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  FOREIGN KEY (`property_id`) REFERENCES `properties`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`agent_id`) REFERENCES `agents`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`assigned_to`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  
  INDEX `idx_email` (`email`),
  INDEX `idx_status` (`status`),
  INDEX `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- BLOG SYSTEM
-- =============================================

CREATE TABLE `blog_categories` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `slug` VARCHAR(100) UNIQUE NOT NULL,
  `description` TEXT,
  `image` VARCHAR(255),
  `post_count` INT DEFAULT 0,
  `status` ENUM('active', 'inactive') DEFAULT 'active',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `blog_posts` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(255) NOT NULL,
  `slug` VARCHAR(255) UNIQUE NOT NULL,
  `category_id` BIGINT UNSIGNED,
  `author_id` BIGINT UNSIGNED NOT NULL,
  
  `excerpt` TEXT,
  `content` LONGTEXT,
  `featured_image` VARCHAR(255),
  
  -- SEO
  `meta_title` VARCHAR(255),
  `meta_description` TEXT,
  `meta_keywords` VARCHAR(500),
  
  -- Stats
  `views` INT DEFAULT 0,
  `likes` INT DEFAULT 0,
  
  -- Status
  `status` ENUM('draft', 'published', 'scheduled') DEFAULT 'draft',
  `is_featured` BOOLEAN DEFAULT FALSE,
  `published_at` TIMESTAMP NULL,
  
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  FOREIGN KEY (`category_id`) REFERENCES `blog_categories`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`author_id`) REFERENCES `users`(`id`) ON DELETE RESTRICT,
  
  INDEX `idx_slug` (`slug`),
  INDEX `idx_status` (`status`),
  INDEX `idx_published` (`published_at`),
  FULLTEXT INDEX `idx_search` (`title`, `content`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- TESTIMONIALS
-- =============================================

CREATE TABLE `testimonials` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(255) NOT NULL,
  `designation` VARCHAR(100),
  `company` VARCHAR(255),
  `avatar` VARCHAR(255),
  `rating` TINYINT DEFAULT 5,
  `message` TEXT NOT NULL,
  `is_featured` BOOLEAN DEFAULT FALSE,
  `status` ENUM('active', 'inactive') DEFAULT 'active',
  `sort_order` INT DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_status` (`status`),
  INDEX `idx_sort` (`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- CMS PAGES
-- =============================================

CREATE TABLE `pages` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(255) NOT NULL,
  `slug` VARCHAR(255) UNIQUE NOT NULL,
  `content` LONGTEXT,
  
  -- SEO
  `meta_title` VARCHAR(255),
  `meta_description` TEXT,
  `meta_keywords` VARCHAR(500),
  
  -- Template
  `template` VARCHAR(100) DEFAULT 'default',
  `featured_image` VARCHAR(255),
  
  `status` ENUM('active', 'inactive') DEFAULT 'active',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- ACTIVITY LOG
-- =============================================

CREATE TABLE `activity_logs` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` BIGINT UNSIGNED NULL,
  `action` VARCHAR(100) NOT NULL,
  `model_type` VARCHAR(100),
  `model_id` BIGINT UNSIGNED,
  `description` TEXT,
  `ip_address` VARCHAR(45),
  `user_agent` TEXT,
  `properties` JSON,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX `idx_user` (`user_id`),
  INDEX `idx_model` (`model_type`, `model_id`),
  INDEX `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- EMAIL TEMPLATES
-- =============================================

CREATE TABLE `email_templates` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) UNIQUE NOT NULL,
  `subject` VARCHAR(255) NOT NULL,
  `content` LONGTEXT NOT NULL,
  `variables` JSON,
  `status` ENUM('active', 'inactive') DEFAULT 'active',
  `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;

-- =============================================
-- VIEWING APPOINTMENTS
-- =============================================

CREATE TABLE `viewing_appointments` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `property_id` BIGINT UNSIGNED NOT NULL,
  `agent_id` BIGINT UNSIGNED,
  `name` VARCHAR(255) NOT NULL,
  `email` VARCHAR(255) NOT NULL,
  `phone` VARCHAR(20),
  `preferred_date` DATE NOT NULL,
  `preferred_time` TIME NOT NULL,
  `message` TEXT,
  `status` ENUM('pending', 'confirmed', 'completed', 'cancelled') DEFAULT 'pending',
  `notes` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`property_id`) REFERENCES `properties`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`agent_id`) REFERENCES `agents`(`id`) ON DELETE SET NULL,
  INDEX `idx_date` (`preferred_date`),
  INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- INITIAL DATA SEEDING
-- =============================================

-- Insert default features
INSERT INTO `features` (`name`, `icon`, `category`) VALUES
('Air Conditioning', 'fa-snowflake', 'Comfort'),
('Heating', 'fa-fire', 'Comfort'),
('Swimming Pool', 'fa-swimming-pool', 'Outdoor'),
('Gym', 'fa-dumbbell', 'Amenities'),
('Security System', 'fa-shield-alt', 'Security'),
('CCTV', 'fa-video', 'Security'),
('Garden', 'fa-leaf', 'Outdoor'),
('Balcony', 'fa-warehouse', 'Features'),
('Garage', 'fa-car', 'Parking'),
('Elevator', 'fa-elevator', 'Building'),
('Furnished', 'fa-couch', 'Features'),
('Pet Friendly', 'fa-paw', 'Features'),
('Wifi', 'fa-wifi', 'Technology'),
('Fireplace', 'fa-fire-alt', 'Comfort'),
('Solar Panels', 'fa-solar-panel', 'Sustainability');

-- Insert default property types
INSERT INTO `property_types` (`name`, `slug`, `icon`) VALUES
('Apartment', 'apartment', 'fa-building'),
('Villa', 'villa', 'fa-home'),
('Land', 'land', 'fa-map'),
('Commercial', 'commercial', 'fa-store'),
('Office Space', 'office-space', 'fa-briefcase'),
('Penthouse', 'penthouse', 'fa-city');

-- Insert sample locations
INSERT INTO `locations` (`name`, `slug`, `type`, `latitude`, `longitude`) VALUES
('Dhaka', 'dhaka', 'city', 23.8103, 90.4125),
('Gulshan', 'gulshan', 'area', 23.7805, 90.4147),
('Banani', 'banani', 'area', 23.7937, 90.4066),
('Dhanmondi', 'dhanmondi', 'area', 23.7461, 90.3742),
('Mirpur', 'mirpur', 'area', 23.8223, 90.3654),
('Uttara', 'uttara', 'area', 23.8759, 90.3795);
