-- ============================================
-- BlueMountainsConnect - Full Database Schema
-- Version: 1.0.0
-- ============================================

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

-- ============================================
-- TOWNS
-- ============================================
CREATE TABLE IF NOT EXISTS `bmc_towns` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `slug` VARCHAR(100) NOT NULL UNIQUE,
  `region` VARCHAR(100) DEFAULT 'Blue Mountains',
  `is_active` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- USERS
-- ============================================
CREATE TABLE IF NOT EXISTS `bmc_users` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(150) NOT NULL,
  `email` VARCHAR(255) NOT NULL UNIQUE,
  `password` VARCHAR(255) NOT NULL,
  `avatar` VARCHAR(500) DEFAULT NULL,
  `bio` TEXT DEFAULT NULL,
  `phone` VARCHAR(30) DEFAULT NULL,
  `town_id` INT UNSIGNED DEFAULT NULL,
  `role` ENUM('user','admin','superadmin') DEFAULT 'user',
  `account_type` ENUM('personal','business') DEFAULT 'personal',
  `is_approved` TINYINT(1) DEFAULT 0,
  `is_suspended` TINYINT(1) DEFAULT 0,
  `is_promoted` TINYINT(1) DEFAULT 0,
  `promotion_expires_at` TIMESTAMP NULL DEFAULT NULL,
  `screening_answers` JSON DEFAULT NULL,
  `email_verified` TINYINT(1) DEFAULT 0,
  `reset_token` VARCHAR(255) DEFAULT NULL,
  `reset_token_expires` TIMESTAMP NULL DEFAULT NULL,
  `last_login` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`town_id`) REFERENCES `bmc_towns`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- BUSINESS PROFILES
-- ============================================
CREATE TABLE IF NOT EXISTS `bmc_business_profiles` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT UNSIGNED NOT NULL UNIQUE,
  `business_name` VARCHAR(200) NOT NULL,
  `slug` VARCHAR(200) NOT NULL UNIQUE,
  `description` TEXT DEFAULT NULL,
  `category` VARCHAR(100) DEFAULT NULL,
  `address` VARCHAR(300) DEFAULT NULL,
  `town_id` INT UNSIGNED DEFAULT NULL,
  `phone` VARCHAR(30) DEFAULT NULL,
  `email` VARCHAR(255) DEFAULT NULL,
  `website` VARCHAR(500) DEFAULT NULL,
  `facebook` VARCHAR(500) DEFAULT NULL,
  `instagram` VARCHAR(500) DEFAULT NULL,
  `logo` VARCHAR(500) DEFAULT NULL,
  `cover_image` VARCHAR(500) DEFAULT NULL,
  `hours` JSON DEFAULT NULL,
  `avg_rating` DECIMAL(3,2) DEFAULT 0.00,
  `total_reviews` INT UNSIGNED DEFAULT 0,
  `is_featured` TINYINT(1) DEFAULT 0,
  `is_verified` TINYINT(1) DEFAULT 0,
  `meta_title` VARCHAR(200) DEFAULT NULL,
  `meta_description` VARCHAR(500) DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `bmc_users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`town_id`) REFERENCES `bmc_towns`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- POSTS (Feed)
-- ============================================
CREATE TABLE IF NOT EXISTS `bmc_posts` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT UNSIGNED NOT NULL,
  `town_id` INT UNSIGNED DEFAULT NULL,
  `type` ENUM('discussion','event','job','emergency','news') NOT NULL DEFAULT 'discussion',
  `title` VARCHAR(300) NOT NULL,
  `body` LONGTEXT NOT NULL,
  `images` JSON DEFAULT NULL,
  `tags` JSON DEFAULT NULL,
  `is_promoted` TINYINT(1) DEFAULT 0,
  `is_pinned` TINYINT(1) DEFAULT 0,
  `is_approved` TINYINT(1) DEFAULT 1,
  `is_deleted` TINYINT(1) DEFAULT 0,
  `views` INT UNSIGNED DEFAULT 0,
  `likes_count` INT UNSIGNED DEFAULT 0,
  `comments_count` INT UNSIGNED DEFAULT 0,
  `meta_title` VARCHAR(300) DEFAULT NULL,
  `meta_description` VARCHAR(500) DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `bmc_users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`town_id`) REFERENCES `bmc_towns`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- EVENTS (extends posts)
-- ============================================
CREATE TABLE IF NOT EXISTS `bmc_events` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `post_id` INT UNSIGNED NOT NULL UNIQUE,
  `event_date` DATE NOT NULL,
  `event_time` TIME DEFAULT NULL,
  `end_date` DATE DEFAULT NULL,
  `end_time` TIME DEFAULT NULL,
  `venue` VARCHAR(300) DEFAULT NULL,
  `address` VARCHAR(300) DEFAULT NULL,
  `town_id` INT UNSIGNED DEFAULT NULL,
  `is_free` TINYINT(1) DEFAULT 1,
  `ticket_price` DECIMAL(10,2) DEFAULT NULL,
  `ticket_url` VARCHAR(500) DEFAULT NULL,
  `rsvp_count` INT UNSIGNED DEFAULT 0,
  `max_attendees` INT UNSIGNED DEFAULT NULL,
  `category` VARCHAR(100) DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`post_id`) REFERENCES `bmc_posts`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`town_id`) REFERENCES `bmc_towns`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- JOBS (extends posts)
-- ============================================
CREATE TABLE IF NOT EXISTS `bmc_jobs` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `post_id` INT UNSIGNED NOT NULL UNIQUE,
  `company_name` VARCHAR(200) DEFAULT NULL,
  `job_type` ENUM('full-time','part-time','casual','contract','volunteer') DEFAULT 'full-time',
  `category` VARCHAR(100) DEFAULT NULL,
  `salary_min` DECIMAL(10,2) DEFAULT NULL,
  `salary_max` DECIMAL(10,2) DEFAULT NULL,
  `salary_type` ENUM('hourly','weekly','monthly','annual','negotiable') DEFAULT 'negotiable',
  `town_id` INT UNSIGNED DEFAULT NULL,
  `address` VARCHAR(300) DEFAULT NULL,
  `requirements` TEXT DEFAULT NULL,
  `benefits` TEXT DEFAULT NULL,
  `application_deadline` DATE DEFAULT NULL,
  `is_open` TINYINT(1) DEFAULT 1,
  `applications_count` INT UNSIGNED DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`post_id`) REFERENCES `bmc_posts`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`town_id`) REFERENCES `bmc_towns`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- JOB APPLICATIONS
-- ============================================
CREATE TABLE IF NOT EXISTS `bmc_job_applications` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `job_id` INT UNSIGNED NOT NULL,
  `applicant_id` INT UNSIGNED NOT NULL,
  `cover_letter` TEXT DEFAULT NULL,
  `resume_url` VARCHAR(500) DEFAULT NULL,
  `phone` VARCHAR(30) DEFAULT NULL,
  `status` ENUM('pending','reviewed','shortlisted','rejected','hired') DEFAULT 'pending',
  `employer_notes` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `unique_application` (`job_id`, `applicant_id`),
  FOREIGN KEY (`job_id`) REFERENCES `bmc_jobs`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`applicant_id`) REFERENCES `bmc_users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- COMMENTS
-- ============================================
CREATE TABLE IF NOT EXISTS `bmc_comments` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `post_id` INT UNSIGNED NOT NULL,
  `user_id` INT UNSIGNED NOT NULL,
  `parent_id` INT UNSIGNED DEFAULT NULL,
  `body` TEXT NOT NULL,
  `is_deleted` TINYINT(1) DEFAULT 0,
  `likes_count` INT UNSIGNED DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`post_id`) REFERENCES `bmc_posts`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`user_id`) REFERENCES `bmc_users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`parent_id`) REFERENCES `bmc_comments`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- LIKES
-- ============================================
CREATE TABLE IF NOT EXISTS `bmc_likes` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT UNSIGNED NOT NULL,
  `post_id` INT UNSIGNED DEFAULT NULL,
  `comment_id` INT UNSIGNED DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `unique_post_like` (`user_id`, `post_id`),
  UNIQUE KEY `unique_comment_like` (`user_id`, `comment_id`),
  FOREIGN KEY (`user_id`) REFERENCES `bmc_users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`post_id`) REFERENCES `bmc_posts`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`comment_id`) REFERENCES `bmc_comments`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- RATINGS & REVIEWS (for businesses)
-- ============================================
CREATE TABLE IF NOT EXISTS `bmc_ratings` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `business_id` INT UNSIGNED NOT NULL,
  `user_id` INT UNSIGNED NOT NULL,
  `stars` TINYINT UNSIGNED NOT NULL CHECK (`stars` BETWEEN 1 AND 5),
  `review` TEXT DEFAULT NULL,
  `is_approved` TINYINT(1) DEFAULT 1,
  `is_deleted` TINYINT(1) DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `unique_rating` (`business_id`, `user_id`),
  FOREIGN KEY (`business_id`) REFERENCES `bmc_business_profiles`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`user_id`) REFERENCES `bmc_users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- EVENT RSVPs
-- ============================================
CREATE TABLE IF NOT EXISTS `bmc_event_rsvps` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `event_id` INT UNSIGNED NOT NULL,
  `user_id` INT UNSIGNED NOT NULL,
  `status` ENUM('going','interested','not_going') DEFAULT 'going',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `unique_rsvp` (`event_id`, `user_id`),
  FOREIGN KEY (`event_id`) REFERENCES `bmc_events`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`user_id`) REFERENCES `bmc_users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- NOTIFICATIONS
-- ============================================
CREATE TABLE IF NOT EXISTS `bmc_notifications` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT UNSIGNED NOT NULL,
  `type` VARCHAR(50) NOT NULL,
  `title` VARCHAR(300) NOT NULL,
  `message` TEXT DEFAULT NULL,
  `link` VARCHAR(500) DEFAULT NULL,
  `is_read` TINYINT(1) DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `bmc_users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- PROMOTIONS / ADS
-- ============================================
CREATE TABLE IF NOT EXISTS `bmc_promotions` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT UNSIGNED DEFAULT NULL,
  `post_id` INT UNSIGNED DEFAULT NULL,
  `business_id` INT UNSIGNED DEFAULT NULL,
  `type` ENUM('promoted_post','featured_business','boosted_event','banner_ad') NOT NULL,
  `start_date` TIMESTAMP NOT NULL,
  `end_date` TIMESTAMP NOT NULL,
  `is_active` TINYINT(1) DEFAULT 1,
  `activated_by` INT UNSIGNED DEFAULT NULL,
  `notes` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `bmc_users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`post_id`) REFERENCES `bmc_posts`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`business_id`) REFERENCES `bmc_business_profiles`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- SITE SETTINGS
-- ============================================
CREATE TABLE IF NOT EXISTS `bmc_settings` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `setting_key` VARCHAR(100) NOT NULL UNIQUE,
  `setting_value` TEXT DEFAULT NULL,
  `setting_group` VARCHAR(50) DEFAULT 'general',
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- MEDIA / UPLOADS
-- ============================================
CREATE TABLE IF NOT EXISTS `bmc_media` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT UNSIGNED NOT NULL,
  `filename` VARCHAR(300) NOT NULL,
  `original_name` VARCHAR(300) NOT NULL,
  `mime_type` VARCHAR(100) NOT NULL,
  `size` INT UNSIGNED NOT NULL,
  `path` VARCHAR(500) NOT NULL,
  `url` VARCHAR(500) NOT NULL,
  `entity_type` VARCHAR(50) DEFAULT NULL,
  `entity_id` INT UNSIGNED DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `bmc_users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- REPORTS (for moderation)
-- ============================================
CREATE TABLE IF NOT EXISTS `bmc_reports` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `reporter_id` INT UNSIGNED NOT NULL,
  `post_id` INT UNSIGNED DEFAULT NULL,
  `comment_id` INT UNSIGNED DEFAULT NULL,
  `user_id` INT UNSIGNED DEFAULT NULL,
  `reason` ENUM('spam','inappropriate','misinformation','harassment','other') NOT NULL,
  `details` TEXT DEFAULT NULL,
  `status` ENUM('pending','reviewed','resolved','dismissed') DEFAULT 'pending',
  `resolved_by` INT UNSIGNED DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`reporter_id`) REFERENCES `bmc_users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- SEED DATA - Towns
-- ============================================
INSERT INTO `bmc_towns` (`name`, `slug`, `region`) VALUES
('Katoomba', 'katoomba', 'Blue Mountains'),
('Leura', 'leura', 'Blue Mountains'),
('Blackheath', 'blackheath', 'Blue Mountains'),
('Wentworth Falls', 'wentworth-falls', 'Blue Mountains'),
('Springwood', 'springwood', 'Blue Mountains'),
('Blaxland', 'blaxland', 'Blue Mountains'),
('Glenbrook', 'glenbrook', 'Blue Mountains'),
('Hazelbrook', 'hazelbrook', 'Blue Mountains'),
('Lawson', 'lawson', 'Blue Mountains'),
('Woodford', 'woodford', 'Blue Mountains'),
('Lithgow', 'lithgow', 'Lithgow'),
('Portland', 'portland', 'Lithgow'),
('Wallerawang', 'wallerawang', 'Lithgow'),
('Lithgow Rural', 'lithgow-rural', 'Lithgow'),
('Mount Victoria', 'mount-victoria', 'Blue Mountains'),
('Megalong Valley', 'megalong-valley', 'Blue Mountains'),
('Penrith', 'penrith', 'Greater Western Sydney'),
('Other', 'other', 'Other');

-- ============================================
-- SEED DATA - Site Settings
-- ============================================
INSERT INTO `bmc_settings` (`setting_key`, `setting_value`, `setting_group`) VALUES
('site_name', 'BlueMountainsConnect', 'general'),
('site_tagline', 'Your local community, connected', 'general'),
('site_email', '', 'general'),
('site_url', '', 'general'),
('allow_registrations', '1', 'general'),
('require_admin_approval', '1', 'general'),
('emergency_alert_restricted', '1', 'general'),
('promotions_enabled', '0', 'monetisation'),
('featured_businesses_enabled', '0', 'monetisation'),
('max_upload_size_mb', '10', 'media'),
('allowed_file_types', 'jpg,jpeg,png,gif,webp,pdf', 'media'),
('posts_per_page', '20', 'feed'),
('maintenance_mode', '0', 'general');