-- StaySpot Unified Database Schema
-- Combined from both provided schemas with enhancements
-- Version: 2.0.0



-- Create database
CREATE DATABASE IF NOT EXISTS stayspot;
USE stayspot;

-- ==================== ENUMERATION TABLES ====================

CREATE TABLE user_roles (
    role_code VARCHAR(50) PRIMARY KEY,
    role_name VARCHAR(100) NOT NULL,
    description TEXT,
    permissions JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE property_types (
    type_code VARCHAR(50) PRIMARY KEY,
    type_name VARCHAR(100) NOT NULL,
    description TEXT,
    icon_class VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE property_statuses (
    status_code VARCHAR(50) PRIMARY KEY,
    status_name VARCHAR(100) NOT NULL,
    description TEXT,
    color_class VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE maintenance_priorities (
    priority_code VARCHAR(20) PRIMARY KEY,
    priority_name VARCHAR(50) NOT NULL,
    description TEXT,
    sla_hours INT,
    color_class VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE maintenance_statuses (
    status_code VARCHAR(50) PRIMARY KEY,
    status_name VARCHAR(100) NOT NULL,
    description TEXT,
    color_class VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE lease_statuses (
    status_code VARCHAR(50) PRIMARY KEY,
    status_name VARCHAR(100) NOT NULL,
    description TEXT,
    color_class VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE payment_statuses (
    status_code VARCHAR(50) PRIMARY KEY,
    status_name VARCHAR(100) NOT NULL,
    description TEXT,
    color_class VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE service_categories (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    category_code VARCHAR(50) NOT NULL UNIQUE,
    category_name VARCHAR(100) NOT NULL,
    description TEXT,
    parent_category_code VARCHAR(50) NULL,
    icon_class VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_parent_category (parent_category_code),
    FOREIGN KEY (parent_category_code) REFERENCES service_categories(category_code)
);

-- ==================== PROPERTY MANAGEMENT ====================

CREATE TABLE properties (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    uuid CHAR(36) NOT NULL UNIQUE DEFAULT (UUID()),
    
    -- Basic Information
    title VARCHAR(255) NOT NULL,
    description TEXT,
    property_type_code VARCHAR(50) NOT NULL,
    status_code VARCHAR(50) NOT NULL,
    
    -- Location Details
    address_street VARCHAR(255) NOT NULL,
    address_city VARCHAR(100) NOT NULL,
    address_state VARCHAR(100) NOT NULL,
    address_zip_code VARCHAR(20) NOT NULL,
    address_country VARCHAR(100) DEFAULT 'US',
    latitude DECIMAL(10,8),
    longitude DECIMAL(11,8),
    
    -- Property Specifications
    bedrooms INT NOT NULL DEFAULT 0,
    bathrooms DECIMAL(3,1) NOT NULL DEFAULT 0,
    area_sqft INT,
    year_built YEAR,
    lot_size_sqft INT,
    
    -- Rental Information
    monthly_rent DECIMAL(10,2) NOT NULL,
    security_deposit DECIMAL(10,2),
    available_from DATE,
    is_published BOOLEAN DEFAULT FALSE,
    
    -- Features & Amenities (JSON for flexibility - combined from both)
    amenities JSON,
    interior_features JSON,
    kitchen_amenities JSON,
    bathroom_features JSON,
    outdoor_amenities JSON,
    smart_home_features JSON,
    accessibility_features JSON,
    utility_information JSON,
    rules JSON,
    features JSON,
    
    -- Media
    main_image_url VARCHAR(500),
    images JSON,
    
    -- Ownership & Management
    owner_id BIGINT NOT NULL,
    property_manager_id BIGINT NULL,
    primary_agent_id BIGINT NULL,
    
    -- Soft Delete
    deleted_at DATETIME NULL,
    
    -- Audit Fields
    created_by BIGINT NOT NULL,
    updated_by BIGINT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Indexes
    INDEX idx_owner (owner_id),
    INDEX idx_manager (property_manager_id),
    INDEX idx_agent (primary_agent_id),
    INDEX idx_location (address_city, address_state),
    INDEX idx_rent (monthly_rent),
    INDEX idx_bedrooms (bedrooms),
    INDEX idx_status (status_code),
    INDEX idx_available (available_from),
    INDEX idx_deleted (deleted_at),
    INDEX idx_published (is_published),
    INDEX idx_properties_search (address_city, monthly_rent, bedrooms, status_code),
    FULLTEXT idx_search (title, description, address_street),
    
    -- Foreign Keys
    FOREIGN KEY (property_type_code) REFERENCES property_types(type_code),
    FOREIGN KEY (status_code) REFERENCES property_statuses(status_code),
    FOREIGN KEY (owner_id) REFERENCES users(id),
    FOREIGN KEY (property_manager_id) REFERENCES users(id),
    FOREIGN KEY (primary_agent_id) REFERENCES users(id),
    FOREIGN KEY (created_by) REFERENCES users(id),
    FOREIGN KEY (updated_by) REFERENCES users(id)
);

CREATE TABLE property_images (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    property_id BIGINT NOT NULL,
    image_url VARCHAR(500) NOT NULL,
    image_order INT DEFAULT 0,
    caption VARCHAR(255),
    is_primary BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_property (property_id),
    INDEX idx_order (image_order),
    FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE
);

CREATE TABLE property_documents (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    property_id BIGINT NOT NULL,
    document_name VARCHAR(255) NOT NULL,
    document_url VARCHAR(500) NOT NULL,
    document_type ENUM(
        'lease_agreement',
        'id_proof',
        'income_proof',
        'property_document',
        'maintenance_report',
        'inspection_report',
        'insurance_document',
        'other'
    ) NOT NULL,
    category VARCHAR(100),
    file_size BIGINT,
    mime_type VARCHAR(100),
    tags JSON,
    is_public BOOLEAN DEFAULT FALSE,
    expires_at DATETIME,
    uploaded_by BIGINT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_property (property_id),
    INDEX idx_type (document_type),
    INDEX idx_uploaded_by (uploaded_by),
    FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE,
    FOREIGN KEY (uploaded_by) REFERENCES users(id)
);

-- ==================== LEASE MANAGEMENT ====================

CREATE TABLE leases (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    uuid CHAR(36) NOT NULL UNIQUE DEFAULT (UUID()),
    
    -- Lease Information
    property_id BIGINT NOT NULL,
    tenant_id BIGINT NOT NULL,
    landlord_id BIGINT NOT NULL,
    lease_number VARCHAR(100) NOT NULL UNIQUE,
    
    -- Term Details
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    monthly_rent DECIMAL(10,2) NOT NULL,
    security_deposit DECIMAL(10,2) NOT NULL,
    
    -- Occupants
    primary_tenant_id BIGINT NOT NULL,
    occupants_count INT DEFAULT 1,
    occupants_details JSON,
    
    -- Lease Status
    status_code VARCHAR(50) NOT NULL DEFAULT 'pending',
    
    -- Documents
    lease_document_url VARCHAR(500),
    
    -- Financial
    rent_due_day INT DEFAULT 1,
    late_fee_percentage DECIMAL(5,2) DEFAULT 5.0,
    grace_period_days INT DEFAULT 5,
    
    -- Special Terms
    special_terms TEXT,
    terms JSON,
    
    -- Dates
    signed_at DATETIME,
    terminated_at DATETIME,
    
    -- Soft Delete
    deleted_at DATETIME NULL,
    
    -- Audit Fields
    created_by BIGINT NOT NULL,
    updated_by BIGINT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Indexes
    INDEX idx_property (property_id),
    INDEX idx_tenant (tenant_id),
    INDEX idx_landlord (landlord_id),
    INDEX idx_primary_tenant (primary_tenant_id),
    INDEX idx_status (status_code),
    INDEX idx_dates (start_date, end_date),
    INDEX idx_lease_number (lease_number),
    INDEX idx_deleted (deleted_at),
    
    -- Foreign Keys
    FOREIGN KEY (property_id) REFERENCES properties(id),
    FOREIGN KEY (tenant_id) REFERENCES users(id),
    FOREIGN KEY (landlord_id) REFERENCES users(id),
    FOREIGN KEY (primary_tenant_id) REFERENCES users(id),
    FOREIGN KEY (status_code) REFERENCES lease_statuses(status_code),
    FOREIGN KEY (created_by) REFERENCES users(id),
    FOREIGN KEY (updated_by) REFERENCES users(id)
);

-- ==================== PAYMENT MANAGEMENT ====================

CREATE TABLE payments (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    uuid CHAR(36) NOT NULL UNIQUE DEFAULT (UUID()),
    
    -- Payment Details
    lease_id BIGINT NOT NULL,
    tenant_id BIGINT NOT NULL,
    landlord_id BIGINT NOT NULL,
    property_id BIGINT NOT NULL,
    
    -- Amount Information
    amount_due DECIMAL(10,2) NOT NULL,
    amount_paid DECIMAL(10,2) DEFAULT 0,
    late_fee_amount DECIMAL(10,2) DEFAULT 0,
    
    -- Period Information
    period_month INT NOT NULL,
    period_year INT NOT NULL,
    due_date DATE NOT NULL,
    paid_date DATETIME,
    
    -- Payment Method
    payment_method ENUM('credit_card', 'debit_card', 'bank_transfer', 'cash', 'check', 'online', 'paypal', 'stripe'),
    transaction_id VARCHAR(255),
    payment_gateway VARCHAR(100),
    receipt_url VARCHAR(500),
    
    -- Status
    status_code VARCHAR(50) NOT NULL DEFAULT 'pending',
    
    -- Description
    description TEXT,
    
    -- Audit Fields
    created_by BIGINT NOT NULL,
    updated_by BIGINT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Indexes
    INDEX idx_lease (lease_id),
    INDEX idx_tenant (tenant_id),
    INDEX idx_landlord (landlord_id),
    INDEX idx_property (property_id),
    INDEX idx_period (period_year, period_month),
    INDEX idx_due_date (due_date),
    INDEX idx_paid_date (paid_date),
    INDEX idx_status (status_code),
    INDEX idx_transaction (transaction_id),
    INDEX idx_payments_period (period_year, period_month, status_code),
    
    -- Foreign Keys
    FOREIGN KEY (lease_id) REFERENCES leases(id),
    FOREIGN KEY (tenant_id) REFERENCES users(id),
    FOREIGN KEY (landlord_id) REFERENCES users(id),
    FOREIGN KEY (property_id) REFERENCES properties(id),
    FOREIGN KEY (status_code) REFERENCES payment_statuses(status_code),
    FOREIGN KEY (created_by) REFERENCES users(id),
    FOREIGN KEY (updated_by) REFERENCES users(id)
);

CREATE TABLE expenses (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    uuid CHAR(36) NOT NULL UNIQUE DEFAULT (UUID()),
    
    -- Expense Details
    property_id BIGINT NOT NULL,
    category VARCHAR(100) NOT NULL,
    description TEXT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    
    -- Vendor Information
    vendor_name VARCHAR(255),
    vendor_contact VARCHAR(255),
    
    -- Timing
    expense_date DATE NOT NULL,
    due_date DATE,
    paid_date DATETIME,
    
    -- Recurring Expenses
    is_recurring BOOLEAN DEFAULT FALSE,
    recurrence_pattern VARCHAR(50),
    
    -- Documentation
    receipt_url VARCHAR(500),
    
    -- Audit Fields
    created_by BIGINT NOT NULL,
    updated_by BIGINT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Indexes
    INDEX idx_property (property_id),
    INDEX idx_category (category),
    INDEX idx_date (expense_date),
    INDEX idx_vendor (vendor_name),
    
    -- Foreign Keys
    FOREIGN KEY (property_id) REFERENCES properties(id),
    FOREIGN KEY (created_by) REFERENCES users(id),
    FOREIGN KEY (updated_by) REFERENCES users(id)
);

-- ==================== MAINTENANCE MANAGEMENT ====================

CREATE TABLE maintenance_requests (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    uuid CHAR(36) NOT NULL UNIQUE DEFAULT (UUID()),
    
    -- Request Details
    property_id BIGINT NOT NULL,
    unit_id VARCHAR(100),
    requested_by BIGINT NOT NULL,
    assigned_to BIGINT NULL,
    landlord_id BIGINT NOT NULL,
    
    -- Issue Information
    title VARCHAR(255) NOT NULL,
    description TEXT NOT NULL,
    category ENUM('plumbing', 'electrical', 'hvac', 'appliance', 'structural', 'cosmetic', 'pest_control', 'other') NOT NULL DEFAULT 'other',
    priority_code VARCHAR(20) NOT NULL DEFAULT 'normal',
    
    -- Status & Tracking
    status_code VARCHAR(50) NOT NULL DEFAULT 'submitted',
    estimated_cost DECIMAL(10,2),
    actual_cost DECIMAL(10,2),
    rating INT,
    
    -- Media
    images JSON,
    
    -- Scheduling
    requested_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    scheduled_date DATETIME,
    completed_date DATETIME,
    
    -- Location Access
    access_instructions TEXT,
    tenant_available BOOLEAN DEFAULT FALSE,
    notes TEXT,
    
    -- Audit Fields
    created_by BIGINT NOT NULL,
    updated_by BIGINT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Indexes
    INDEX idx_property (property_id),
    INDEX idx_requested_by (requested_by),
    INDEX idx_assigned_to (assigned_to),
    INDEX idx_landlord (landlord_id),
    INDEX idx_priority (priority_code),
    INDEX idx_status (status_code),
    INDEX idx_category (category),
    INDEX idx_requested_date (requested_date),
    INDEX idx_scheduled_date (scheduled_date),
    INDEX idx_maintenance_priority (priority_code, status_code, requested_date),
    
    -- Foreign Keys
    FOREIGN KEY (property_id) REFERENCES properties(id),
    FOREIGN KEY (requested_by) REFERENCES users(id),
    FOREIGN KEY (assigned_to) REFERENCES users(id),
    FOREIGN KEY (landlord_id) REFERENCES users(id),
    FOREIGN KEY (priority_code) REFERENCES maintenance_priorities(priority_code),
    FOREIGN KEY (status_code) REFERENCES maintenance_statuses(status_code),
    FOREIGN KEY (created_by) REFERENCES users(id),
    FOREIGN KEY (updated_by) REFERENCES users(id)
);

CREATE TABLE maintenance_photos (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    maintenance_request_id BIGINT NOT NULL,
    photo_url VARCHAR(500) NOT NULL,
    caption VARCHAR(255),
    photo_type ENUM('before', 'after', 'during') DEFAULT 'during',
    uploaded_by BIGINT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_request (maintenance_request_id),
    FOREIGN KEY (maintenance_request_id) REFERENCES maintenance_requests(id) ON DELETE CASCADE,
    FOREIGN KEY (uploaded_by) REFERENCES users(id)
);

CREATE TABLE maintenance_notes (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    maintenance_request_id BIGINT NOT NULL,
    note_text TEXT NOT NULL,
    added_by BIGINT NOT NULL,
    is_internal BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_request (maintenance_request_id),
    FOREIGN KEY (maintenance_request_id) REFERENCES maintenance_requests(id) ON DELETE CASCADE,
    FOREIGN KEY (added_by) REFERENCES users(id)
);

-- ==================== NOTIFICATION & MESSAGING ====================

CREATE TABLE notifications (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    uuid CHAR(36) NOT NULL UNIQUE DEFAULT (UUID()),
    
    -- Notification Details
    user_id BIGINT NOT NULL,
    title VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    type ENUM(
        'payment_reminder',
        'maintenance_update', 
        'lease_expiry',
        'new_message',
        'system_alert',
        'booking_confirmation',
        'payment_received',
        'maintenance_scheduled'
    ) NOT NULL,
    
    -- Context
    entity_type ENUM('property', 'lease', 'payment', 'maintenance', 'user'),
    entity_id BIGINT,
    action_url VARCHAR(500),
    metadata JSON,
    
    -- Status
    is_read BOOLEAN DEFAULT FALSE,
    read_at DATETIME,
    priority ENUM('low', 'medium', 'high') DEFAULT 'medium',
    
    -- Delivery
    send_email BOOLEAN DEFAULT FALSE,
    send_sms BOOLEAN DEFAULT FALSE,
    email_sent BOOLEAN DEFAULT FALSE,
    sms_sent BOOLEAN DEFAULT FALSE,
    
    -- Expiry
    expires_at DATETIME,
    
    -- Audit Fields
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Indexes
    INDEX idx_user (user_id),
    INDEX idx_type (type),
    INDEX idx_entity (entity_type, entity_id),
    INDEX idx_created (created_at),
    INDEX idx_read (is_read),
    INDEX idx_priority (priority),
    INDEX idx_notifications_user_unread (user_id, is_read, created_at),
    
    -- Foreign Keys
    FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE messages (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    uuid CHAR(36) NOT NULL UNIQUE DEFAULT (UUID()),
    
    -- Message Details
    sender_id BIGINT NOT NULL,
    receiver_id BIGINT NOT NULL,
    subject VARCHAR(255),
    message_text TEXT NOT NULL,
    message_type ENUM('text', 'image', 'file', 'system') DEFAULT 'text',
    
    -- Context
    property_id BIGINT,
    maintenance_request_id BIGINT,
    lease_id BIGINT,
    parent_message_id BIGINT,
    conversation_id VARCHAR(100) NOT NULL,
    
    -- Attachments
    attachments JSON,
    
    -- Status
    is_read BOOLEAN DEFAULT FALSE,
    read_at DATETIME,
    
    -- Audit Fields
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Indexes
    INDEX idx_sender (sender_id),
    INDEX idx_receiver (receiver_id),
    INDEX idx_property (property_id),
    INDEX idx_maintenance (maintenance_request_id),
    INDEX idx_lease (lease_id),
    INDEX idx_conversation (conversation_id),
    INDEX idx_created (created_at),
    INDEX idx_messages_conversation (sender_id, receiver_id, created_at),
    
    -- Foreign Keys
    FOREIGN KEY (sender_id) REFERENCES users(id),
    FOREIGN KEY (receiver_id) REFERENCES users(id),
    FOREIGN KEY (property_id) REFERENCES properties(id),
    FOREIGN KEY (maintenance_request_id) REFERENCES maintenance_requests(id),
    FOREIGN KEY (lease_id) REFERENCES leases(id),
    FOREIGN KEY (parent_message_id) REFERENCES messages(id) ON DELETE CASCADE
);

-- ==================== REVIEWS & RATINGS ====================

CREATE TABLE reviews (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    uuid CHAR(36) NOT NULL UNIQUE DEFAULT (UUID()),
    
    -- Review Details
    rating INT NOT NULL CHECK (rating >= 1 AND rating <= 5),
    title VARCHAR(255),
    comment TEXT,
    review_type ENUM('property', 'landlord', 'tenant') NOT NULL,
    
    -- Response
    response TEXT,
    responded_at DATETIME,
    
    -- Status
    is_published BOOLEAN DEFAULT TRUE,
    helpful_count INT DEFAULT 0,
    
    -- Relationships
    reviewer_id BIGINT NOT NULL,
    reviewee_id BIGINT,
    property_id BIGINT,
    lease_id BIGINT,
    
    -- Audit Fields
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Indexes
    INDEX idx_reviewer (reviewer_id),
    INDEX idx_reviewee (reviewee_id),
    INDEX idx_property (property_id),
    INDEX idx_lease (lease_id),
    INDEX idx_review_type (review_type),
    INDEX idx_rating (rating),
    INDEX idx_published (is_published),
    
    -- Foreign Keys
    FOREIGN KEY (reviewer_id) REFERENCES users(id),
    FOREIGN KEY (reviewee_id) REFERENCES users(id),
    FOREIGN KEY (property_id) REFERENCES properties(id),
    FOREIGN KEY (lease_id) REFERENCES leases(id)
);

-- ==================== VENDOR MANAGEMENT ====================

CREATE TABLE vendors (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    uuid CHAR(36) NOT NULL UNIQUE DEFAULT (UUID()),
    
    -- Vendor Information
    company_name VARCHAR(255) NOT NULL,
    contact_name VARCHAR(100),
    email VARCHAR(255),
    phone VARCHAR(20),
    
    -- Services
    service_categories JSON,
    primary_service_category VARCHAR(100),
    description TEXT,
    
    -- Address
    address_street VARCHAR(255),
    address_city VARCHAR(100),
    address_state VARCHAR(100),
    address_zip_code VARCHAR(20),
    address_country VARCHAR(100) DEFAULT 'US',
    
    -- Business Information
    tax_id VARCHAR(100),
    insurance_expiry DATE,
    license_number VARCHAR(100),
    license_expiry DATE,
    
    -- Insurance Information
    insurance_provider VARCHAR(255),
    insurance_policy_number VARCHAR(100),
    insurance_coverage_amount DECIMAL(15,2),
    
    -- Rating & Performance
    average_rating DECIMAL(3,2) DEFAULT 0,
    total_jobs INT DEFAULT 0,
    completed_jobs INT DEFAULT 0,
    total_earnings DECIMAL(15,2) DEFAULT 0,
    
    -- Response Time Metrics
    avg_response_time_hours DECIMAL(5,2) DEFAULT 0,
    avg_completion_time_hours DECIMAL(5,2) DEFAULT 0,
    
    -- Status
    is_approved BOOLEAN DEFAULT FALSE,
    is_verified BOOLEAN DEFAULT FALSE,
    verification_date DATETIME,
    
    -- Availability
    is_available BOOLEAN DEFAULT TRUE,
    max_jobs_per_week INT DEFAULT 5,
    
    -- Service Areas
    service_areas JSON,
    service_radius_miles INT DEFAULT 25,
    
    -- Hourly Rate
    hourly_rate DECIMAL(10,2),
    emergency_hourly_rate DECIMAL(10,2),
    
    -- Audit Fields
    created_by BIGINT NOT NULL,
    updated_by BIGINT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Indexes
    INDEX idx_company (company_name),
    INDEX idx_primary_category (primary_service_category),
    INDEX idx_approved (is_approved),
    INDEX idx_verified (is_verified),
    INDEX idx_available (is_available),
    INDEX idx_rating (average_rating),
    INDEX idx_location (address_city, address_state),
    INDEX idx_service_radius (service_radius_miles),
    INDEX idx_insurance_expiry (insurance_expiry),
    
    -- Foreign Keys
    FOREIGN KEY (created_by) REFERENCES users(id),
    FOREIGN KEY (updated_by) REFERENCES users(id)
);

CREATE TABLE vendor_service_categories (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    vendor_id BIGINT NOT NULL,
    category_code VARCHAR(50) NOT NULL,
    is_primary BOOLEAN DEFAULT FALSE,
    years_experience INT DEFAULT 0,
    hourly_rate DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    UNIQUE KEY unique_vendor_category (vendor_id, category_code),
    INDEX idx_vendor (vendor_id),
    INDEX idx_category (category_code),
    INDEX idx_primary (is_primary),
    
    FOREIGN KEY (vendor_id) REFERENCES vendors(id) ON DELETE CASCADE,
    FOREIGN KEY (category_code) REFERENCES service_categories(category_code)
);

CREATE TABLE vendor_assignments (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    uuid CHAR(36) NOT NULL UNIQUE DEFAULT (UUID()),
    
    maintenance_request_id BIGINT NOT NULL,
    vendor_id BIGINT NOT NULL,
    
    -- Assignment Details
    assigned_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    accepted_date DATETIME,
    estimated_start_date DATETIME,
    estimated_completion_date DATETIME,
    
    -- Cost Information
    estimated_cost DECIMAL(10,2),
    actual_cost DECIMAL(10,2),
    quote_amount DECIMAL(10,2),
    quote_url VARCHAR(500),
    
    -- Status
    status ENUM('pending', 'accepted', 'declined', 'in_progress', 'completed', 'cancelled') DEFAULT 'pending',
    
    -- Vendor Notes
    vendor_notes TEXT,
    internal_notes TEXT,
    
    -- Performance Metrics
    response_time_hours DECIMAL(5,2),
    completion_time_hours DECIMAL(5,2),
    
    -- Rating & Feedback
    vendor_rating INT,
    vendor_feedback TEXT,
    admin_rating INT,
    admin_feedback TEXT,
    
    -- Audit Fields
    assigned_by BIGINT NOT NULL,
    updated_by BIGINT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Indexes
    INDEX idx_request (maintenance_request_id),
    INDEX idx_vendor (vendor_id),
    INDEX idx_status (status),
    INDEX idx_assigned_date (assigned_date),
    INDEX idx_estimated_dates (estimated_start_date, estimated_completion_date),
    
    -- Foreign Keys
    FOREIGN KEY (maintenance_request_id) REFERENCES maintenance_requests(id),
    FOREIGN KEY (vendor_id) REFERENCES vendors(id),
    FOREIGN KEY (assigned_by) REFERENCES users(id),
    FOREIGN KEY (updated_by) REFERENCES users(id)
);

CREATE TABLE vendor_documents (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    vendor_id BIGINT NOT NULL,
    document_type VARCHAR(100) NOT NULL,
    document_name VARCHAR(255) NOT NULL,
    document_url VARCHAR(500) NOT NULL,
    file_size BIGINT,
    expiry_date DATE,
    is_verified BOOLEAN DEFAULT FALSE,
    verified_by BIGINT,
    verified_at DATETIME,
    uploaded_by BIGINT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_vendor (vendor_id),
    INDEX idx_document_type (document_type),
    INDEX idx_expiry (expiry_date),
    INDEX idx_verified (is_verified),
    
    FOREIGN KEY (vendor_id) REFERENCES vendors(id) ON DELETE CASCADE,
    FOREIGN KEY (verified_by) REFERENCES users(id),
    FOREIGN KEY (uploaded_by) REFERENCES users(id)
);

CREATE TABLE vendor_reviews (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    vendor_id BIGINT NOT NULL,
    maintenance_request_id BIGINT NOT NULL,
    reviewer_id BIGINT NOT NULL,
    
    -- Review Details
    rating INT NOT NULL CHECK (rating >= 1 AND rating <= 5),
    title VARCHAR(255),
    comment TEXT,
    
    -- Review Categories
    category_ratings JSON,
    
    -- Would Recommend
    would_recommend BOOLEAN,
    
    -- Status
    is_approved BOOLEAN DEFAULT TRUE,
    is_featured BOOLEAN DEFAULT FALSE,
    
    -- Audit Fields
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Indexes
    INDEX idx_vendor (vendor_id),
    INDEX idx_rating (rating),
    INDEX idx_reviewer (reviewer_id),
    INDEX idx_maintenance_request (maintenance_request_id),
    INDEX idx_approved (is_approved),
    INDEX idx_featured (is_featured),
    UNIQUE KEY unique_request_review (maintenance_request_id, reviewer_id),
    
    -- Foreign Keys
    FOREIGN KEY (vendor_id) REFERENCES vendors(id),
    FOREIGN KEY (maintenance_request_id) REFERENCES maintenance_requests(id),
    FOREIGN KEY (reviewer_id) REFERENCES users(id)
);

-- ==================== INSPECTION & QUALITY ASSURANCE ====================

CREATE TABLE inspections (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    uuid CHAR(36) NOT NULL UNIQUE DEFAULT (UUID()),
    
    -- Inspection Details
    property_id BIGINT NOT NULL,
    inspector_id BIGINT NOT NULL,
    inspection_type VARCHAR(100) NOT NULL,
    
    -- Scheduling
    scheduled_date DATETIME NOT NULL,
    completed_date DATETIME,
    
    -- Results
    overall_score INT,
    status VARCHAR(50) DEFAULT 'scheduled',
    
    -- Documentation
    report_url VARCHAR(500),
    notes TEXT,
    
    -- Audit Fields
    created_by BIGINT NOT NULL,
    updated_by BIGINT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Indexes
    INDEX idx_property (property_id),
    INDEX idx_inspector (inspector_id),
    INDEX idx_scheduled (scheduled_date),
    INDEX idx_type (inspection_type),
    
    -- Foreign Keys
    FOREIGN KEY (property_id) REFERENCES properties(id),
    FOREIGN KEY (inspector_id) REFERENCES users(id),
    FOREIGN KEY (created_by) REFERENCES users(id),
    FOREIGN KEY (updated_by) REFERENCES users(id)
);

CREATE TABLE inspection_items (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    inspection_id BIGINT NOT NULL,
    
    -- Item Details
    category VARCHAR(100) NOT NULL,
    item_name VARCHAR(255) NOT NULL,
    item_description TEXT,
    
    -- Scoring
    score INT,
    max_score INT DEFAULT 10,
    notes TEXT,
    
    -- Photos
    photo_url VARCHAR(500),
    
    -- Order
    item_order INT DEFAULT 0,
    
    -- Indexes
    INDEX idx_inspection (inspection_id),
    INDEX idx_category (category),
    
    -- Foreign Keys
    FOREIGN KEY (inspection_id) REFERENCES inspections(id) ON DELETE CASCADE
);

-- ==================== ADDITIONAL FEATURES ====================

CREATE TABLE leads (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    uuid CHAR(36) NOT NULL UNIQUE DEFAULT (UUID()),
    
    -- Lead Information
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(255),
    phone VARCHAR(20),
    
    -- Requirements
    preferred_property_type VARCHAR(100),
    min_bedrooms INT,
    max_budget DECIMAL(10,2),
    preferred_locations JSON,
    
    -- Source & Status
    source VARCHAR(100),
    status VARCHAR(50) DEFAULT 'new',
    priority VARCHAR(20) DEFAULT 'medium',
    
    -- Assignment
    assigned_agent_id BIGINT,
    
    -- Communication
    last_contact_date DATETIME,
    next_follow_up DATETIME,
    
    -- Notes
    notes TEXT,
    
    -- Audit Fields
    created_by BIGINT NOT NULL,
    updated_by BIGINT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Indexes
    INDEX idx_email (email),
    INDEX idx_phone (phone),
    INDEX idx_assigned_agent (assigned_agent_id),
    INDEX idx_status (status),
    INDEX idx_priority (priority),
    INDEX idx_follow_up (next_follow_up),
    
    -- Foreign Keys
    FOREIGN KEY (assigned_agent_id) REFERENCES users(id),
    FOREIGN KEY (created_by) REFERENCES users(id),
    FOREIGN KEY (updated_by) REFERENCES users(id)
);

CREATE TABLE insurance_policies (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    uuid CHAR(36) NOT NULL UNIQUE DEFAULT (UUID()),
    
    -- Policy Details
    property_id BIGINT NOT NULL,
    insurance_company VARCHAR(255) NOT NULL,
    policy_number VARCHAR(100) NOT NULL,
    policy_type VARCHAR(100) NOT NULL,
    
    -- Coverage
    coverage_amount DECIMAL(15,2) NOT NULL,
    deductible DECIMAL(10,2),
    coverage_details JSON,
    
    -- Term
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    renewal_date DATE,
    
    -- Premium
    annual_premium DECIMAL(10,2) NOT NULL,
    payment_frequency VARCHAR(50),
    
    -- Agent Information
    agent_name VARCHAR(100),
    agent_contact VARCHAR(255),
    
    -- Documents
    policy_document_url VARCHAR(500),
    
    -- Status
    is_active BOOLEAN DEFAULT TRUE,
    
    -- Audit Fields
    created_by BIGINT NOT NULL,
    updated_by BIGINT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Indexes
    INDEX idx_property (property_id),
    INDEX idx_company (insurance_company),
    INDEX idx_policy_number (policy_number),
    INDEX idx_dates (start_date, end_date),
    INDEX idx_active (is_active),
    
    -- Foreign Keys
    FOREIGN KEY (property_id) REFERENCES properties(id),
    FOREIGN KEY (created_by) REFERENCES users(id),
    FOREIGN KEY (updated_by) REFERENCES users(id)
);

-- ==================== SECURITY & AUDIT ====================

CREATE TABLE audit_logs (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    
    -- Audit Details
    user_id BIGINT,
    action VARCHAR(100) NOT NULL,
    entity_type ENUM('user', 'property', 'lease', 'payment', 'maintenance', 'document', 'review', 'vendor') NOT NULL,
    entity_id BIGINT NOT NULL,
    
    -- Changes
    old_values JSON,
    new_values JSON,
    
    -- Context
    ip_address VARCHAR(45),
    user_agent TEXT,
    description TEXT,
    affected_user_id BIGINT,
    
    -- Timestamp
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- Indexes
    INDEX idx_user (user_id),
    INDEX idx_entity (entity_type, entity_id),
    INDEX idx_action (action),
    INDEX idx_created (created_at),
    INDEX idx_audit_entity (entity_type, entity_id, created_at),
    
    -- Foreign Keys
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (affected_user_id) REFERENCES users(id)
);

CREATE TABLE auth_attempts (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(255) NOT NULL,
    ip_address VARCHAR(45) NOT NULL,
    attempt_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    successful BOOLEAN DEFAULT FALSE,
    
    INDEX idx_email_date (email, attempt_date),
    INDEX idx_ip_date (ip_address, attempt_date)
);

CREATE TABLE system_settings (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    setting_key VARCHAR(255) NOT NULL UNIQUE,
    setting_value TEXT,
    setting_type VARCHAR(50) DEFAULT 'string',
    description TEXT,
    is_public BOOLEAN DEFAULT FALSE,
    updated_by BIGINT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_key (setting_key),
    FOREIGN KEY (updated_by) REFERENCES users(id)
);

-- ==================== INSERT DEFAULT DATA ====================

-- Insert User Roles
INSERT INTO user_roles (role_code, role_name, description) VALUES
('super_admin', 'Super Administrator', 'Full platform access and system management'),
('system_admin', 'System Administrator', 'Enhanced system administrator with user management'),
('landlord', 'Property Owner/Landlord', 'Property owners and investors'),
('tenant', 'Tenant/Resident', 'Property residents and tenants'),
('property_manager', 'Property Manager', 'Professional property management'),
('property_agent', 'Property Agent', 'Real estate agents and brokers'),
('regional_manager', 'Regional Manager', 'Multi-location property management'),
('leasing_consultant', 'Leasing Consultant', 'Tenant acquisition specialists'),
('maintenance_supervisor', 'Maintenance Supervisor', 'Maintenance team leadership'),
('maintenance_staff', 'Maintenance Staff', 'Maintenance and repair technicians'),
('financial_officer', 'Financial Officer', 'Financial management and oversight'),
('financial_analyst', 'Financial Analyst', 'Financial analysis and reporting'),
('marketing_specialist', 'Marketing Specialist', 'Property marketing and advertising'),
('legal_advisor', 'Legal Advisor', 'Legal compliance and advice'),
('insurance_coordinator', 'Insurance Coordinator', 'Insurance policy management'),
('relocation_specialist', 'Relocation Specialist', 'Tenant relocation services'),
('community_manager', 'Community Manager', 'Apartment community management'),
('inspector', 'Inspector/Quality Assurance', 'Property inspections and quality control'),
('vendor', 'Vendor/Contractor', 'External service providers'),
('bnb_host', 'BNB Host', 'Short-term rental hosts'),
('corporate_housing_manager', 'Corporate Housing Manager', 'Corporate housing management'),
('student_housing_coordinator', 'Student Housing Coordinator', 'Student housing specialists'),
('luxury_property_specialist', 'Luxury Property Specialist', 'High-end property management'),
('data_analyst', 'Data Analyst', 'Business intelligence and analytics'),
('customer_support_agent', 'Customer Support Agent', 'User support and assistance');

-- Insert Property Types
INSERT INTO property_types (type_code, type_name, description) VALUES
('apartment', 'Apartment', 'Multi-unit residential building'),
('house', 'Single-Family Home', 'Detached single-family residence'),
('condo', 'Condominium', 'Individually owned unit in a complex'),
('townhouse', 'Townhouse', 'Multi-floor attached home'),
('studio', 'Studio', 'Single-room apartment'),
('villa', 'Villa', 'Luxury detached residence'),
('multi_family', 'Multi-Family', 'Building with multiple residential units'),
('commercial', 'Commercial Space', 'Business and commercial properties'),
('other', 'Other', 'Other property types');

-- Insert Property Statuses
INSERT INTO property_statuses (status_code, status_name, description) VALUES
('draft', 'Draft', 'Property listing not published'),
('available', 'Available', 'Property available for rent'),
('occupied', 'Occupied', 'Property currently rented'),
('maintenance', 'Under Maintenance', 'Property undergoing maintenance'),
('unavailable', 'Unavailable', 'Property not available for rent');

-- Insert Maintenance Priorities
INSERT INTO maintenance_priorities (priority_code, priority_name, description, sla_hours) VALUES
('emergency', 'Emergency', 'Immediate attention required - safety hazards', 2),
('urgent', 'Urgent', 'Critical issues affecting livability', 24),
('high', 'High', 'Important but not critical issues', 72),
('normal', 'Normal', 'Routine maintenance requests', 168),
('low', 'Low', 'Cosmetic or minor issues', 336);

-- Insert Maintenance Statuses
INSERT INTO maintenance_statuses (status_code, status_name, description) VALUES
('submitted', 'Submitted', 'Request submitted by tenant'),
('assigned', 'Assigned', 'Assigned to maintenance staff'),
('in_progress', 'In Progress', 'Work in progress'),
('completed', 'Completed', 'Work completed successfully'),
('cancelled', 'Cancelled', 'Request cancelled'),
('on_hold', 'On Hold', 'Work temporarily on hold');

-- Insert Lease Statuses
INSERT INTO lease_statuses (status_code, status_name, description) VALUES
('draft', 'Draft', 'Lease agreement in draft stage'),
('pending', 'Pending', 'Awaiting signature or approval'),
('active', 'Active', 'Lease currently in effect'),
('expired', 'Expired', 'Lease term completed'),
('terminated', 'Terminated', 'Lease ended prematurely'),
('renewed', 'Renewed', 'Lease extended for new term');

-- Insert Payment Statuses
INSERT INTO payment_statuses (status_code, status_name, description) VALUES
('pending', 'Pending', 'Payment initiated but not completed'),
('paid', 'Paid', 'Payment successfully completed'),
('failed', 'Failed', 'Payment failed or declined'),
('overdue', 'Overdue', 'Payment past due date'),
('partial', 'Partial', 'Partial payment received'),
('refunded', 'Refunded', 'Payment refunded to tenant');

-- Insert Service Categories
INSERT INTO service_categories (category_code, category_name, description) VALUES
('plumbing_emergency', 'Emergency Plumbing', '24/7 emergency plumbing services'),
('plumbing_repair', 'Plumbing Repair', 'General plumbing repairs and maintenance'),
('plumbing_installation', 'Plumbing Installation', 'New plumbing fixture installation'),
('electrical_emergency', 'Emergency Electrical', '24/7 emergency electrical services'),
('electrical_repair', 'Electrical Repair', 'General electrical repairs'),
('electrical_installation', 'Electrical Installation', 'New electrical fixture installation'),
('hvac_repair', 'HVAC Repair', 'Heating and cooling system repairs'),
('hvac_maintenance', 'HVAC Maintenance', 'Regular HVAC maintenance'),
('hvac_installation', 'HVAC Installation', 'New HVAC system installation'),
('carpentry', 'Carpentry', 'Woodwork and structural repairs'),
('painting', 'Painting', 'Interior and exterior painting'),
('drywall', 'Drywall Repair', 'Drywall installation and repair'),
('appliance_repair', 'Appliance Repair', 'Home appliance repairs'),
('appliance_installation', 'Appliance Installation', 'New appliance installation'),
('landscaping', 'Landscaping', 'Yard maintenance and landscaping'),
('roofing', 'Roofing', 'Roof repairs and maintenance'),
('gutter_cleaning', 'Gutter Cleaning', 'Gutter cleaning and repair'),
('deep_cleaning', 'Deep Cleaning', 'Thorough cleaning services'),
('move_in_cleaning', 'Move-In/Move-Out Cleaning', 'Cleaning for tenant transitions'),
('carpet_cleaning', 'Carpet Cleaning', 'Professional carpet cleaning'),
('pest_control', 'Pest Control', 'Pest extermination and prevention'),
('emergency_boardup', 'Emergency Board-Up', 'Property securing after damage'),
('water_restoration', 'Water Damage Restoration', 'Water damage cleanup and restoration'),
('property_inspection', 'Property Inspection', 'Professional property inspections'),
('safety_inspection', 'Safety Inspection', 'Safety compliance inspections');

-- Insert System Settings
INSERT INTO system_settings (setting_key, setting_value, setting_type, description, is_public) VALUES
('company_name', 'StaySpot', 'string', 'Platform company name', TRUE),
('platform_version', '2.0.0', 'string', 'Current platform version', TRUE),
('default_currency', 'USD', 'string', 'Default currency for payments', TRUE),
('rent_due_day', '1', 'number', 'Default day of month rent is due', FALSE),
('late_fee_percentage', '5.0', 'number', 'Default late fee percentage', FALSE),
('grace_period_days', '5', 'number', 'Default grace period for late payments', FALSE),
('maintenance_emergency_hours', '2', 'number', 'SLA for emergency maintenance', FALSE),
('maintenance_urgent_hours', '24', 'number', 'SLA for urgent maintenance', FALSE),
('auto_archive_days', '90', 'number', 'Days before archiving old records', FALSE);

-- ==================== CREATE PROPERTIES ====================

INSERT INTO properties (title, description, property_type_code, status_code, address_street, address_city, address_state, address_zip_code, bedrooms, bathrooms, area_sqft, monthly_rent, security_deposit, amenities, owner_id, is_published, created_by, created_at) VALUES
('Luxury Downtown Apartment', 'Beautiful luxury apartment in the heart of downtown with amazing city views and modern amenities. Features hardwood floors, stainless steel appliances, and a private balcony overlooking the city.', 'apartment', 'available', '123 Main Street', 'New York', 'NY', '10001', 2, 2, 1200, 2500.00, 2500.00, '["wifi", "parking", "gym", "pool", "air_conditioning", "laundry", "dishwasher"]', 3, TRUE, 3, NOW()),

('Cozy Suburban House', 'Charming 3-bedroom house in quiet suburban neighborhood with garden and garage. Perfect for families with plenty of outdoor space and recent renovations throughout.', 'house', 'available', '456 Oak Avenue', 'Chicago', 'IL', '60007', 3, 2, 1800, 1800.00, 1800.00, '["parking", "garden", "pet_friendly", "laundry"]', 3, TRUE, 3, NOW()),

('Modern City Condo', 'Sleek modern condo with high-end finishes and convenient downtown location. Walking distance to restaurants, shopping, and public transportation.', 'condo', 'available', '789 Downtown Blvd', 'San Francisco', 'CA', '94102', 1, 1, 900, 3200.00, 3200.00, '["wifi", "gym", "security", "elevator", "air_conditioning"]', 3, TRUE, 3, NOW()),

('Spacious Townhouse', 'Large townhouse perfect for families, featuring multiple levels and private outdoor space. Recently updated with modern kitchen and bathrooms.', 'townhouse', 'available', '321 Elm Street', 'Austin', 'TX', '73301', 4, 3, 2200, 2200.00, 2200.00, '["parking", "pet_friendly", "laundry", "air_conditioning"]', 3, TRUE, 3, NOW()),

('Charming Studio Apartment', 'Compact and efficient studio apartment in vibrant neighborhood, perfect for singles or students. Recently renovated with new appliances and flooring.', 'studio', 'available', '654 Pine Road', 'Portland', 'OR', '97205', 1, 1, 600, 1500.00, 1500.00, '["wifi", "air_conditioning", "laundry"]', 3, TRUE, 3, NOW());

-- ==================== CREATE LEASES ====================

INSERT INTO leases (property_id, tenant_id, landlord_id, primary_tenant_id, lease_number, start_date, end_date, monthly_rent, security_deposit, status_code, created_by, created_at) VALUES
(1, 10, 3, 10, 'LEASE-001', '2024-01-01', '2024-12-31', 2500.00, 2500.00, 'active', 3, NOW()),
(2, 11, 3, 11, 'LEASE-002', '2024-02-01', '2025-01-31', 1800.00, 1800.00, 'active', 3, NOW()),
(3, 10, 3, 10, 'LEASE-003', '2024-03-01', '2025-02-28', 3200.00, 3200.00, 'pending', 3, NOW()),
(4, 11, 3, 11, 'LEASE-004', '2023-11-01', '2024-10-31', 2200.00, 2200.00, 'active', 3, NOW()),
(5, 10, 3, 10, 'LEASE-005', '2024-01-15', '2024-12-14', 1500.00, 1500.00, 'active', 3, NOW());

-- ==================== CREATE PAYMENTS ====================

INSERT INTO payments (lease_id, tenant_id, landlord_id, property_id, amount_due, amount_paid, period_month, period_year, due_date, paid_date, status_code, payment_method, description, created_by, created_at) VALUES
-- Lease 1 payments
(1, 10, 3, 1, 2500.00, 2500.00, 1, 2024, '2024-01-01', '2024-01-01', 'paid', 'bank_transfer', 'January 2024 Rent', 3, NOW()),
(1, 10, 3, 1, 2500.00, 2500.00, 2, 2024, '2024-02-01', '2024-02-01', 'paid', 'bank_transfer', 'February 2024 Rent', 3, NOW()),
(1, 10, 3, 1, 2500.00, 2500.00, 3, 2024, '2024-03-01', '2024-03-01', 'paid', 'credit_card', 'March 2024 Rent', 3, NOW()),
(1, 10, 3, 1, 2500.00, 0, 4, 2024, '2024-04-01', NULL, 'pending', NULL, 'April 2024 Rent', 3, NOW()),

-- Lease 2 payments
(2, 11, 3, 2, 1800.00, 1800.00, 2, 2024, '2024-02-01', '2024-02-01', 'paid', 'bank_transfer', 'February 2024 Rent', 3, NOW()),
(2, 11, 3, 2, 1800.00, 1800.00, 3, 2024, '2024-03-01', '2024-03-01', 'paid', 'bank_transfer', 'March 2024 Rent', 3, NOW()),
(2, 11, 3, 2, 1800.00, 0, 4, 2024, '2024-04-01', NULL, 'pending', NULL, 'April 2024 Rent', 3, NOW());

-- ==================== CREATE MAINTENANCE REQUESTS ====================

INSERT INTO maintenance_requests (property_id, requested_by, landlord_id, title, description, category, priority_code, status_code, created_by, created_at) VALUES
(1, 10, 3, 'Leaking Kitchen Faucet', 'The kitchen faucet has been leaking constantly for the past 2 days. It drips about once per second and is wasting water.', 'plumbing', 'normal', 'submitted', 10, NOW()),
(2, 11, 3, 'AC Not Cooling', 'The air conditioning unit is running but not cooling the apartment. The temperature stays around 78°F even when set to 72°F.', 'hvac', 'high', 'in_progress', 11, NOW()),
(1, 10, 3, 'Broken Window Blinds', 'The living room window blinds are broken and won''t open or close properly. The mechanism seems to be stuck.', 'cosmetic', 'low', 'completed', 10, NOW()),
(2, 11, 3, 'Electrical Outlet Not Working', 'The electrical outlet in the bedroom stopped working yesterday. Tried resetting the breaker but it still doesn''t work.', 'electrical', 'high', 'submitted', 11, NOW());

-- ==================== CREATE NOTIFICATIONS ====================

INSERT INTO notifications (user_id, title, message, type, is_read, created_at) VALUES
(10, 'Welcome to StaySpot!', 'Thank you for joining StaySpot. Your account has been successfully created.', 'system_alert', FALSE, NOW()),
(3, 'Payment Received', 'Your rent payment of $2,500.00 for March 2024 has been received.', 'payment_received', FALSE, NOW()),
(10, 'Maintenance Request Submitted', 'Your maintenance request "Leaking Kitchen Faucet" has been received and will be reviewed shortly.', 'maintenance_update', FALSE, NOW()),
(10, 'Lease Renewal Reminder', 'Your lease for Luxury Downtown Apartment will expire in 60 days. Contact your landlord to discuss renewal options.', 'lease_expiry', FALSE, NOW());

-- ==================== CREATE VENDORS ====================

INSERT INTO vendors (company_name, contact_name, email, phone, service_categories, description, address_street, address_city, address_state, address_zip_code, is_approved, is_verified, hourly_rate, created_by, created_at) VALUES
('QuickFix Plumbing', 'Mike Johnson', 'mike@quickfixplumbing.com', '+1-555-1001', '["plumbing_emergency", "plumbing_repair"]', '24/7 emergency plumbing services with 15 years of experience', '123 Plumbing Ave', 'New York', 'NY', '10001', TRUE, TRUE, 85.00, 3, NOW()),
('Pro Electrical Solutions', 'Sarah Wilson', 'sarah@proelectrical.com', '+1-555-1002', '["electrical_emergency", "electrical_repair"]', 'Licensed and insured electrical contractors', '456 Electric St', 'Chicago', 'IL', '60007', TRUE, TRUE, 95.00, 3, NOW()),
('Comfort HVAC Services', 'Tom Davis', 'tom@comforthvac.com', '+1-555-1003', '["hvac_repair", "hvac_maintenance"]', 'Heating and cooling specialists serving the tri-state area', '789 HVAC Blvd', 'San Francisco', 'CA', '94102', TRUE, TRUE, 110.00, 3, NOW());

CREATE VIEW financial_summary AS
SELECT 
    p.id as property_id,
    p.title as property_title,
    p.monthly_rent,
    COALESCE(SUM(CASE WHEN pay.status_code = 'paid' THEN pay.amount_paid ELSE 0 END), 0) as total_rent_collected,
    COALESCE(SUM(CASE WHEN pay.status_code = 'overdue' THEN pay.amount_due ELSE 0 END), 0) as total_rent_overdue,
    COALESCE(SUM(e.amount), 0) as total_expenses,
    (COALESCE(SUM(CASE WHEN pay.status_code = 'paid' THEN pay.amount_paid ELSE 0 END), 0) - COALESCE(SUM(e.amount), 0)) as net_income
FROM properties p
LEFT JOIN leases l ON p.id = l.property_id AND l.status_code = 'active' AND l.deleted_at IS NULL
LEFT JOIN payments pay ON l.id = pay.lease_id
LEFT JOIN expenses e ON p.id = e.property_id AND YEAR(e.expense_date) = YEAR(CURRENT_DATE) AND MONTH(e.expense_date) = MONTH(CURRENT_DATE)
WHERE p.deleted_at IS NULL
GROUP BY p.id, p.title, p.monthly_rent;

-- ==================== FINAL SETUP ====================

-- Display completion message
SELECT 'StaySpot Combined Database Schema Created Successfully!' as message;