CREATE TABLE maintenance_requests (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    organization_tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    property_id UUID NOT NULL REFERENCES properties(id) ON DELETE CASCADE,
    unit_id UUID NOT NULL REFERENCES units(id) ON DELETE CASCADE,
    tenant_id UUID REFERENCES tenants_table(id) ON DELETE SET NULL,
    request_number VARCHAR(50) UNIQUE NOT NULL,
    request_type VARCHAR(100) NOT NULL CHECK (request_type IN (
        'plumbing', 'electrical', 'hvac', 'appliance', 'structural',
        'pest_control', 'cleaning', 'landscaping', 'painting',
        'lock_security', 'water_damage', 'roofing', 'flooring',
        'move_in_inspection', 'move_out_inspection', 'routine_inspection',
        'emergency', 'other'
    )),
    priority VARCHAR(50) DEFAULT 'medium' CHECK (priority IN (
        'low', 'medium', 'high', 'urgent', 'emergency'
    )),
    title VARCHAR(255) NOT NULL,
    description TEXT NOT NULL,
    status VARCHAR(50) DEFAULT 'open' CHECK (status IN (
        'open', 'acknowledged', 'in_progress', 'on_hold', 
        'pending_parts', 'pending_approval', 'completed', 'cancelled'
    )),
    status_history JSONB DEFAULT '[]',
    scheduled_date TIMESTAMP WITH TIME ZONE,
    completed_date TIMESTAMP WITH TIME ZONE,
    estimated_duration_hours DECIMAL(5, 1),
    actual_duration_hours DECIMAL(5, 1),
    assigned_to UUID REFERENCES users(id),
    vendor_id UUID REFERENCES maintenance_vendors(id),
    assigned_at TIMESTAMP WITH TIME ZONE,
    estimated_cost DECIMAL(10, 2),
    actual_cost DECIMAL(10, 2),
    is_billable_to_tenant BOOLEAN DEFAULT FALSE,
    approval_required BOOLEAN DEFAULT FALSE,
    approved_by UUID REFERENCES users(id),
    approved_at TIMESTAMP WITH TIME ZONE,
    images JSONB DEFAULT '[]',
    documents JSONB DEFAULT '[]',
    tenant_rating INTEGER CHECK (tenant_rating BETWEEN 1 AND 5),
    tenant_feedback TEXT,
    resolution_notes TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    deleted_at TIMESTAMP WITH TIME ZONE,
    metadata JSONB DEFAULT '{}',
    created_by UUID REFERENCES users(id),
    updated_by UUID REFERENCES users(id)
);
CREATE TABLE maintenance_vendors (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    organization_tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    company_name VARCHAR(255) NOT NULL,
    contact_person VARCHAR(255),
    email VARCHAR(255),
    phone VARCHAR(20) NOT NULL,
    alternative_phone VARCHAR(20),
    website VARCHAR(255),
    address_line1 VARCHAR(255),
    address_line2 VARCHAR(255),
    city VARCHAR(100),
    state VARCHAR(100),
    postal_code VARCHAR(20),
    country VARCHAR(100) DEFAULT 'Kenya',
    specializations JSONB DEFAULT '[]', 
    service_areas JSONB DEFAULT '[]', 
    tax_id VARCHAR(100),
    registration_number VARCHAR(100),
    insurance_expiry DATE,
    license_number VARCHAR(100),
    status VARCHAR(50) DEFAULT 'active' CHECK (status IN (
        'active', 'inactive', 'blacklisted', 'pending_review'
    )),
    average_rating DECIMAL(3, 2) DEFAULT 0,
    total_jobs_completed INTEGER DEFAULT 0,
    total_cost_saved DECIMAL(10, 2) DEFAULT 0,
    documents JSONB DEFAULT '[]',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    deleted_at TIMESTAMP WITH TIME ZONE,
    metadata JSONB DEFAULT '{}',
    created_by UUID REFERENCES users(id)
);
CREATE TABLE vendor_reviews (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    vendor_id UUID NOT NULL REFERENCES maintenance_vendors(id) ON DELETE CASCADE,
    maintenance_request_id UUID REFERENCES maintenance_requests(id) ON DELETE SET NULL,
    rating INTEGER NOT NULL CHECK (rating BETWEEN 1 AND 5),
    review_text TEXT,
    quality_rating INTEGER CHECK (quality_rating BETWEEN 1 AND 5),
    timeliness_rating INTEGER CHECK (timeliness_rating BETWEEN 1 AND 5),
    cost_rating INTEGER CHECK (cost_rating BETWEEN 1 AND 5),
    communication_rating INTEGER CHECK (communication_rating BETWEEN 1 AND 5),
    is_verified BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    metadata JSONB DEFAULT '{}',
    created_by UUID REFERENCES users(id)
);
CREATE TABLE maintenance_schedules (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    organization_tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    property_id UUID NOT NULL REFERENCES properties(id) ON DELETE CASCADE,
    unit_id UUID REFERENCES units(id) ON DELETE CASCADE,
    task_name VARCHAR(255) NOT NULL,
    task_type VARCHAR(100) CHECK (task_type IN (
        'inspection', 'servicing', 'cleaning', 'testing', 'replacement', 'other'
    )),
    description TEXT,
    frequency VARCHAR(50) NOT NULL CHECK (frequency IN (
        'daily', 'weekly', 'biweekly', 'monthly', 'quarterly', 
        'biannually', 'annually', 'custom'
    )),
    frequency_interval INTEGER,
    next_due_date DATE NOT NULL,
    last_completed_date DATE,
    grace_period_days INTEGER DEFAULT 7,
    assigned_to UUID REFERENCES users(id),
    vendor_id UUID REFERENCES maintenance_vendors(id),
    status VARCHAR(50) DEFAULT 'active' CHECK (status IN (
        'active', 'paused', 'completed', 'cancelled'
    )),
    checklist JSONB DEFAULT '[]',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    metadata JSONB DEFAULT '{}',
    created_by UUID REFERENCES users(id)
);

CREATE TABLE inspections (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    organization_tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    property_id UUID NOT NULL REFERENCES properties(id) ON DELETE CASCADE,
    unit_id UUID NOT NULL REFERENCES units(id) ON DELETE CASCADE,
    lease_id UUID REFERENCES leases(id) ON DELETE SET NULL,
    inspection_type VARCHAR(100) CHECK (inspection_type IN (
        'move_in', 'move_out', 'routine', 'safety', 'compliance', 'damage', 'other'
    )),
    inspection_date DATE NOT NULL,
    inspector_name VARCHAR(255),
    inspector_id UUID REFERENCES users(id),
    overall_condition VARCHAR(50) CHECK (overall_condition IN (
        'excellent', 'good', 'fair', 'poor', 'critical'
    )),
    notes TEXT,
    checklist_results JSONB DEFAULT '[]',
    issues_found JSONB DEFAULT '[]',
    images JSONB DEFAULT '[]',
    documents JSONB DEFAULT '[]',
    tenant_signature VARCHAR(500),
    inspector_signature VARCHAR(500),
    signed_date DATE,
    requires_follow_up BOOLEAN DEFAULT FALSE,
    follow_up_date DATE,
    created_maintenance_requests JSONB DEFAULT '[]',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    deleted_at TIMESTAMP WITH TIME ZONE,
    metadata JSONB DEFAULT '{}',
    created_by UUID REFERENCES users(id)
);
CREATE TABLE maintenance_inventory (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    organization_tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    property_id UUID REFERENCES properties(id) ON DELETE SET NULL,
    item_name VARCHAR(255) NOT NULL,
    item_code VARCHAR(100),
    category VARCHAR(100),
    description TEXT,
    quantity_on_hand INTEGER DEFAULT 0,
    minimum_quantity INTEGER DEFAULT 0,
    unit_of_measure VARCHAR(50),
    unit_cost DECIMAL(10, 2),
    total_value DECIMAL(10, 2) GENERATED ALWAYS AS (quantity_on_hand * COALESCE(unit_cost, 0)) STORED,
    storage_location VARCHAR(255),
    reorder_point INTEGER,
    reorder_quantity INTEGER,
    supplier_id UUID,
    status VARCHAR(50) DEFAULT 'active' CHECK (status IN (
        'active', 'discontinued', 'out_of_stock'
    )),
    last_restock_date DATE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    metadata JSONB DEFAULT '{}',
    created_by UUID REFERENCES users(id)
);
CREATE INDEX idx_maintenance_org ON maintenance_requests(organization_tenant_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_maintenance_property ON maintenance_requests(property_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_maintenance_unit ON maintenance_requests(unit_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_maintenance_tenant ON maintenance_requests(tenant_id);
CREATE INDEX idx_maintenance_status ON maintenance_requests(status) WHERE deleted_at IS NULL;
CREATE INDEX idx_maintenance_priority ON maintenance_requests(priority);
CREATE INDEX idx_maintenance_type ON maintenance_requests(request_type);
CREATE INDEX idx_maintenance_assigned ON maintenance_requests(assigned_to);
CREATE INDEX idx_maintenance_number ON maintenance_requests(request_number);
CREATE INDEX idx_maintenance_scheduled ON maintenance_requests(scheduled_date);
CREATE INDEX idx_vendors_org ON maintenance_vendors(organization_tenant_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_vendors_status ON maintenance_vendors(status) WHERE deleted_at IS NULL;
CREATE INDEX idx_vendors_rating ON maintenance_vendors(average_rating);
CREATE INDEX idx_vendor_reviews_vendor ON vendor_reviews(vendor_id);
CREATE INDEX idx_vendor_reviews_request ON vendor_reviews(maintenance_request_id);
CREATE INDEX idx_maint_schedules_org ON maintenance_schedules(organization_tenant_id);
CREATE INDEX idx_maint_schedules_property ON maintenance_schedules(property_id);
CREATE INDEX idx_maint_schedules_next_due ON maintenance_schedules(next_due_date);
CREATE INDEX idx_maint_schedules_status ON maintenance_schedules(status);
CREATE INDEX idx_inspections_org ON inspections(organization_tenant_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_inspections_property ON inspections(property_id);
CREATE INDEX idx_inspections_unit ON inspections(unit_id);
CREATE INDEX idx_inspections_type ON inspections(inspection_type);
CREATE INDEX idx_inspections_date ON inspections(inspection_date);
CREATE INDEX idx_inventory_org ON maintenance_inventory(organization_tenant_id);
CREATE INDEX idx_inventory_property ON maintenance_inventory(property_id);
CREATE INDEX idx_inventory_code ON maintenance_inventory(item_code);
CREATE INDEX idx_inventory_status ON maintenance_inventory(status);
CREATE TRIGGER update_maintenance_timestamp BEFORE UPDATE ON maintenance_requests
    FOR EACH ROW EXECUTE FUNCTION update_timestamp();

CREATE TRIGGER update_vendors_timestamp BEFORE UPDATE ON maintenance_vendors
    FOR EACH ROW EXECUTE FUNCTION update_timestamp();

CREATE TRIGGER update_vendor_reviews_timestamp BEFORE UPDATE ON vendor_reviews
    FOR EACH ROW EXECUTE FUNCTION update_timestamp();

CREATE TRIGGER update_maint_schedules_timestamp BEFORE UPDATE ON maintenance_schedules
    FOR EACH ROW EXECUTE FUNCTION update_timestamp();

CREATE TRIGGER update_inspections_timestamp BEFORE UPDATE ON inspections
    FOR EACH ROW EXECUTE FUNCTION update_timestamp();

CREATE TRIGGER update_inventory_timestamp BEFORE UPDATE ON maintenance_inventory
    FOR EACH ROW EXECUTE FUNCTION update_timestamp();
    
CREATE OR REPLACE FUNCTION update_vendor_rating()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE maintenance_vendors 
    SET 
        average_rating = (
            SELECT COALESCE(AVG(rating), 0) 
            FROM vendor_reviews 
            WHERE vendor_id = NEW.vendor_id
        ),
        total_jobs_completed = (
            SELECT COUNT(DISTINCT maintenance_request_id) 
            FROM vendor_reviews 
            WHERE vendor_id = NEW.vendor_id
        ),
        updated_at = NOW()
    WHERE id = NEW.vendor_id;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_vendor_rating_on_review
    AFTER INSERT OR UPDATE OF rating ON vendor_reviews
    FOR EACH ROW
    EXECUTE FUNCTION update_vendor_rating();