CREATE TABLE tenants_table (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    organization_tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(255),
    phone VARCHAR(20) NOT NULL,
    alternative_phone VARCHAR(20),
    id_type VARCHAR(50) CHECK (id_type IN ('national_id', 'passport', 'drivers_license', 'other')),
    id_number VARCHAR(100),
    date_of_birth DATE,
    nationality VARCHAR(100),
    employer VARCHAR(255),
    job_title VARCHAR(255),
    employment_status VARCHAR(100) CHECK (employment_status IN (
        'employed', 'self_employed', 'unemployed', 'student', 'retired', 'other'
    )),
    monthly_income DECIMAL(10, 2),
    emergency_contact_name VARCHAR(255),
    emergency_contact_phone VARCHAR(20),
    emergency_contact_relation VARCHAR(100),
    current_address_line1 VARCHAR(255),
    current_address_line2 VARCHAR(255),
    current_city VARCHAR(100),
    current_state VARCHAR(100),
    current_postal_code VARCHAR(20),
    background_check_status VARCHAR(50) DEFAULT 'pending' CHECK (background_check_status IN (
        'pending', 'in_progress', 'approved', 'rejected', 'expired'
    )),
    background_check_date DATE,
    credit_score INTEGER,
    criminal_record BOOLEAN,
    eviction_history BOOLEAN,
    status VARCHAR(50) DEFAULT 'active' CHECK (status IN (
        'active', 'inactive', 'blacklisted', 'moved_out'
    )),
    documents JSONB DEFAULT '[]',
    preferred_move_in_date DATE,
    preferred_lease_term INTEGER, 
    pets_allowed BOOLEAN DEFAULT FALSE,
    special_requirements TEXT,
    communication_preference VARCHAR(50) DEFAULT 'email' CHECK (communication_preference IN (
        'email', 'sms', 'phone', 'whatsapp'
    )),
    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 tenant_references (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    tenant_id UUID NOT NULL REFERENCES tenants_table(id) ON DELETE CASCADE,
    
    reference_type VARCHAR(50) CHECK (reference_type IN (
        'previous_landlord', 'employer', 'personal', 'professional', 'other'
    )),
    name VARCHAR(255) NOT NULL,
    phone VARCHAR(20),
    email VARCHAR(255),
    relationship VARCHAR(100),
    company VARCHAR(255),
    feedback TEXT,
    rating INTEGER CHECK (rating BETWEEN 1 AND 5),
    verified BOOLEAN DEFAULT FALSE,
    verified_at TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    metadata JSONB DEFAULT '{}'
);
CREATE TABLE tenant_notes (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    tenant_id UUID NOT NULL REFERENCES tenants_table(id) ON DELETE CASCADE,
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    
    note_type VARCHAR(50) CHECK (note_type IN (
        'general', 'payment', 'behavior', 'maintenance', 'lease', 'other'
    )),
    note TEXT NOT NULL,
    is_private BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    metadata JSONB DEFAULT '{}'
);
CREATE INDEX idx_tenants_org ON tenants_table(organization_tenant_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_tenants_name ON tenants_table(last_name, first_name) WHERE deleted_at IS NULL;
CREATE INDEX idx_tenants_email ON tenants_table(email) WHERE deleted_at IS NULL;
CREATE INDEX idx_tenants_phone ON tenants_table(phone) WHERE deleted_at IS NULL;
CREATE INDEX idx_tenants_status ON tenants_table(status) WHERE deleted_at IS NULL;
CREATE INDEX idx_tenants_background ON tenants_table(background_check_status);
CREATE INDEX idx_tenant_references_tenant ON tenant_references(tenant_id);
CREATE INDEX idx_tenant_references_type ON tenant_references(reference_type);
CREATE INDEX idx_tenant_notes_tenant ON tenant_notes(tenant_id);
CREATE INDEX idx_tenant_notes_user ON tenant_notes(user_id);
CREATE INDEX idx_tenant_notes_type ON tenant_notes(note_type);
CREATE TRIGGER update_tenants_table_timestamp BEFORE UPDATE ON tenants_table
    FOR EACH ROW EXECUTE FUNCTION update_timestamp();

CREATE TRIGGER update_tenant_references_timestamp BEFORE UPDATE ON tenant_references
    FOR EACH ROW EXECUTE FUNCTION update_timestamp();

CREATE TRIGGER update_tenant_notes_timestamp BEFORE UPDATE ON tenant_notes
    FOR EACH ROW EXECUTE FUNCTION update_timestamp();