CREATE TABLE properties (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    property_type VARCHAR(100) CHECK (property_type IN (
        'residential', 'commercial', 'industrial', 'mixed_use', 'land'
    )),
    description TEXT,
    address_line1 VARCHAR(255) NOT NULL,
    address_line2 VARCHAR(255),
    city VARCHAR(100),
    state VARCHAR(100),
    postal_code VARCHAR(20),
    country VARCHAR(100) DEFAULT 'Kenya',
    latitude DECIMAL(10, 8),
    longitude DECIMAL(11, 8),
    year_built INTEGER,
    total_units INTEGER DEFAULT 0,
    total_square_footage DECIMAL(10, 2),
    number_of_floors INTEGER,
    parking_spaces INTEGER DEFAULT 0,
    amenities JSONB DEFAULT '[]',
    manager_id UUID REFERENCES users(id) ON DELETE SET NULL,
    agent_id UUID REFERENCES users(id) ON DELETE SET NULL,
    management_company VARCHAR(255),
    currency VARCHAR(3) DEFAULT 'KES',
    tax_rate DECIMAL(5, 2) DEFAULT 16.00,
    late_fee_percentage DECIMAL(5, 2) DEFAULT 5.00,
    late_fee_grace_period INTEGER DEFAULT 5,
    status VARCHAR(50) DEFAULT 'active' CHECK (status IN (
        'active', 'inactive', 'under_renovation', 'sold', 'archived'
    )),
    primary_image_url VARCHAR(500),
    images JSONB DEFAULT '[]',
    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),
    updated_by UUID REFERENCES users(id)
);
CREATE TABLE units (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    property_id UUID NOT NULL REFERENCES properties(id) ON DELETE CASCADE,
    tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    unit_number VARCHAR(50) NOT NULL,
    unit_type VARCHAR(100) CHECK (unit_type IN (
        'studio', 'one_bedroom', 'two_bedroom', 'three_bedroom',
        'penthouse', 'office', 'retail', 'warehouse', 'parking', 'other'
    )),
    floor_number INTEGER,
    square_footage DECIMAL(10, 2),
    bedrooms INTEGER DEFAULT 0,
    bathrooms DECIMAL(3, 1) DEFAULT 0,
    has_balcony BOOLEAN DEFAULT FALSE,
    has_parking BOOLEAN DEFAULT FALSE,
    features JSONB DEFAULT '{}',
    base_rent DECIMAL(10, 2),
    security_deposit DECIMAL(10, 2),
    includes_utilities BOOLEAN DEFAULT FALSE,
    utility_allowance DECIMAL(10, 2) DEFAULT 0,
    status VARCHAR(50) DEFAULT 'vacant' CHECK (status IN (
        'vacant', 'occupied', 'reserved', 'under_maintenance', 
        'not_rentable', 'occupied_renewal'
    )),
    available_date DATE,
    current_tenant_id UUID,
    current_lease_id UUID,
    lease_start_date DATE,
    lease_end_date DATE,
    last_inspection_date DATE,
    next_inspection_date DATE,
    condition_rating INTEGER CHECK (condition_rating BETWEEN 1 AND 5),
    maintenance_notes TEXT,
    primary_image_url VARCHAR(500),
    floor_plan_url VARCHAR(500),
    images 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),
    updated_by UUID REFERENCES users(id),
    UNIQUE(property_id, unit_number)
);
CREATE TABLE property_documents (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    property_id UUID REFERENCES properties(id) ON DELETE CASCADE,
    unit_id UUID REFERENCES units(id) ON DELETE CASCADE,
    tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    
    document_type VARCHAR(100) CHECK (document_type IN (
        'title_deed', 'insurance', 'inspection_report', 'property_tax',
        'utility_bill', 'contract', 'photo', 'other'
    )),
    title VARCHAR(255) NOT NULL,
    description TEXT,
    file_name VARCHAR(255) NOT NULL,
    file_url VARCHAR(500) NOT NULL,
    file_size INTEGER,
    mime_type VARCHAR(100),
    is_verified BOOLEAN DEFAULT FALSE,
    expiry_date DATE,
    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 '{}',
    uploaded_by UUID REFERENCES users(id)
);
CREATE TABLE property_expenses (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    property_id UUID NOT NULL REFERENCES properties(id) ON DELETE CASCADE,
    tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    
    expense_type VARCHAR(100) CHECK (expense_type IN (
        'maintenance', 'repair', 'utility', 'tax', 'insurance',
        'management_fee', 'cleaning', 'security', 'landscaping', 'other'
    )),
    description TEXT NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    vendor_name VARCHAR(255),
    vendor_contact VARCHAR(255),
    invoice_number VARCHAR(100),
    expense_date DATE NOT NULL,
    due_date DATE,
    paid_date DATE,
    status VARCHAR(50) DEFAULT 'pending' CHECK (status IN (
        'pending', 'approved', 'paid', 'cancelled', 'disputed'
    )),
    receipt_url VARCHAR(500),
    attachments JSONB DEFAULT '[]',
    approved_by UUID REFERENCES users(id),
    approved_at TIMESTAMP WITH TIME ZONE,
    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 INDEX idx_properties_tenant ON properties(tenant_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_properties_type ON properties(property_type) WHERE deleted_at IS NULL;
CREATE INDEX idx_properties_status ON properties(status) WHERE deleted_at IS NULL;
CREATE INDEX idx_properties_manager ON properties(manager_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_properties_agent ON properties(agent_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_units_property ON units(property_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_units_tenant ON units(tenant_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_units_status ON units(status) WHERE deleted_at IS NULL;
CREATE INDEX idx_units_type ON units(unit_type) WHERE deleted_at IS NULL;
CREATE INDEX idx_units_number ON units(unit_number);
CREATE INDEX idx_units_tenant_current ON units(current_tenant_id);
CREATE INDEX idx_property_docs_property ON property_documents(property_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_property_docs_unit ON property_documents(unit_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_property_docs_type ON property_documents(document_type) WHERE deleted_at IS NULL;
CREATE INDEX idx_property_expenses_property ON property_expenses(property_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_property_expenses_type ON property_expenses(expense_type);
CREATE INDEX idx_property_expenses_date ON property_expenses(expense_date);
CREATE INDEX idx_property_expenses_status ON property_expenses(status);
CREATE TRIGGER update_properties_timestamp BEFORE UPDATE ON properties
    FOR EACH ROW EXECUTE FUNCTION update_timestamp();

CREATE TRIGGER update_units_timestamp BEFORE UPDATE ON units
    FOR EACH ROW EXECUTE FUNCTION update_timestamp();

CREATE TRIGGER update_property_documents_timestamp BEFORE UPDATE ON property_documents
    FOR EACH ROW EXECUTE FUNCTION update_timestamp();

CREATE TRIGGER update_property_expenses_timestamp BEFORE UPDATE ON property_expenses
    FOR EACH ROW EXECUTE FUNCTION update_timestamp();
