-- System configuration and metadata
public.system_configuration
public.tenant_registry
public.feature_flags
public.audit_logs
public.scheduled_jobs

-- User authentication (references tenant-specific user data)
public.auth_users
public.auth_sessions
public.auth_tokens
public.role_permissions
public.api_keys

-- org_123.organizations
CREATE TABLE organizations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL,
    display_name VARCHAR(255),
    domain VARCHAR(100),
    subscription_plan VARCHAR(50),
    max_properties INTEGER DEFAULT 10,
    max_users INTEGER DEFAULT 50,
    settings JSONB DEFAULT '{}',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- org_123.users
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID NOT NULL REFERENCES organizations(id),
    email VARCHAR(255) UNIQUE NOT NULL,
    phone_number VARCHAR(20),
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    role VARCHAR(50) NOT NULL,
    status VARCHAR(20) DEFAULT 'active',
    preferences JSONB DEFAULT '{}',
    last_login_at TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- org_123.properties
CREATE TABLE properties (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID NOT NULL REFERENCES organizations(id),
    name VARCHAR(255) NOT NULL,
    type VARCHAR(50) NOT NULL, -- 'residential', 'commercial', 'industrial'
    address JSONB NOT NULL, -- Full address object
    total_units INTEGER DEFAULT 0,
    property_manager_id UUID REFERENCES users(id),
    financial_settings JSONB DEFAULT '{}',
    amenities TEXT[],
    policies JSONB DEFAULT '{}',
    status VARCHAR(20) DEFAULT 'active',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- org_123.buildings (for multi-building properties)
CREATE TABLE buildings (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    property_id UUID NOT NULL REFERENCES properties(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    floors INTEGER DEFAULT 1,
    units_per_floor INTEGER DEFAULT 1,
    amenities TEXT[],
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- org_123.units
CREATE TABLE units (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    property_id UUID NOT NULL REFERENCES properties(id) ON DELETE CASCADE,
    building_id UUID REFERENCES buildings(id) ON DELETE CASCADE,
    unit_number VARCHAR(50) NOT NULL,
    floor INTEGER,
    type VARCHAR(50), -- 'studio', '1br', '2br', etc.
    square_feet DECIMAL(8,2),
    bedrooms INTEGER DEFAULT 0,
    bathrooms DECIMAL(3,1) DEFAULT 0,
    rent_amount DECIMAL(10,2),
    status VARCHAR(20) DEFAULT 'vacant', -- 'vacant', 'occupied', 'maintenance'
    amenities TEXT[],
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

    UNIQUE(property_id, unit_number)
);

-- org_123.tenants
CREATE TABLE tenants (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID REFERENCES users(id),
    property_id UUID NOT NULL REFERENCES properties(id),
    unit_id UUID REFERENCES units(id),
    emergency_contact JSONB,
    employment_info JSONB,
    references JSONB,
    background_check_status VARCHAR(20),
    move_in_date DATE,
    status VARCHAR(20) DEFAULT 'active',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- org_123.leases
CREATE TABLE leases (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants(id),
    unit_id UUID NOT NULL REFERENCES units(id),
    lease_type VARCHAR(20) DEFAULT 'fixed', -- 'fixed', 'month-to-month'
    start_date DATE NOT NULL,
    end_date DATE,
    rent_amount DECIMAL(10,2) NOT NULL,
    security_deposit DECIMAL(10,2),
    late_fee_amount DECIMAL(10,2) DEFAULT 0,
    lease_terms JSONB DEFAULT '{}',
    status VARCHAR(20) DEFAULT 'active',
    signed_at TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- org_123.accounts
CREATE TABLE accounts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    account_code VARCHAR(20) UNIQUE NOT NULL,
    account_name VARCHAR(255) NOT NULL,
    account_type VARCHAR(20) NOT NULL, -- 'asset', 'liability', 'income', 'expense'
    parent_account_id UUID REFERENCES accounts(id),
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- org_123.transactions
CREATE TABLE transactions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    property_id UUID REFERENCES properties(id),
    unit_id UUID REFERENCES units(id),
    tenant_id UUID REFERENCES tenants(id),
    transaction_type VARCHAR(20) NOT NULL, -- 'income', 'expense'
    account_id UUID NOT NULL REFERENCES accounts(id),
    amount DECIMAL(12,2) NOT NULL,
    description TEXT,
    transaction_date DATE NOT NULL,
    due_date DATE,
    payment_method VARCHAR(50),
    reference_number VARCHAR(100),
    status VARCHAR(20) DEFAULT 'pending',
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- org_123.work_orders
CREATE TABLE work_orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    property_id UUID NOT NULL REFERENCES properties(id),
    unit_id UUID REFERENCES units(id),
    tenant_id UUID REFERENCES tenants(id),
    created_by_user_id UUID NOT NULL REFERENCES users(id),
    assigned_to_user_id UUID REFERENCES users(id),
    title VARCHAR(255) NOT NULL,
    description TEXT,
    category VARCHAR(50), -- 'plumbing', 'electrical', 'hvac', etc.
    priority VARCHAR(20) DEFAULT 'medium', -- 'low', 'medium', 'high', 'urgent'
    status VARCHAR(20) DEFAULT 'open',
    estimated_cost DECIMAL(10,2),
    actual_cost DECIMAL(10,2),
    scheduled_date TIMESTAMP WITH TIME ZONE,
    completed_date TIMESTAMP WITH TIME ZONE,
    tenant_accessible BOOLEAN DEFAULT true,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- org_123.messages
CREATE TABLE messages (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    sender_id UUID NOT NULL REFERENCES users(id),
    recipient_type VARCHAR(20) NOT NULL, -- 'user', 'property', 'unit'
    recipient_id UUID NOT NULL,
    subject VARCHAR(255),
    content TEXT NOT NULL,
    message_type VARCHAR(20) DEFAULT 'message', -- 'message', 'announcement', 'alert'
    priority VARCHAR(20) DEFAULT 'normal',
    read_at TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Property-based queries (most common)
CREATE INDEX idx_units_property_status ON org_123.units(property_id, status);
CREATE INDEX idx_tenants_property_status ON org_123.tenants(property_id, status);
CREATE INDEX idx_transactions_property_date ON org_123.transactions(property_id, transaction_date DESC);
CREATE INDEX idx_work_orders_property_status ON org_123.work_orders(property_id, status);

-- User-based queries
CREATE INDEX idx_users_org_role ON org_123.users(organization_id, role);
CREATE INDEX idx_users_email ON org_123.users(email);

-- Time-based queries for reporting
CREATE INDEX idx_transactions_date_type ON org_123.transactions(transaction_date DESC, transaction_type);
CREATE INDEX idx_work_orders_created_status ON org_123.work_orders(created_at DESC, status);

-- Search and filtering
CREATE INDEX idx_properties_name_gin ON org_123.properties USING gin(to_tsvector('english', name));
CREATE INDEX idx_units_number ON org_123.units(unit_number);

-- Financial reporting
CREATE INDEX idx_transactions_property_date_type_status 
ON org_123.transactions(property_id, transaction_date, transaction_type, status);

-- Maintenance dashboard
CREATE INDEX idx_work_orders_assigned_status_priority 
ON org_123.work_orders(assigned_to_user_id, status, priority);

-- Tenant management
CREATE INDEX idx_leases_unit_dates ON org_123.leases(unit_id, start_date, end_date);

-- Enable RLS on all tenant tables
ALTER TABLE org_123.properties ENABLE ROW LEVEL SECURITY;
ALTER TABLE org_123.units ENABLE ROW LEVEL SECURITY;
ALTER TABLE org_123.tenants ENABLE ROW LEVEL SECURITY;

-- Example RLS policy for property managers
CREATE POLICY property_manager_access ON org_123.properties
FOR ALL TO property_manager_role
USING (property_manager_id = current_user_id());

-- Tenant can only see their own data
CREATE POLICY tenant_own_data ON org_123.tenants
FOR ALL TO tenant_role
USING (user_id = current_user_id());

-- org_123.audit_log
CREATE TABLE audit_log (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    table_name VARCHAR(100) NOT NULL,
    record_id UUID NOT NULL,
    action VARCHAR(20) NOT NULL, -- 'INSERT', 'UPDATE', 'DELETE'
    old_values JSONB,
    new_values JSONB,
    user_id UUID,
    ip_address INET,
    user_agent TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Audit trigger function
CREATE OR REPLACE FUNCTION audit_trigger() RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO audit_log(table_name, record_id, action, new_values, user_id)
        VALUES (TG_TABLE_NAME, NEW.id, TG_OP, to_jsonb(NEW), current_user_id());
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO audit_log(table_name, record_id, action, old_values, new_values, user_id)
        VALUES (TG_TABLE_NAME, NEW.id, TG_OP, to_jsonb(OLD), to_jsonb(NEW), current_user_id());
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO audit_log(table_name, record_id, action, old_values, user_id)
        VALUES (TG_TABLE_NAME, OLD.id, TG_OP, to_jsonb(OLD), current_user_id());
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- Archive old transactions (>2 years)
CREATE TABLE org_123.transactions_archive (LIKE org_123.transactions);

-- Automated archival function
CREATE OR REPLACE FUNCTION archive_old_transactions() RETURNS void AS $$
BEGIN
    INSERT INTO org_123.transactions_archive
    SELECT * FROM org_123.transactions 
    WHERE created_at < NOW() - INTERVAL '2 years';

    DELETE FROM org_123.transactions 
    WHERE created_at < NOW() - INTERVAL '2 years';
END;
$$ LANGUAGE plpgsql;

-- Schema migration template
-- V001__initial_schema.sql
CREATE SCHEMA IF NOT EXISTS org_${tenant_id};

-- Create tables in correct order (dependencies first)
-- 1. Organizations
-- 2. Users  
-- 3. Properties
-- 4. Buildings & Units
-- 5. Tenants & Leases
-- 6. Transactions
-- 7. Work Orders
-- 8. Messages

-- Create indexes
-- Create triggers
-- Insert default data

-- Export tenant data
COPY (
    SELECT row_to_json(t) FROM (
        SELECT * FROM org_123.properties
        UNION ALL
        SELECT * FROM org_123.units
        -- ... all tables
    ) t
) TO '/path/to/export.json';

-- Import tenant data with validation
CREATE OR REPLACE FUNCTION import_tenant_data(data JSONB) 
RETURNS void AS $$
-- Validation and import logic
$$ LANGUAGE plpgsql;

-- Find slow queries
SELECT query, mean_time, calls, total_time 
FROM pg_stat_statements 
WHERE mean_time > 100 
ORDER BY mean_time DESC;

-- Check index usage
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
WHERE idx_scan < 100
ORDER BY idx_scan;

-- Monitor connection usage
SELECT datname, usename, application_name, state, count(*)
FROM pg_stat_activity
WHERE state = 'active'
GROUP BY datname, usename, application_name, state;

-- Set organization context for session
SET application_name = 'property_management';
SET row_security = on;
SELECT set_config('app.current_organization_id', '123e4567-e89b-12d3-a456-426614174000', true);

-- Enable RLS on all tenant-specific tables
ALTER TABLE core.properties ENABLE ROW LEVEL SECURITY;
ALTER TABLE core.units ENABLE ROW LEVEL SECURITY;
ALTER TABLE financial.invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE operations.maintenance_requests ENABLE ROW LEVEL SECURITY;

-- Organization isolation policy (applied to all tenant tables)
CREATE OR REPLACE FUNCTION current_organization_id() RETURNS UUID AS $$
BEGIN
    RETURN COALESCE(
        current_setting('app.current_organization_id', true)::UUID,
        '00000000-0000-0000-0000-000000000000'::UUID
    );
END;
$$ LANGUAGE plpgsql STABLE SECURITY DEFINER;

-- Example RLS policy for properties
CREATE POLICY organization_isolation ON core.properties
FOR ALL
USING (organization_id = current_organization_id());

-- Property manager access policy
CREATE POLICY property_manager_access ON core.properties
FOR ALL TO property_manager_role
USING (
    organization_id = current_organization_id() AND
    (property_manager_id = auth.uid() OR 
     auth.uid() IN (
         SELECT user_id FROM core.organization_members 
         WHERE organization_id = current_organization_id() 
         AND role IN ('admin', 'super_admin')
     ))
);

-- Tenant access policy
CREATE POLICY tenant_access ON core.properties
FOR SELECT TO tenant_role
USING (
    organization_id = current_organization_id() AND
    id IN (
        SELECT property_id FROM core.units u
        JOIN core.tenants t ON u.id = t.unit_id
        WHERE t.user_id = auth.uid()
    )
);

-- Create identity schema
CREATE SCHEMA IF NOT EXISTS identity;

-- User profiles (core identity information)
CREATE TABLE identity.user_profiles (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    external_auth_id VARCHAR(255) UNIQUE, -- OAuth provider ID
    email VARCHAR(255) UNIQUE NOT NULL,
    email_verified BOOLEAN DEFAULT false,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    phone_number VARCHAR(20),
    phone_verified BOOLEAN DEFAULT false,
    profile_image_url TEXT,
    language_preference VARCHAR(10) DEFAULT 'en',
    timezone VARCHAR(50) DEFAULT 'UTC',
    mfa_enabled BOOLEAN DEFAULT false,
    mfa_secret VARCHAR(255),
    last_login_at TIMESTAMPTZ,
    login_count INTEGER DEFAULT 0,
    status VARCHAR(20) DEFAULT 'active', -- active, inactive, suspended, banned
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Authentication sessions
CREATE TABLE identity.auth_sessions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES identity.user_profiles(id) ON DELETE CASCADE,
    token_hash VARCHAR(255) NOT NULL,
    refresh_token_hash VARCHAR(255),
    device_id VARCHAR(255),
    device_name VARCHAR(255),
    ip_address INET,
    user_agent TEXT,
    expires_at TIMESTAMPTZ NOT NULL,
    last_activity_at TIMESTAMPTZ DEFAULT NOW(),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- User roles and permissions
CREATE TABLE identity.user_roles (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES identity.user_profiles(id) ON DELETE CASCADE,
    entity_id UUID NOT NULL, -- organization_id, property_id, etc.
    entity_type VARCHAR(50) NOT NULL, -- organization, property, unit
    role_name VARCHAR(50) NOT NULL, -- admin, property_manager, tenant, etc.
    permissions JSONB DEFAULT '[]', -- array of permission strings
    granted_by UUID REFERENCES identity.user_profiles(id),
    granted_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    expires_at TIMESTAMPTZ,
    is_active BOOLEAN DEFAULT true,

    UNIQUE(user_id, entity_id, entity_type, role_name)
);

-- OAuth provider configurations
CREATE TABLE identity.oauth_providers (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    provider_name VARCHAR(50) NOT NULL UNIQUE, -- google, microsoft, azure_ad
    client_id VARCHAR(255) NOT NULL,
    client_secret VARCHAR(255) NOT NULL, -- encrypted
    authorization_url TEXT NOT NULL,
    token_url TEXT NOT NULL,
    user_info_url TEXT NOT NULL,
    scopes TEXT[] DEFAULT ARRAY['openid', 'profile', 'email'],
    is_enabled BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- API keys for service-to-service authentication
CREATE TABLE identity.api_keys (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL,
    key_hash VARCHAR(255) NOT NULL UNIQUE,
    organization_id UUID, -- NULL for system-wide keys
    permissions JSONB DEFAULT '[]',
    expires_at TIMESTAMPTZ,
    last_used_at TIMESTAMPTZ,
    is_active BOOLEAN DEFAULT true,
    created_by UUID REFERENCES identity.user_profiles(id),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Create indexes for identity schema
CREATE INDEX idx_user_profiles_email ON identity.user_profiles(email);
CREATE INDEX idx_user_profiles_external_auth ON identity.user_profiles(external_auth_id);
CREATE INDEX idx_user_profiles_status ON identity.user_profiles(status);
CREATE INDEX idx_auth_sessions_user_id ON identity.auth_sessions(user_id);
CREATE INDEX idx_auth_sessions_token ON identity.auth_sessions(token_hash);
CREATE INDEX idx_auth_sessions_expires ON identity.auth_sessions(expires_at);
CREATE INDEX idx_user_roles_user_entity ON identity.user_roles(user_id, entity_id, entity_type);
CREATE INDEX idx_user_roles_entity ON identity.user_roles(entity_id, entity_type);
CREATE INDEX idx_api_keys_hash ON identity.api_keys(key_hash);
CREATE INDEX idx_api_keys_org ON identity.api_keys(organization_id);

-- Create core schema
CREATE SCHEMA IF NOT EXISTS core;

-- Organizations (multi-tenant isolation)
CREATE TABLE core.organizations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL,
    display_name VARCHAR(255),
    slug VARCHAR(100) UNIQUE NOT NULL, -- URL-friendly identifier
    domain VARCHAR(100), -- Custom domain for organization
    logo_url TEXT,
    primary_color VARCHAR(7), -- Hex color for branding
    subscription_plan VARCHAR(50) DEFAULT 'basic', -- basic, professional, enterprise
    subscription_status VARCHAR(20) DEFAULT 'active', -- active, suspended, cancelled
    max_properties INTEGER DEFAULT 10,
    max_users INTEGER DEFAULT 50,
    max_storage_gb INTEGER DEFAULT 10,
    billing_email VARCHAR(255),
    support_email VARCHAR(255),
    phone_number VARCHAR(20),
    address JSONB, -- Full address object
    timezone VARCHAR(50) DEFAULT 'UTC',
    currency VARCHAR(3) DEFAULT 'USD',
    language VARCHAR(10) DEFAULT 'en',
    settings JSONB DEFAULT '{}', -- Organization-specific settings
    onboarding_completed BOOLEAN DEFAULT false,
    trial_ends_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- User profiles within organizations
CREATE TABLE core.user_profiles (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    identity_user_id UUID NOT NULL REFERENCES identity.user_profiles(id),
    organization_id UUID NOT NULL REFERENCES core.organizations(id) ON DELETE CASCADE,
    employee_id VARCHAR(50), -- Organization-specific employee ID
    department VARCHAR(100),
    job_title VARCHAR(100),
    manager_id UUID REFERENCES core.user_profiles(id),
    hire_date DATE,
    emergency_contact JSONB, -- Name, phone, relationship
    work_location VARCHAR(255),
    skills TEXT[], -- Array of skills
    certifications JSONB, -- Certification details
    preferences JSONB DEFAULT '{}', -- User preferences within org
    status VARCHAR(20) DEFAULT 'active', -- active, inactive, on_leave
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    UNIQUE(identity_user_id, organization_id)
);

-- Organization membership tracking
CREATE TABLE core.organization_members (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID NOT NULL REFERENCES core.organizations(id) ON DELETE CASCADE,
    user_profile_id UUID NOT NULL REFERENCES core.user_profiles(id) ON DELETE CASCADE,
    role VARCHAR(50) NOT NULL, -- admin, manager, staff, readonly
    invited_by UUID REFERENCES core.user_profiles(id),
    invited_at TIMESTAMPTZ,
    joined_at TIMESTAMPTZ DEFAULT NOW(),
    status VARCHAR(20) DEFAULT 'active', -- pending, active, inactive
    permissions JSONB DEFAULT '[]',

    UNIQUE(organization_id, user_profile_id)
);

-- Properties
CREATE TABLE core.properties (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID NOT NULL REFERENCES core.organizations(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    property_type VARCHAR(50) NOT NULL, -- residential, commercial, industrial, mixed
    subtype VARCHAR(50), -- apartment, condo, house, office, retail, etc.
    status VARCHAR(20) DEFAULT 'active', -- active, inactive, under_construction, sold

    -- Address information
    address_line1 VARCHAR(255) NOT NULL,
    address_line2 VARCHAR(255),
    city VARCHAR(100) NOT NULL,
    state_province VARCHAR(100) NOT NULL,
    postal_code VARCHAR(20),
    country VARCHAR(2) NOT NULL, -- ISO country code
    latitude DECIMAL(10, 7),
    longitude DECIMAL(10, 7),

    -- Property details
    year_built INTEGER,
    total_area_sqft DECIMAL(10, 2),
    lot_size_sqft DECIMAL(10, 2),
    total_units INTEGER DEFAULT 0,
    total_floors INTEGER DEFAULT 1,
    parking_spaces INTEGER DEFAULT 0,

    -- Management information
    property_manager_id UUID REFERENCES core.user_profiles(id),
    acquisition_date DATE,
    acquisition_cost DECIMAL(15, 2),
    current_market_value DECIMAL(15, 2),
    annual_property_tax DECIMAL(10, 2),
    annual_insurance DECIMAL(10, 2),

    -- Features and amenities
    amenities TEXT[], -- Array of amenities
    features TEXT[], -- Property features
    appliances TEXT[], -- Included appliances

    -- Policy and rules
    pet_policy JSONB, -- Pet rules and restrictions
    smoking_policy VARCHAR(20) DEFAULT 'no_smoking',
    guest_policy JSONB, -- Guest and visitor policies

    -- Financial settings
    late_fee_amount DECIMAL(8, 2) DEFAULT 0,
    late_fee_percentage DECIMAL(5, 2) DEFAULT 0,
    grace_period_days INTEGER DEFAULT 5,

    -- Media and documents
    images TEXT[], -- Array of image URLs
    documents TEXT[], -- Array of document URLs
    virtual_tour_url TEXT,

    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Buildings (for multi-building properties)
CREATE TABLE core.buildings (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    property_id UUID NOT NULL REFERENCES core.properties(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    building_number VARCHAR(20),
    address VARCHAR(500), -- If different from main property
    floors INTEGER DEFAULT 1,
    units_per_floor INTEGER DEFAULT 1,
    total_units INTEGER DEFAULT 0,
    elevator_count INTEGER DEFAULT 0,
    stairwell_count INTEGER DEFAULT 1,
    amenities TEXT[],
    year_built INTEGER,
    construction_type VARCHAR(50),
    heating_type VARCHAR(50),
    cooling_type VARCHAR(50),
    roof_type VARCHAR(50),
    exterior_material VARCHAR(50),
    images TEXT[],
    floor_plans TEXT[], -- Array of floor plan URLs
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    UNIQUE(property_id, building_number)
);

-- Units
CREATE TABLE core.units (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    property_id UUID NOT NULL REFERENCES core.properties(id) ON DELETE CASCADE,
    building_id UUID REFERENCES core.buildings(id) ON DELETE CASCADE,
    unit_number VARCHAR(50) NOT NULL,
    unit_type VARCHAR(50), -- studio, 1br, 2br, 3br, commercial, etc.
    floor INTEGER,
    square_feet DECIMAL(8, 2),
    bedrooms INTEGER DEFAULT 0,
    bathrooms DECIMAL(3, 1) DEFAULT 0,
    half_baths INTEGER DEFAULT 0,

    -- Rental information
    market_rent DECIMAL(10, 2),
    current_rent DECIMAL(10, 2),
    security_deposit DECIMAL(10, 2),
    pet_deposit DECIMAL(8, 2),

    -- Unit status
    status VARCHAR(20) DEFAULT 'vacant', -- vacant, occupied, maintenance, unavailable
    availability_date DATE,
    lease_start_date DATE,
    lease_end_date DATE,

    -- Unit features
    features TEXT[], -- Unit-specific features
    appliances TEXT[], -- Included appliances
    flooring_type VARCHAR(50),
    view_type VARCHAR(50),
    balcony_patio BOOLEAN DEFAULT false,
    fireplace BOOLEAN DEFAULT false,
    in_unit_laundry BOOLEAN DEFAULT false,

    -- Utilities
    utilities_included TEXT[], -- Heat, electric, water, etc.
    utility_allowance DECIMAL(8, 2),

    -- Parking
    assigned_parking_spaces INTEGER DEFAULT 0,
    parking_space_numbers TEXT[],
    garage_spaces INTEGER DEFAULT 0,

    -- Media
    images TEXT[],
    floor_plan_url TEXT,
    virtual_tour_url TEXT,

    -- Maintenance
    last_renovated DATE,
    condition_rating INTEGER CHECK (condition_rating >= 1 AND condition_rating <= 5),
    maintenance_notes TEXT,

    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    UNIQUE(property_id, unit_number)
);

-- Tenants
CREATE TABLE core.tenants (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID NOT NULL REFERENCES core.organizations(id) ON DELETE CASCADE,
    user_profile_id UUID REFERENCES core.user_profiles(id), -- If tenant has system access
    property_id UUID NOT NULL REFERENCES core.properties(id),
    unit_id UUID REFERENCES core.units(id),

    -- Personal information (for non-system users)
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(255),
    phone_primary VARCHAR(20),
    phone_secondary VARCHAR(20),
    date_of_birth DATE,

    -- Emergency contacts
    emergency_contact_1 JSONB, -- name, relationship, phone, email
    emergency_contact_2 JSONB,

    -- Employment information
    employer_name VARCHAR(255),
    job_title VARCHAR(100),
    annual_income DECIMAL(12, 2),
    employment_verification JSONB, -- verification details

    -- Previous rental history
    previous_address JSONB,
    previous_landlord JSONB,
    rental_history JSONB, -- Array of previous rentals

    -- Tenant status
    status VARCHAR(20) DEFAULT 'active', -- prospect, approved, active, notice_given, moved_out
    move_in_date DATE,
    move_out_date DATE,
    notice_date DATE,

    -- Background check and screening
    credit_score INTEGER,
    background_check_status VARCHAR(20), -- pending, passed, failed, expired
    background_check_date DATE,
    screening_notes TEXT,

    -- Preferences and notes
    communication_preference VARCHAR(20) DEFAULT 'email', -- email, sms, phone, mail
    language_preference VARCHAR(10) DEFAULT 'en',
    special_needs TEXT,
    notes TEXT,

    -- Pets
    pets JSONB, -- Array of pet information

    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Leases
CREATE TABLE core.leases (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID NOT NULL REFERENCES core.organizations(id) ON DELETE CASCADE,
    tenant_id UUID NOT NULL REFERENCES core.tenants(id),
    unit_id UUID NOT NULL REFERENCES core.units(id),
    property_id UUID NOT NULL REFERENCES core.properties(id),

    -- Lease terms
    lease_type VARCHAR(20) DEFAULT 'fixed', -- fixed, month_to_month, weekly
    start_date DATE NOT NULL,
    end_date DATE,
    rent_amount DECIMAL(10, 2) NOT NULL,
    rent_due_day INTEGER DEFAULT 1, -- Day of month rent is due

    -- Deposits and fees
    security_deposit DECIMAL(10, 2) DEFAULT 0,
    pet_deposit DECIMAL(8, 2) DEFAULT 0,
    cleaning_deposit DECIMAL(8, 2) DEFAULT 0,
    key_deposit DECIMAL(6, 2) DEFAULT 0,

    -- Late fees
    late_fee_amount DECIMAL(8, 2) DEFAULT 0,
    late_fee_percentage DECIMAL(5, 2) DEFAULT 0,
    grace_period_days INTEGER DEFAULT 5,

    -- Lease clauses and terms
    lease_terms JSONB DEFAULT '{}', -- Custom lease terms
    utilities_included TEXT[],
    pets_allowed BOOLEAN DEFAULT false,
    smoking_allowed BOOLEAN DEFAULT false,
    subletting_allowed BOOLEAN DEFAULT false,
    modifications_allowed BOOLEAN DEFAULT false,

    -- Lease status and tracking
    status VARCHAR(20) DEFAULT 'draft', -- draft, active, expired, terminated, renewed
    signed_by_tenant_at TIMESTAMPTZ,
    signed_by_landlord_at TIMESTAMPTZ,
    move_in_inspection_id UUID,
    move_out_inspection_id UUID,

    -- Renewal information
    auto_renew BOOLEAN DEFAULT false,
    renewal_notice_days INTEGER DEFAULT 60,
    rent_increase_percentage DECIMAL(5, 2) DEFAULT 0,

    -- Documents
    lease_document_url TEXT,
    signed_lease_url TEXT,
    addendums TEXT[], -- Array of addendum URLs

    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Create indexes for core schema
CREATE INDEX idx_organizations_slug ON core.organizations(slug);
CREATE INDEX idx_organizations_status ON core.organizations(subscription_status);
CREATE INDEX idx_user_profiles_org ON core.user_profiles(organization_id);
CREATE INDEX idx_user_profiles_identity ON core.user_profiles(identity_user_id);
CREATE INDEX idx_organization_members_org ON core.organization_members(organization_id);
CREATE INDEX idx_properties_org ON core.properties(organization_id);
CREATE INDEX idx_properties_manager ON core.properties(property_manager_id);
CREATE INDEX idx_properties_type ON core.properties(property_type, status);
CREATE INDEX idx_properties_location ON core.properties(city, state_province, country);
CREATE INDEX idx_buildings_property ON core.buildings(property_id);
CREATE INDEX idx_units_property ON core.units(property_id);
CREATE INDEX idx_units_building ON core.units(building_id);
CREATE INDEX idx_units_status ON core.units(status);
CREATE INDEX idx_units_availability ON core.units(availability_date) WHERE status = 'vacant';
CREATE INDEX idx_tenants_org ON core.tenants(organization_id);
CREATE INDEX idx_tenants_property ON core.tenants(property_id);
CREATE INDEX idx_tenants_unit ON core.tenants(unit_id);
CREATE INDEX idx_tenants_status ON core.tenants(status);
CREATE INDEX idx_tenants_email ON core.tenants(email);
CREATE INDEX idx_leases_org ON core.leases(organization_id);
CREATE INDEX idx_leases_tenant ON core.leases(tenant_id);
CREATE INDEX idx_leases_unit ON core.leases(unit_id);
CREATE INDEX idx_leases_dates ON core.leases(start_date, end_date);
CREATE INDEX idx_leases_status ON core.leases(status);