CREATE TABLE documents (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    organization_tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    original_name VARCHAR(255) NOT NULL,
    storage_key VARCHAR(500) NOT NULL,
    mime_type VARCHAR(100),
    size INTEGER,
    category VARCHAR(100) CHECK (category IN (
        'lease', 'invoice', 'receipt', 'maintenance', 'identification',
        'property_photo', 'insurance', 'tax', 'legal', 'general'
    )),
    url VARCHAR(500),
    thumbnails JSONB DEFAULT '[]',
    retention_policy VARCHAR(50) DEFAULT '3_years' CHECK (retention_policy IN (
        '1_year', '3_years', '7_years', '10_years', 'permanent'
    )),
    expiry_date DATE,
    tenant_id UUID REFERENCES tenants_table(id) ON DELETE SET NULL,
    property_id UUID REFERENCES properties(id) ON DELETE SET NULL,
    unit_id UUID REFERENCES units(id) ON DELETE SET NULL,
    lease_id UUID REFERENCES leases(id) ON DELETE SET NULL,
    maintenance_id UUID REFERENCES maintenance_requests(id) ON DELETE SET NULL,
    payment_id UUID REFERENCES payments(id) ON DELETE SET NULL,
    is_verified BOOLEAN DEFAULT FALSE,
    is_public BOOLEAN DEFAULT FALSE,
    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),
    verified_by UUID REFERENCES users(id)
);
CREATE TABLE document_downloads (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    document_id UUID NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
    user_id UUID REFERENCES users(id) ON DELETE SET NULL,
    
    downloaded_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    ip_address VARCHAR(45),
    user_agent TEXT
);
CREATE TABLE notifications (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    organization_tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    recipient_id UUID NOT NULL, 
    recipient_type VARCHAR(50) DEFAULT 'user' CHECK (recipient_type IN ('user', 'tenant')),
    type VARCHAR(100) NOT NULL,
    title VARCHAR(255),
    message TEXT,
    data JSONB DEFAULT '{}',
    channels JSONB DEFAULT '["email"]',
    priority VARCHAR(50) DEFAULT 'normal' CHECK (priority IN (
        'low', 'normal', 'high', 'urgent'
    )),
    status VARCHAR(50) DEFAULT 'pending' CHECK (status IN (
        'pending', 'sent', 'delivered', 'read', 'failed'
    )),
    read_at TIMESTAMP WITH TIME ZONE,
    delivery_results JSONB DEFAULT '{}',
    error_message TEXT,
    retry_count INTEGER DEFAULT 0,
    scheduled_for TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    delivered_at TIMESTAMP WITH TIME ZONE,
    metadata JSONB DEFAULT '{}',
    created_by UUID REFERENCES users(id)
);
CREATE TABLE scheduled_messages (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    organization_tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    
    recipient VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    sender_id VARCHAR(255),
    scheduled_at TIMESTAMP WITH TIME ZONE NOT NULL,
    provider VARCHAR(50) DEFAULT 'twilio',
    
    status VARCHAR(50) DEFAULT 'scheduled' CHECK (status IN (
        'scheduled', 'sent', 'failed', 'cancelled'
    )),
    
    sent_at TIMESTAMP WITH TIME ZONE,
    error_message TEXT,
    
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    
    metadata JSONB DEFAULT '{}'
);
CREATE TABLE message_logs (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    organization_tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    
    message_id VARCHAR(255) UNIQUE,
    message_type VARCHAR(50) CHECK (message_type IN ('email', 'sms', 'push')),
    recipient VARCHAR(255),
    sender VARCHAR(255),
    
    subject VARCHAR(500),
    content TEXT,
    
    status VARCHAR(50) DEFAULT 'sent' CHECK (status IN (
        'queued', 'sent', 'delivered', 'failed', 'bounced', 'opened', 'clicked'
    )),
    status_details JSONB DEFAULT '{}',
    
    provider VARCHAR(50),
    cost DECIMAL(10, 4),
    
    delivered_at TIMESTAMP WITH TIME ZONE,
    opened_at TIMESTAMP WITH TIME ZONE,
    clicked_at TIMESTAMP WITH TIME ZONE,
    
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    
    metadata JSONB DEFAULT '{}'
);
CREATE TABLE communications (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    organization_tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    sender_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    sender_type VARCHAR(50) DEFAULT 'user' CHECK (sender_type IN ('user', 'system', 'tenant')),
    thread_id UUID,
    parent_message_id UUID REFERENCES communications(id) ON DELETE CASCADE,
    subject VARCHAR(500),
    message_type VARCHAR(50) DEFAULT 'general' CHECK (message_type IN (
        'general', 'maintenance', 'payment', 'lease', 'complaint', 'inquiry', 'announcement'
    )),
    body TEXT NOT NULL,
    recipients JSONB DEFAULT '[]',
    cc JSONB DEFAULT '[]',
    bcc JSONB DEFAULT '[]',
    attachments JSONB DEFAULT '[]',
    status VARCHAR(50) DEFAULT 'sent' CHECK (status IN (
        'draft', 'sent', 'delivered', 'read', 'archived'
    )),
    read_by 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 '{}'
);
CREATE TABLE user_devices (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    organization_tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    
    device_token VARCHAR(500) NOT NULL,
    device_type VARCHAR(50) CHECK (device_type IN ('ios', 'android', 'web')),
    device_name VARCHAR(255),
    
    is_active BOOLEAN DEFAULT TRUE,
    last_used_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    
    UNIQUE(user_id, device_token)
);
CREATE TABLE notification_preferences (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    organization_tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    email_enabled BOOLEAN DEFAULT TRUE,
    sms_enabled BOOLEAN DEFAULT TRUE,
    push_enabled BOOLEAN DEFAULT TRUE,
    rent_reminders BOOLEAN DEFAULT TRUE,
    payment_receipts BOOLEAN DEFAULT TRUE,
    maintenance_updates BOOLEAN DEFAULT TRUE,
    lease_notices BOOLEAN DEFAULT TRUE,
    community_announcements BOOLEAN DEFAULT TRUE,
    marketing_emails BOOLEAN DEFAULT FALSE,
    quiet_hours_enabled BOOLEAN DEFAULT FALSE,
    quiet_hours_start TIME,
    quiet_hours_end TIME,
    
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    
    UNIQUE(user_id)
);
CREATE TABLE generated_reports (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    organization_tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    
    report_type VARCHAR(100) NOT NULL CHECK (report_type IN (
        'financial', 'occupancy', 'maintenance', 'tenant', 'payment',
        'expense', 'income', 'tax', 'custom'
    )),
    title VARCHAR(255),
    parameters JSONB DEFAULT '{}',
    data JSONB,
    file_url VARCHAR(500),
    file_format VARCHAR(50) CHECK (file_format IN ('json', 'pdf', 'excel', 'csv')),
    status VARCHAR(50) DEFAULT 'completed' CHECK (status IN (
        'generating', 'completed', 'failed', 'archived'
    )),
    is_scheduled BOOLEAN DEFAULT FALSE,
    schedule_frequency VARCHAR(50),
    next_generation_date TIMESTAMP WITH TIME ZONE,
    
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    generated_by UUID REFERENCES users(id),
    
    metadata JSONB DEFAULT '{}'
);
CREATE INDEX idx_documents_org ON documents(organization_tenant_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_documents_category ON documents(category) WHERE deleted_at IS NULL;
CREATE INDEX idx_documents_tenant ON documents(tenant_id);
CREATE INDEX idx_documents_property ON documents(property_id);
CREATE INDEX idx_documents_unit ON documents(unit_id);
CREATE INDEX idx_documents_lease ON documents(lease_id);
CREATE INDEX idx_documents_storage ON documents(storage_key);
CREATE INDEX idx_doc_downloads_document ON document_downloads(document_id);
CREATE INDEX idx_doc_downloads_user ON document_downloads(user_id);
CREATE INDEX idx_doc_downloads_date ON document_downloads(downloaded_at);
CREATE INDEX idx_notifications_org ON notifications(organization_tenant_id);
CREATE INDEX idx_notifications_recipient ON notifications(recipient_id, recipient_type);
CREATE INDEX idx_notifications_type ON notifications(type);
CREATE INDEX idx_notifications_status ON notifications(status);
CREATE INDEX idx_notifications_created ON notifications(created_at);
CREATE INDEX idx_notifications_scheduled ON notifications(scheduled_for) WHERE status = 'pending';
CREATE INDEX idx_scheduled_msgs_org ON scheduled_messages(organization_tenant_id);
CREATE INDEX idx_scheduled_msgs_status ON scheduled_messages(status);
CREATE INDEX idx_scheduled_msgs_time ON scheduled_messages(scheduled_at);
CREATE INDEX idx_message_logs_org ON message_logs(organization_tenant_id);
CREATE INDEX idx_message_logs_id ON message_logs(message_id);
CREATE INDEX idx_message_logs_type ON message_logs(message_type);
CREATE INDEX idx_message_logs_status ON message_logs(status);
CREATE INDEX idx_communications_org ON communications(organization_tenant_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_communications_sender ON communications(sender_id);
CREATE INDEX idx_communications_thread ON communications(thread_id);
CREATE INDEX idx_communications_type ON communications(message_type);
CREATE INDEX idx_communications_parent ON communications(parent_message_id);
CREATE INDEX idx_user_devices_user ON user_devices(user_id);
CREATE INDEX idx_user_devices_token ON user_devices(device_token);
CREATE INDEX idx_user_devices_active ON user_devices(is_active);
CREATE INDEX idx_notif_prefs_user ON notification_preferences(user_id);
CREATE INDEX idx_notif_prefs_org ON notification_preferences(organization_tenant_id);
CREATE INDEX idx_gen_reports_org ON generated_reports(organization_tenant_id);
CREATE INDEX idx_gen_reports_type ON generated_reports(report_type);
CREATE INDEX idx_gen_reports_status ON generated_reports(status);
CREATE TRIGGER update_documents_timestamp BEFORE UPDATE ON documents
    FOR EACH ROW EXECUTE FUNCTION update_timestamp();

CREATE TRIGGER update_notifications_timestamp BEFORE UPDATE ON notifications
    FOR EACH ROW EXECUTE FUNCTION update_timestamp();

CREATE TRIGGER update_scheduled_msgs_timestamp BEFORE UPDATE ON scheduled_messages
    FOR EACH ROW EXECUTE FUNCTION update_timestamp();

CREATE TRIGGER update_communications_timestamp BEFORE UPDATE ON communications
    FOR EACH ROW EXECUTE FUNCTION update_timestamp();

CREATE TRIGGER update_user_devices_timestamp BEFORE UPDATE ON user_devices
    FOR EACH ROW EXECUTE FUNCTION update_timestamp();

CREATE TRIGGER update_notif_prefs_timestamp BEFORE UPDATE ON notification_preferences
    FOR EACH ROW EXECUTE FUNCTION update_timestamp();

CREATE TRIGGER update_gen_reports_timestamp BEFORE UPDATE ON generated_reports
    FOR EACH ROW EXECUTE FUNCTION update_timestamp();