template-saas-database-v2/ddl/99-additional-fk-indexes.sql

-- =============================================================================
-- INDICES ADICIONALES PARA FOREIGN KEYS
-- Proyecto: template-saas
-- Generado por: TASK-2026-02-03-REMEDIACION-BD (P1)
-- Fecha: 2026-02-03
-- Descripcion: Indices para las 32 FKs que carecian de indice dedicado
-- Referencia: orchestration/tareas/TASK-2026-02-03-VALIDACION-INTEGRAL-MODELADO-BD/
--             entregables/INFORME-INTEGRIDAD-REFERENCIAL.md
-- =============================================================================

-- =============================================================================
-- PRIORIDAD ALTA: Tablas frecuentemente consultadas
-- =============================================================================

-- -----------------------------------------------------------------------------
-- Schema: billing
-- -----------------------------------------------------------------------------

-- FK: billing.subscriptions.plan_id -> plans.plans(id)
CREATE INDEX IF NOT EXISTS idx_subscriptions_plan
ON billing.subscriptions(plan_id);

-- FK: billing.invoices.subscription_id -> billing.subscriptions(id)
CREATE INDEX IF NOT EXISTS idx_invoices_subscription
ON billing.invoices(subscription_id) WHERE subscription_id IS NOT NULL;

-- FK: billing.payments.invoice_id -> billing.invoices(id)
CREATE INDEX IF NOT EXISTS idx_payments_invoice
ON billing.payments(invoice_id) WHERE invoice_id IS NOT NULL;

-- -----------------------------------------------------------------------------
-- Schema: notifications
-- -----------------------------------------------------------------------------

-- FK: notifications.notifications.template_id -> notifications.templates(id)
CREATE INDEX IF NOT EXISTS idx_notifications_template
ON notifications.notifications(template_id) WHERE template_id IS NOT NULL;

-- -----------------------------------------------------------------------------
-- Schema: sales
-- -----------------------------------------------------------------------------

-- FK: sales.leads.created_by -> users.users(id)
CREATE INDEX IF NOT EXISTS idx_leads_created_by
ON sales.leads(created_by) WHERE created_by IS NOT NULL;

-- FK: sales.opportunities.created_by -> users.users(id)
CREATE INDEX IF NOT EXISTS idx_opportunities_created_by
ON sales.opportunities(created_by) WHERE created_by IS NOT NULL;

-- -----------------------------------------------------------------------------
-- Schema: commissions
-- -----------------------------------------------------------------------------

-- FK: commissions.entries.assignment_id -> commissions.assignments(id)
CREATE INDEX IF NOT EXISTS idx_entries_assignment
ON commissions.entries(assignment_id) WHERE assignment_id IS NOT NULL;

-- =============================================================================
-- PRIORIDAD MEDIA: Tablas de auditoria/historial
-- =============================================================================

-- -----------------------------------------------------------------------------
-- Schema: audit
-- -----------------------------------------------------------------------------

-- FK: audit.audit_logs.user_id -> users.users(id)
CREATE INDEX IF NOT EXISTS idx_audit_logs_user
ON audit.audit_logs(user_id) WHERE user_id IS NOT NULL;

-- FK: audit.data_changes.user_id -> users.users(id)
CREATE INDEX IF NOT EXISTS idx_data_changes_user
ON audit.data_changes(user_id) WHERE user_id IS NOT NULL;

-- -----------------------------------------------------------------------------
-- Schema: storage
-- -----------------------------------------------------------------------------

-- FK: storage.files.uploaded_by -> users.users(id)
CREATE INDEX IF NOT EXISTS idx_files_uploaded_by
ON storage.files(uploaded_by) WHERE uploaded_by IS NOT NULL;

-- -----------------------------------------------------------------------------
-- Schema: webhooks
-- -----------------------------------------------------------------------------

-- FK: webhooks.webhooks.created_by -> users.users(id)
CREATE INDEX IF NOT EXISTS idx_webhooks_created_by
ON webhooks.webhooks(created_by) WHERE created_by IS NOT NULL;

-- -----------------------------------------------------------------------------
-- Schema: whatsapp
-- -----------------------------------------------------------------------------

-- FK: whatsapp.messages.user_id -> users.users(id)
CREATE INDEX IF NOT EXISTS idx_messages_user
ON whatsapp.messages(user_id) WHERE user_id IS NOT NULL;

-- =============================================================================
-- PRIORIDAD BAJA: Campos de auditoria (created_by/approved_by/closed_by/etc)
-- =============================================================================

-- -----------------------------------------------------------------------------
-- Schema: sales
-- -----------------------------------------------------------------------------

-- FK: sales.activities.created_by -> users.users(id)
CREATE INDEX IF NOT EXISTS idx_activities_created_by
ON sales.activities(created_by) WHERE created_by IS NOT NULL;

-- -----------------------------------------------------------------------------
-- Schema: portfolio
-- -----------------------------------------------------------------------------

-- FK: portfolio.categories.created_by -> users.users(id)
CREATE INDEX IF NOT EXISTS idx_categories_created_by
ON portfolio.categories(created_by) WHERE created_by IS NOT NULL;

-- FK: portfolio.products.created_by -> users.users(id)
CREATE INDEX IF NOT EXISTS idx_products_created_by
ON portfolio.products(created_by) WHERE created_by IS NOT NULL;

-- -----------------------------------------------------------------------------
-- Schema: commissions
-- -----------------------------------------------------------------------------

-- FK: commissions.schemes.created_by -> users.users(id)
CREATE INDEX IF NOT EXISTS idx_schemes_created_by
ON commissions.schemes(created_by) WHERE created_by IS NOT NULL;

-- FK: commissions.assignments.created_by -> users.users(id)
CREATE INDEX IF NOT EXISTS idx_assignments_created_by
ON commissions.assignments(created_by) WHERE created_by IS NOT NULL;

-- FK: commissions.entries.approved_by -> users.users(id)
CREATE INDEX IF NOT EXISTS idx_entries_approved_by
ON commissions.entries(approved_by) WHERE approved_by IS NOT NULL;

-- FK: commissions.periods.closed_by -> users.users(id)
CREATE INDEX IF NOT EXISTS idx_periods_closed_by
ON commissions.periods(closed_by) WHERE closed_by IS NOT NULL;

-- FK: commissions.periods.paid_by -> users.users(id)
CREATE INDEX IF NOT EXISTS idx_periods_paid_by
ON commissions.periods(paid_by) WHERE paid_by IS NOT NULL;

-- FK: commissions.periods.created_by -> users.users(id)
CREATE INDEX IF NOT EXISTS idx_periods_created_by
ON commissions.periods(created_by) WHERE created_by IS NOT NULL;

-- -----------------------------------------------------------------------------
-- Schema: mlm
-- -----------------------------------------------------------------------------

-- FK: mlm.structures.created_by -> users.users(id)
CREATE INDEX IF NOT EXISTS idx_structures_created_by
ON mlm.structures(created_by) WHERE created_by IS NOT NULL;

-- FK: mlm.nodes.highest_rank_id -> mlm.ranks(id)
CREATE INDEX IF NOT EXISTS idx_nodes_highest_rank
ON mlm.nodes(highest_rank_id) WHERE highest_rank_id IS NOT NULL;

-- FK: mlm.rank_history.previous_rank_id -> mlm.ranks(id)
CREATE INDEX IF NOT EXISTS idx_rank_history_previous_rank
ON mlm.rank_history(previous_rank_id) WHERE previous_rank_id IS NOT NULL;

-- -----------------------------------------------------------------------------
-- Schema: users
-- -----------------------------------------------------------------------------

-- FK: users.roles.parent_role_id -> users.roles(id)
CREATE INDEX IF NOT EXISTS idx_roles_parent_role
ON users.roles(parent_role_id) WHERE parent_role_id IS NOT NULL;

-- FK: users.invitations.role_id -> users.roles(id)
CREATE INDEX IF NOT EXISTS idx_invitations_role
ON users.invitations(role_id) WHERE role_id IS NOT NULL;

-- FK: users.invitations.accepted_by_user_id -> users.users(id)
CREATE INDEX IF NOT EXISTS idx_invitations_accepted_by
ON users.invitations(accepted_by_user_id) WHERE accepted_by_user_id IS NOT NULL;

-- FK: users.invitations.created_by -> users.users(id)
CREATE INDEX IF NOT EXISTS idx_invitations_created_by
ON users.invitations(created_by) WHERE created_by IS NOT NULL;

-- -----------------------------------------------------------------------------
-- Schema: auth
-- -----------------------------------------------------------------------------

-- FK: auth.refresh_tokens.session_id -> auth.sessions(id)
CREATE INDEX IF NOT EXISTS idx_refresh_tokens_session
ON auth.refresh_tokens(session_id) WHERE session_id IS NOT NULL;

-- -----------------------------------------------------------------------------
-- Schema: notifications
-- -----------------------------------------------------------------------------

-- FK: notifications.delivery_logs.queue_id -> notifications.notification_queue(id)
CREATE INDEX IF NOT EXISTS idx_delivery_logs_queue
ON notifications.delivery_logs(queue_id) WHERE queue_id IS NOT NULL;

-- FK: notifications.delivery_logs.device_id -> notifications.user_devices(id)
CREATE INDEX IF NOT EXISTS idx_delivery_logs_device
ON notifications.delivery_logs(device_id) WHERE device_id IS NOT NULL;

-- -----------------------------------------------------------------------------
-- Schema: feature_flags
-- -----------------------------------------------------------------------------

-- FK: feature_flags.flag_evaluations.user_id -> users.users(id)
CREATE INDEX IF NOT EXISTS idx_flag_evaluations_user
ON feature_flags.flag_evaluations(user_id) WHERE user_id IS NOT NULL;

-- =============================================================================
-- FIN DE INDICES ADICIONALES PARA FOREIGN KEYS
-- Total: 32 indices creados
-- Nota: El informe original indicaba 31 FKs sin indice (21.7%), pero el
--       analisis detallado revela 32 FKs. Todos han sido cubiertos.
-- =============================================================================