-- =============================================
-- DUBAI REAL ESTATE MARKETPLACE - MYSQL SCHEMA
-- DLD & RERA Compliant
-- =============================================

-- =============================================
-- CORE USER MANAGEMENT
-- =============================================

CREATE TABLE roles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL UNIQUE, -- super_admin, developer, agency, agent, customer
    display_name VARCHAR(100) NOT NULL,
    description TEXT,
    is_system_role BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

INSERT INTO roles (name, display_name, is_system_role) VALUES
('super_admin', 'Super Administrator', TRUE),
('developer', 'Property Developer', TRUE),
('agency', 'Real Estate Agency', TRUE),
('agent', 'Real Estate Agent', TRUE),
('customer', 'Property Seeker', TRUE);

CREATE TABLE users (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    phone VARCHAR(20),
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    avatar_url VARCHAR(500),
    
    -- RERA Compliance Fields
    rera_permit_number VARCHAR(50), -- For agents
    brn VARCHAR(50), -- Broker Registration Number (for agents)
    orn VARCHAR(50), -- Office Registration Number (for agencies)
    rera_verified_at TIMESTAMP NULL,
    rera_verification_status ENUM('pending', 'verified', 'rejected') DEFAULT 'pending',
    
    -- Account Status
    status ENUM('active', 'inactive', 'suspended', 'pending_verification') DEFAULT 'pending_verification',
    email_verified_at TIMESTAMP NULL,
    phone_verified_at TIMESTAMP NULL,
    last_login_at TIMESTAMP NULL,
    last_login_ip VARCHAR(45),
    
    -- Multi-role support (primary role here, additional in user_roles)
    primary_role_id INT NOT NULL,
    
    -- Preferences
    preferred_language ENUM('en', 'ar') DEFAULT 'en',
    currency_preference VARCHAR(3) DEFAULT 'AED',
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    
    FOREIGN KEY (primary_role_id) REFERENCES roles(id),
    INDEX idx_users_email (email),
    INDEX idx_users_rera (rera_permit_number),
    INDEX idx_users_brn (brn),
    INDEX idx_users_status (status)
);

CREATE TABLE user_roles (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    role_id INT NOT NULL,
    assigned_by BIGINT,
    assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP NULL,
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
    FOREIGN KEY (assigned_by) REFERENCES users(id),
    UNIQUE KEY unique_user_role (user_id, role_id)
);

-- =============================================
-- AGENCY MANAGEMENT
-- =============================================

CREATE TABLE agencies (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(200) NOT NULL,
    slug VARCHAR(200) NOT NULL UNIQUE,
    description TEXT,
    logo_url VARCHAR(500),
    cover_image_url VARCHAR(500),
    
    -- Contact Information
    email VARCHAR(255) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    website VARCHAR(255),
    
    -- Location
    address TEXT,
    city VARCHAR(100) DEFAULT 'Dubai',
    country VARCHAR(100) DEFAULT 'UAE',
    latitude DECIMAL(10, 8),
    longitude DECIMAL(11, 8),
    
    -- RERA Compliance
    orn VARCHAR(50) NOT NULL, -- Office Registration Number
    rera_verified_at TIMESTAMP NULL,
    rera_verification_status ENUM('pending', 'verified', 'rejected') DEFAULT 'pending',
    
    -- Business Details
    trade_license_number VARCHAR(100),
    company_registration_number VARCHAR(100),
    
    -- Settings
    max_agents_allowed INT DEFAULT 5,
    auto_assign_leads BOOLEAN DEFAULT TRUE,
    assignment_round_robin BOOLEAN DEFAULT TRUE,
    
    -- Owner/Manager
    owner_id BIGINT NOT NULL,
    
    status ENUM('active', 'inactive', 'suspended') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    
    FOREIGN KEY (owner_id) REFERENCES users(id),
    INDEX idx_agencies_orn (orn),
    INDEX idx_agencies_status (status),
    INDEX idx_agencies_slug (slug)
);

CREATE TABLE agency_agents (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    agency_id BIGINT NOT NULL,
    agent_id BIGINT NOT NULL,
    
    -- Agent Details within Agency
    designation VARCHAR(100), -- Senior Agent, Junior Agent, etc.
    commission_split DECIMAL(5,2) DEFAULT 50.00, -- Percentage to agent
    
    -- Specializations
    specializes_off_plan BOOLEAN DEFAULT FALSE,
    specializes_ready BOOLEAN DEFAULT FALSE,
    specializes_commercial BOOLEAN DEFAULT FALSE,
    specializes_residential BOOLEAN DEFAULT FALSE,
    specializes_luxury BOOLEAN DEFAULT FALSE,
    
    -- Performance
    total_leads_assigned INT DEFAULT 0,
    total_leads_converted INT DEFAULT 0,
    
    joined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    left_at TIMESTAMP NULL,
    is_active BOOLEAN DEFAULT TRUE,
    
    FOREIGN KEY (agency_id) REFERENCES agencies(id) ON DELETE CASCADE,
    FOREIGN KEY (agent_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_agency_agent (agency_id, agent_id),
    INDEX idx_agency_agents_active (agency_id, is_active)
);

-- =============================================
-- DEVELOPER MANAGEMENT
-- =============================================

CREATE TABLE developers (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(200) NOT NULL,
    slug VARCHAR(200) NOT NULL UNIQUE,
    description TEXT,
    logo_url VARCHAR(500),
    cover_image_url VARCHAR(500),
    
    -- Contact Information
    email VARCHAR(255) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    website VARCHAR(255),
    
    -- Location
    headquarters_address TEXT,
    city VARCHAR(100) DEFAULT 'Dubai',
    country VARCHAR(100) DEFAULT 'UAE',
    
    -- Business Details
    trade_license_number VARCHAR(100),
    company_registration_number VARCHAR(100),
    
    -- Reputation
    established_year INT,
    total_projects_completed INT DEFAULT 0,
    total_units_delivered INT DEFAULT 0,
    
    -- Owner/Manager
    owner_id BIGINT NOT NULL,
    
    status ENUM('active', 'inactive', 'suspended') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    
    FOREIGN KEY (owner_id) REFERENCES users(id),
    INDEX idx_developers_slug (slug),
    INDEX idx_developers_status (status)
);

-- =============================================
-- PROPERTY MASTER DATA
-- =============================================

CREATE TABLE property_types (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL UNIQUE,
    display_name VARCHAR(100) NOT NULL,
    category ENUM('residential', 'commercial', 'industrial', 'mixed_use') NOT NULL,
    icon VARCHAR(50),
    sort_order INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE
);

INSERT INTO property_types (name, display_name, category) VALUES
('apartment', 'Apartment', 'residential'),
('villa', 'Villa', 'residential'),
('townhouse', 'Townhouse', 'residential'),
('penthouse', 'Penthouse', 'residential'),
('studio', 'Studio', 'residential'),
('office', 'Office', 'commercial'),
('retail', 'Retail/Shop', 'commercial'),
('warehouse', 'Warehouse', 'industrial'),
('land', 'Land/Plot', 'mixed_use'),
('building', 'Full Building', 'commercial');

CREATE TABLE amenities (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    category VARCHAR(50) NOT NULL, -- security, leisure, convenience, etc.
    icon VARCHAR(50),
    is_active BOOLEAN DEFAULT TRUE,
    sort_order INT DEFAULT 0
);

INSERT INTO amenities (name, category) VALUES
('Security', 'security'),
('CCTV', 'security'),
('Swimming Pool', 'leisure'),
('Gym', 'leisure'),
('Parking', 'convenience'),
('Balcony', 'convenience'),
('Central AC', 'convenience'),
('Maid Room', 'convenience'),
('Study Room', 'convenience'),
('Pets Allowed', 'policy'),
('Concierge', 'services'),
('Elevator', 'convenience');

CREATE TABLE locations (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) NOT NULL UNIQUE,
    parent_id INT NULL,
    location_type ENUM('city', 'area', 'sub_community', 'building') NOT NULL,
    
    -- Dubai Specific
    emirate VARCHAR(50) DEFAULT 'Dubai',
    
    -- Coordinates
    latitude DECIMAL(10, 8),
    longitude DECIMAL(11, 8),
    
    -- Metadata
    description TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    sort_order INT DEFAULT 0,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (parent_id) REFERENCES locations(id),
    INDEX idx_locations_type (location_type),
    INDEX idx_locations_parent (parent_id)
);

-- =============================================
-- PROJECT MASTER (Developer Projects)
-- =============================================

CREATE TABLE projects (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(200) NOT NULL,
    slug VARCHAR(200) NOT NULL UNIQUE,
    description TEXT,
    
    -- Developer Reference
    developer_id BIGINT NOT NULL,
    
    -- Project Details
    project_type ENUM('residential', 'commercial', 'mixed_use', 'hospitality') NOT NULL,
    total_units INT,
    total_floors INT,
    launch_date DATE,
    completion_date DATE,
    handover_date DATE,
    
    -- Completion Status (Dubai Specific)
    completion_status ENUM('off_plan', 'under_construction', 'ready', 'handed_over') NOT NULL,
    construction_progress DECIMAL(5,2) DEFAULT 0.00, -- Percentage
    
    -- Location
    location_id INT NOT NULL,
    address TEXT,
    latitude DECIMAL(10, 8),
    longitude DECIMAL(11, 8),
    
    -- Pricing Range
    min_price DECIMAL(15, 2),
    max_price DECIMAL(15, 2),
    currency VARCHAR(3) DEFAULT 'AED',
    
    -- Media
    cover_image_url VARCHAR(500),
    brochure_url VARCHAR(500),
    floor_plan_url VARCHAR(500),
    video_tour_url VARCHAR(500),
    
    -- Amenities (JSON array of amenity IDs)
    amenities_json JSON,
    
    -- RERA
    rera_permit_number VARCHAR(50),
    escrow_account_number VARCHAR(100),
    
    -- Status
    status ENUM('draft', 'active', 'sold_out', 'inactive') DEFAULT 'draft',
    is_featured BOOLEAN DEFAULT FALSE,
    
    -- SEO
    meta_title VARCHAR(200),
    meta_description TEXT,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    
    FOREIGN KEY (developer_id) REFERENCES developers(id),
    FOREIGN KEY (location_id) REFERENCES locations(id),
    INDEX idx_projects_developer (developer_id),
    INDEX idx_projects_location (location_id),
    INDEX idx_projects_status (status),
    INDEX idx_projects_completion (completion_status),
    INDEX idx_projects_featured (is_featured, status),
    FULLTEXT INDEX idx_projects_search (name, description)
);

-- =============================================
-- UNITS (Individual Properties)
-- =============================================

CREATE TABLE units (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    
    -- Unit Identification
    unit_number VARCHAR(50),
    title VARCHAR(200) NOT NULL,
    slug VARCHAR(200) NOT NULL UNIQUE,
    description TEXT,
    
    -- Project Reference (NULL for standalone properties)
    project_id BIGINT NULL,
    
    -- Ownership
    listing_type ENUM('sale', 'rent') NOT NULL,
    owner_id BIGINT, -- For direct owner listings
    agent_id BIGINT NOT NULL, -- Primary listing agent
    agency_id BIGINT, -- Agent's agency
    developer_id BIGINT, -- For developer direct listings
    
    -- Property Details
    property_type_id INT NOT NULL,
    bedrooms INT, -- NULL for studios
    bathrooms DECIMAL(3,1),
    total_rooms INT,
    area_sqft DECIMAL(10,2),
    area_sqm DECIMAL(10,2),
    plot_area_sqft DECIMAL(10,2),
    floor_number INT,
    total_floors INT,
    parking_spots INT DEFAULT 0,
    
    -- Dubai Specific - Completion
    completion_status ENUM('off_plan', 'under_construction', 'ready') NOT NULL,
    completion_date DATE,
    handover_date DATE,
    construction_progress DECIMAL(5,2) DEFAULT 0.00,
    
    -- Pricing (AED)
    price DECIMAL(15, 2) NOT NULL,
    price_per_sqft DECIMAL(10, 2),
    original_price DECIMAL(15, 2), -- For price drops
    maintenance_fee DECIMAL(10, 2),
    currency VARCHAR(3) DEFAULT 'AED',
    
    -- Payment Plan (for off-plan)
    payment_plan_available BOOLEAN DEFAULT FALSE,
    down_payment_percent DECIMAL(5,2),
    post_handover_payment BOOLEAN DEFAULT FALSE,
    
    -- Location
    location_id INT NOT NULL,
    address TEXT,
    latitude DECIMAL(10, 8),
    longitude DECIMAL(11, 8),
    
    -- Views & Features
    view_type JSON, -- ["sea", "city", "garden"]
    furnished ENUM('unfurnished', 'semi_furnished', 'fully_furnished'),
    
    -- Media (JSON arrays)
    images_json JSON, -- [{url, order, is_featured}]
    floor_plans_json JSON,
    videos_json JSON,
    virtual_tour_url VARCHAR(500),
    
    -- Amenities
    amenities_json JSON,
    
    -- RERA Compliance
    rera_permit_number VARCHAR(50),
    rera_verified_at TIMESTAMP NULL,
    
    -- Listing Settings
    is_active BOOLEAN DEFAULT TRUE,
    is_featured BOOLEAN DEFAULT FALSE,
    is_premium BOOLEAN DEFAULT FALSE,
    listing_expires_at TIMESTAMP NULL,
    
    -- Statistics
    view_count INT DEFAULT 0,
    inquiry_count INT DEFAULT 0,
    favorite_count INT DEFAULT 0,
    
    -- SEO
    meta_title VARCHAR(200),
    meta_description TEXT,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    
    FOREIGN KEY (project_id) REFERENCES projects(id),
    FOREIGN KEY (property_type_id) REFERENCES property_types(id),
    FOREIGN KEY (agent_id) REFERENCES users(id),
    FOREIGN KEY (agency_id) REFERENCES agencies(id),
    FOREIGN KEY (developer_id) REFERENCES developers(id),
    FOREIGN KEY (location_id) REFERENCES locations(id),
    FOREIGN KEY (owner_id) REFERENCES users(id),
    
    INDEX idx_units_project (project_id),
    INDEX idx_units_agent (agent_id),
    INDEX idx_units_agency (agency_id),
    INDEX idx_units_location (location_id),
    INDEX idx_units_type (property_type_id),
    INDEX idx_units_listing (listing_type, is_active),
    INDEX idx_units_completion (completion_status),
    INDEX idx_units_price (price),
    INDEX idx_units_bedrooms (bedrooms),
    INDEX idx_units_search (is_active, listing_type, completion_status),
    FULLTEXT INDEX idx_units_ft (title, description)
);

-- =============================================
-- SUBSCRIPTION MANAGEMENT
-- =============================================

CREATE TABLE subscription_plans (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    
    -- Target Audience
    target_role ENUM('agency', 'agent', 'developer') NOT NULL,
    
    -- Pricing
    price_monthly DECIMAL(10, 2) NOT NULL,
    price_annually DECIMAL(10, 2) NOT NULL,
    currency VARCHAR(3) DEFAULT 'AED',
    
    -- Limits
    max_listings INT NOT NULL,
    max_featured_listings INT DEFAULT 0,
    max_agents INT DEFAULT 1, -- For agency plans
    max_projects INT DEFAULT 0, -- For developer plans
    max_webinars_per_month INT DEFAULT 0,
    
    -- Features (JSON flags)
    features_json JSON,
    
    -- Status
    is_active BOOLEAN DEFAULT TRUE,
    is_popular BOOLEAN DEFAULT FALSE,
    sort_order INT DEFAULT 0,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_plans_role (target_role, is_active)
);

-- Insert default plans
INSERT INTO subscription_plans (name, slug, target_role, price_monthly, price_annually, max_listings, max_featured_listings, max_agents, max_projects, max_webinars_per_month, features_json) VALUES
('Free Agent', 'free-agent', 'agent', 0, 0, 3, 0, 1, 0, 0, '{"analytics": false, "lead_management": true, "priority_support": false}'),
('Pro Agent', 'pro-agent', 'agent', 199, 1990, 20, 5, 1, 0, 0, '{"analytics": true, "lead_management": true, "priority_support": true, "verified_badge": true}'),
('Elite Agent', 'elite-agent', 'agent', 499, 4990, 100, 20, 1, 0, 2, '{"analytics": true, "lead_management": true, "priority_support": true, "verified_badge": true, "webime_hosting": true}'),
('Startup Agency', 'startup-agency', 'agency', 999, 9990, 100, 20, 10, 0, 5, '{"analytics": true, "team_management": true, "lead_routing": true, "priority_support": true}'),
('Growth Agency', 'growth-agency', 'agency', 2499, 24990, 500, 100, 50, 0, 20, '{"analytics": true, "team_management": true, "lead_routing": true, "priority_support": true, "api_access": true, "webime_hosting": true}'),
('Enterprise Agency', 'enterprise-agency', 'agency', 4999, 49990, 999999, 500, 200, 0, 100, '{"analytics": true, "team_management": true, "lead_routing": true, "priority_support": true, "api_access": true, "webime_hosting": true, "dedicated_manager": true}'),
('Developer Basic', 'developer-basic', 'developer', 4999, 49990, 0, 0, 0, 5, 10, '{"analytics": true, "project_management": true, "lead_capture": true, "webime_hosting": true}'),
('Developer Pro', 'developer-pro', 'developer', 9999, 99990, 0, 0, 0, 20, 50, '{"analytics": true, "project_management": true, "lead_capture": true, "webime_hosting": true, "priority_placement": true, "dedicated_support": true}');

CREATE TABLE subscriptions (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    
    -- Subscriber
    subscriber_type ENUM('agency', 'agent', 'developer') NOT NULL,
    subscriber_id BIGINT NOT NULL, -- agency_id, agent_id, or developer_id
    
    -- Plan
    plan_id INT NOT NULL,
    
    -- Billing
    billing_cycle ENUM('monthly', 'annual') NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    currency VARCHAR(3) DEFAULT 'AED',
    
    -- Stripe Integration
    stripe_customer_id VARCHAR(100),
    stripe_subscription_id VARCHAR(100),
    stripe_price_id VARCHAR(100),
    
    -- Status
    status ENUM('trialing', 'active', 'past_due', 'canceled', 'paused', 'expired') DEFAULT 'trialing',
    trial_ends_at TIMESTAMP NULL,
    current_period_starts_at TIMESTAMP NOT NULL,
    current_period_ends_at TIMESTAMP NOT NULL,
    canceled_at TIMESTAMP NULL,
    cancellation_reason TEXT,
    
    -- Usage Tracking
    listings_used INT DEFAULT 0,
    featured_listings_used INT DEFAULT 0,
    webinars_used_this_period INT DEFAULT 0,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (plan_id) REFERENCES subscription_plans(id),
    INDEX idx_subscriptions_subscriber (subscriber_type, subscriber_id),
    INDEX idx_subscriptions_status (status),
    INDEX idx_subscriptions_stripe (stripe_subscription_id)
);

CREATE TABLE subscription_invoices (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    subscription_id BIGINT NOT NULL,
    
    -- Invoice Details
    invoice_number VARCHAR(100) NOT NULL UNIQUE,
    stripe_invoice_id VARCHAR(100),
    
    -- Amounts
    subtotal DECIMAL(10, 2) NOT NULL,
    tax_amount DECIMAL(10, 2) DEFAULT 0.00,
    discount_amount DECIMAL(10, 2) DEFAULT 0.00,
    total_amount DECIMAL(10, 2) NOT NULL,
    currency VARCHAR(3) DEFAULT 'AED',
    
    -- Status
    status ENUM('draft', 'open', 'paid', 'uncollectible', 'void') DEFAULT 'draft',
    paid_at TIMESTAMP NULL,
    
    -- PDF
    invoice_pdf_url VARCHAR(500),
    
    period_starts_at TIMESTAMP NOT NULL,
    period_ends_at TIMESTAMP NOT NULL,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (subscription_id) REFERENCES subscriptions(id),
    INDEX idx_invoices_subscription (subscription_id),
    INDEX idx_invoices_status (status)
);

-- =============================================
-- WEBIME (VIRTUAL LAUNCH/WEBINAR)
-- =============================================

CREATE TABLE webinars (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    
    -- Host
    host_type ENUM('developer', 'agency') NOT NULL,
    host_id BIGINT NOT NULL, -- developer_id or agency_id
    host_user_id BIGINT NOT NULL, -- Actual user hosting
    
    -- Event Details
    title VARCHAR(200) NOT NULL,
    description TEXT,
    
    -- Schedule
    scheduled_at TIMESTAMP NOT NULL,
    duration_minutes INT DEFAULT 60,
    timezone VARCHAR(50) DEFAULT 'Asia/Dubai',
    
    -- Status
    status ENUM('scheduled', 'live', 'ended', 'cancelled') DEFAULT 'scheduled',
    
    -- Access
    is_public BOOLEAN DEFAULT TRUE,
    require_registration BOOLEAN DEFAULT TRUE,
    max_attendees INT DEFAULT 1000,
    
    -- Meeting Details
    meeting_link VARCHAR(500),
    meeting_password VARCHAR(50),
    recording_url VARCHAR(500),
    
    -- Statistics
    total_registrations INT DEFAULT 0,
    total_attended INT DEFAULT 0,
    peak_concurrent_viewers INT DEFAULT 0,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (host_user_id) REFERENCES users(id),
    INDEX idx_webinars_host (host_type, host_id),
    INDEX idx_webinars_schedule (scheduled_at),
    INDEX idx_webinars_status (status)
);

CREATE TABLE webinar_properties (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    webinar_id BIGINT NOT NULL,
    unit_id BIGINT NOT NULL,
    presentation_order INT DEFAULT 0,
    highlight_features JSON,
    special_offer_text TEXT,
    
    FOREIGN KEY (webinar_id) REFERENCES webinars(id) ON DELETE CASCADE,
    FOREIGN KEY (unit_id) REFERENCES units(id),
    UNIQUE KEY unique_webinar_unit (webinar_id, unit_id)
);

CREATE TABLE webinar_registrations (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    webinar_id BIGINT NOT NULL,
    customer_id BIGINT NOT NULL,
    
    -- Registration Details
    registered_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    attended_at TIMESTAMP NULL,
    left_at TIMESTAMP NULL,
    
    -- Reminders
    reminder_email_sent BOOLEAN DEFAULT FALSE,
    reminder_sms_sent BOOLEAN DEFAULT FALSE,
    
    -- Feedback
    rating INT NULL, -- 1-5
    feedback TEXT,
    
    -- Lead Conversion
    converted_to_lead_id BIGINT NULL,
    
    FOREIGN KEY (webinar_id) REFERENCES webinars(id) ON DELETE CASCADE,
    FOREIGN KEY (customer_id) REFERENCES users(id),
    FOREIGN KEY (converted_to_lead_id) REFERENCES leads(id),
    UNIQUE KEY unique_webinar_customer (webinar_id, customer_id),
    INDEX idx_registrations_webinar (webinar_id)
);

-- =============================================
-- LEAD MANAGEMENT
-- =============================================

CREATE TABLE leads (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    
    -- Source
    source_type ENUM('direct_inquiry', 'webinar', 'phone_call', 'whatsapp', 'website', 'property_portal', 'referral', 'walk_in') NOT NULL,
    source_details VARCHAR(255),
    webinar_id BIGINT NULL,
    
    -- Customer Info
    customer_id BIGINT NULL, -- Registered user
    customer_name VARCHAR(200) NOT NULL,
    customer_email VARCHAR(255) NOT NULL,
    customer_phone VARCHAR(20) NOT NULL,
    
    -- Interested Property
    unit_id BIGINT NULL,
    project_id BIGINT NULL,
    
    -- Assignment
    assigned_to_type ENUM('agent', 'agency') NOT NULL,
    assigned_to_agent_id BIGINT,
    assigned_to_agency_id BIGINT,
    assigned_by BIGINT,
    assigned_at TIMESTAMP NULL,
    
    -- Status Pipeline
    status ENUM('new', 'contacted', 'qualified', 'viewing_scheduled', 'negotiating', 'offer_made', 'closed_won', 'closed_lost', 'unresponsive') DEFAULT 'new',
    priority ENUM('low', 'medium', 'high', 'urgent') DEFAULT 'medium',
    
    -- Requirements
    budget_min DECIMAL(15, 2),
    budget_max DECIMAL(15, 2),
    preferred_location_ids JSON,
    preferred_property_types JSON,
    bedrooms_preferred INT,
    notes TEXT,
    
    -- Follow-up
    next_follow_up_at TIMESTAMP NULL,
    last_contact_at TIMESTAMP NULL,
    
    -- Outcome
    closed_at TIMESTAMP NULL,
    closed_reason TEXT,
    deal_value DECIMAL(15, 2),
    commission_earned DECIMAL(15, 2),
    
    -- Timestamps
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (customer_id) REFERENCES users(id),
    FOREIGN KEY (unit_id) REFERENCES units(id),
    FOREIGN KEY (project_id) REFERENCES projects(id),
    FOREIGN KEY (assigned_to_agent_id) REFERENCES users(id),
    FOREIGN KEY (assigned_to_agency_id) REFERENCES agencies(id),
    FOREIGN KEY (assigned_by) REFERENCES users(id),
    FOREIGN KEY (webinar_id) REFERENCES webinars(id),
    
    INDEX idx_leads_customer (customer_id),
    INDEX idx_leads_agent (assigned_to_agent_id),
    INDEX idx_leads_agency (assigned_to_agency_id),
    INDEX idx_leads_status (status),
    INDEX idx_leads_created (created_at),
    INDEX idx_leads_follow_up (next_follow_up_at)
-- NOTE: Partitioning removed — InnoDB does not allow FOREIGN KEY on partitioned tables (MySQL ERROR 1506).
-- Partition by range can be re-added in production where FK constraints are managed at the application layer.
);

CREATE TABLE lead_activities (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    lead_id BIGINT NOT NULL,
    
    activity_type ENUM('call_made', 'call_received', 'email_sent', 'email_received', 'sms_sent', 'meeting_scheduled', 'viewing_scheduled', 'viewing_completed', 'offer_made', 'note_added', 'status_changed') NOT NULL,
    
    -- Details
    description TEXT,
    outcome VARCHAR(255),
    duration_minutes INT,
    
    -- Related Records
    related_unit_id BIGINT,
    
    -- User
    performed_by BIGINT NOT NULL,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (lead_id) REFERENCES leads(id) ON DELETE CASCADE,
    FOREIGN KEY (performed_by) REFERENCES users(id),
    FOREIGN KEY (related_unit_id) REFERENCES units(id),
    INDEX idx_activities_lead (lead_id),
    INDEX idx_activities_created (created_at)
);

-- =============================================
-- FAVORITES & INQUIRIES (Customer Actions)
-- =============================================

CREATE TABLE favorites (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    customer_id BIGINT NOT NULL,
    unit_id BIGINT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (customer_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (unit_id) REFERENCES units(id) ON DELETE CASCADE,
    UNIQUE KEY unique_customer_unit (customer_id, unit_id),
    INDEX idx_favorites_customer (customer_id)
);

CREATE TABLE property_inquiries (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    
    customer_id BIGINT NOT NULL,
    unit_id BIGINT NOT NULL,
    
    -- Inquiry Details
    message TEXT,
    inquiry_type ENUM('more_info', 'schedule_viewing', 'price_quote', 'availability') DEFAULT 'more_info',
    preferred_contact_method ENUM('email', 'phone', 'whatsapp') DEFAULT 'email',
    preferred_contact_time VARCHAR(50),
    
    -- Status
    status ENUM('pending', 'responded', 'converted_to_lead', 'closed') DEFAULT 'pending',
    responded_at TIMESTAMP NULL,
    responded_by BIGINT,
    
    -- Converted Lead
    converted_lead_id BIGINT,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (customer_id) REFERENCES users(id),
    FOREIGN KEY (unit_id) REFERENCES units(id),
    FOREIGN KEY (responded_by) REFERENCES users(id),
    FOREIGN KEY (converted_lead_id) REFERENCES leads(id),
    INDEX idx_inquiries_customer (customer_id),
    INDEX idx_inquiries_unit (unit_id),
    INDEX idx_inquiries_status (status)
);

-- =============================================
-- AUDIT & SYSTEM
-- =============================================

CREATE TABLE audit_logs (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    
    user_id BIGINT,
    user_type VARCHAR(50),
    
    action VARCHAR(100) NOT NULL,
    entity_type VARCHAR(50) NOT NULL,
    entity_id BIGINT,
    
    old_values JSON,
    new_values JSON,
    
    ip_address VARCHAR(45),
    user_agent TEXT,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_audit_user (user_id),
    INDEX idx_audit_entity (entity_type, entity_id),
    INDEX idx_audit_created (created_at)
-- NOTE: Partitioning removed — YEAR() on TIMESTAMP is timezone-dependent and rejected by MySQL (ERROR 1486).
-- In production, consider using a DATE column or UNIX_TIMESTAMP() for partitioning.
);

CREATE TABLE notifications (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    
    user_id BIGINT NOT NULL,
    
    type ENUM('lead_assigned', 'inquiry_received', 'webinar_reminder', 'subscription_expiring', 'system') NOT NULL,
    title VARCHAR(200) NOT NULL,
    message TEXT,
    
    -- Action
    action_url VARCHAR(500),
    action_text VARCHAR(100),
    
    -- Status
    is_read BOOLEAN DEFAULT FALSE,
    read_at TIMESTAMP NULL,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_notifications_user (user_id, is_read),
    INDEX idx_notifications_created (created_at)
);

-- =============================================
-- VIEWS & ANALYTICS
-- =============================================

CREATE TABLE unit_views (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    unit_id BIGINT NOT NULL,
    viewer_id BIGINT, -- NULL for anonymous
    ip_address VARCHAR(45),
    user_agent TEXT,
    referrer VARCHAR(500),
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (unit_id) REFERENCES units(id) ON DELETE CASCADE,
    INDEX idx_unit_views_unit (unit_id),
    INDEX idx_unit_views_created (created_at)
-- NOTE: Partitioning removed — InnoDB FK + YEAR(TIMESTAMP) partition both unsupported (ERROR 1486/1506).
);

-- =============================================
-- STORED PROCEDURES & FUNCTIONS
-- =============================================

DELIMITER //

-- Function to format AED currency
CREATE FUNCTION FORMAT_AED(amount DECIMAL(15,2)) 
RETURNS VARCHAR(100)
DETERMINISTIC
BEGIN
    RETURN CONCAT('AED ', FORMAT(amount, 0));
END //

-- Procedure to auto-assign lead to agent
CREATE PROCEDURE AutoAssignLead(
    IN p_lead_id BIGINT,
    IN p_agency_id BIGINT
)
BEGIN
    DECLARE v_agent_id BIGINT;
    
    -- Get least busy active agent using round-robin logic
    SELECT aa.agent_id INTO v_agent_id
    FROM agency_agents aa
    LEFT JOIN leads l ON l.assigned_to_agent_id = aa.agent_id 
        AND l.status NOT IN ('closed_won', 'closed_lost')
    WHERE aa.agency_id = p_agency_id
        AND aa.is_active = TRUE
    GROUP BY aa.agent_id
    ORDER BY COUNT(l.id) ASC, aa.joined_at ASC
    LIMIT 1;
    
    IF v_agent_id IS NOT NULL THEN
        UPDATE leads 
        SET assigned_to_agent_id = v_agent_id,
            assigned_to_agency_id = p_agency_id,
            assigned_at = NOW()
        WHERE id = p_lead_id;
    END IF;
END //

-- Procedure to update unit statistics
CREATE PROCEDURE UpdateUnitStats(IN p_unit_id BIGINT)
BEGIN
    UPDATE units u
    SET view_count = (SELECT COUNT(*) FROM unit_views WHERE unit_id = p_unit_id),
        inquiry_count = (SELECT COUNT(*) FROM property_inquiries WHERE unit_id = p_unit_id),
        favorite_count = (SELECT COUNT(*) FROM favorites WHERE unit_id = p_unit_id)
    WHERE u.id = p_unit_id;
END //

DELIMITER ;