# DATABASE_INVENTORY.yml - ERP Core
# Inventario canonico de objetos de base de datos
# Ubicacion Canonica: orchestration/inventarios/
# Ultima actualizacion: 2026-01-07

version: "3.0"
project: erp-core
updated_at: "2026-01-07"
updated_by: Backend-Agent (Sprint 6-7)

# =============================================================================
# CONFIGURACION DE MOTOR
# =============================================================================

engine:
  name: PostgreSQL
  version: "15+"
  extensions:
    - name: uuid-ossp
      purpose: Generacion de UUIDs
    - name: pg_trgm
      purpose: Busqueda fuzzy por trigram
    - name: btree_gist
      purpose: Indices GiST para exclusion
    - name: pgcrypto
      purpose: Funciones criptograficas

# =============================================================================
# RESUMEN
# =============================================================================

summary:
  total_schemas: 14
  total_tables: 191
  total_tables_base: 118
  total_tables_extensions: 73
  total_functions: 70
  total_triggers: 100
  total_rls_policies: 102
  total_ddl_files: 20
  implemented_extensions: 6
  documented: 30

# =============================================================================
# POLITICA DE CARGA LIMPIA
# =============================================================================

clean_load_policy:
  directiva: "core/orchestration/directivas/legacy/DIRECTIVA-POLITICA-CARGA-LIMPIA.md"
  principio: "DDL-First - Los archivos DDL son la fuente de verdad"
  prohibiciones:
    - "Ejecutar ALTER TABLE directo en BD sin actualizar DDL"
    - "Crear carpeta migrations/"
    - "Crear archivos fix-*.sql, patch-*.sql, hotfix-*.sql"
    - "Dejar BD y DDL desincronizados"

  paths:
    db_ddl_path: "database/ddl"
    db_scripts_path: "database/scripts"
    db_seeds_path: "database/seeds"
    db_name: "erp_generic"

  comandos:
    crear: "./scripts/create-database.sh"
    recrear: "./scripts/reset-database.sh"
    eliminar: "./scripts/drop-database.sh"
    seeds_dev: "./scripts/load-seeds.sh dev"
    seeds_prod: "./scripts/load-seeds.sh prod"

# =============================================================================
# ARCHIVOS DDL IMPLEMENTADOS
# =============================================================================

ddl_files:
  orden_ejecucion:
    1: "00-prerequisites.sql"
    2: "01-auth.sql"
    3: "01-auth-extensions.sql"
    4: "01-auth-mfa-email-verification.sql"
    5: "02-core.sql"
    6: "02-core-extensions.sql"
    7: "03-analytics.sql"
    8: "04-financial.sql"
    9: "05-inventory.sql"
    10: "05-inventory-extensions.sql"
    11: "06-purchase.sql"
    12: "07-sales.sql"
    13: "08-projects.sql"
    14: "09-system.sql"
    15: "09-system-extensions.sql"
    16: "10-billing.sql"
    17: "11-crm.sql"
    18: "12-hr.sql"
    19: "13-audit.sql"
    20: "14-reports.sql"

  extensiones_nuevas:
    - archivo: "01-auth-extensions.sql"
      fecha: "2025-12-08"
      estado: IMPLEMENTADO
      specs:
        - SPEC-TWO-FACTOR-AUTHENTICATION.md
        - SPEC-SEGURIDAD-API-KEYS-PERMISOS.md
        - SPEC-OAUTH2-SOCIAL-LOGIN.md
      tablas: 16
      funciones: 6
      vistas: 2
      triggers: 3
      detalle:
        groups_herencia: [auth.groups, auth.group_implied, auth.user_groups]
        acl: [auth.models, auth.model_access, auth.record_rules, auth.rule_groups, auth.model_fields, auth.field_permissions]
        api_keys: [auth.api_keys]
        two_factor: [auth.trusted_devices, auth.verification_codes, auth.mfa_audit_log]
        oauth2: [auth.oauth_providers, auth.oauth_user_links, auth.oauth_states]

    - archivo: "05-inventory-extensions.sql"
      fecha: "2025-12-08"
      estado: IMPLEMENTADO
      specs:
        - SPEC-VALORACION-INVENTARIO.md
        - SPEC-TRAZABILIDAD-LOTES-SERIES.md
        - SPEC-INVENTARIOS-CICLICOS.md
      tablas: 10
      funciones: 7
      vistas: 3
      triggers: 4
      detalle:
        valoracion_svl: [inventory.stock_valuation_layers, inventory.category_stock_accounts, inventory.valuation_settings]
        lotes_series: [inventory.lots, inventory.move_line_consume_rel, inventory.removal_strategies]
        conteos_ciclicos: [inventory.inventory_count_sessions, inventory.inventory_count_lines, inventory.abc_classification_rules, inventory.product_abc_classification]

    - archivo: "01-auth-mfa-email-verification.sql"
      fecha: "2026-01-07"
      estado: IMPLEMENTADO
      sprint: 5
      specs:
        - Sprint 5: MFA Implementation
        - Sprint 5: Email Verification Flow
      tablas: 2
      detalle:
        mfa: [auth.user_mfa_secrets]
        email_verification: [auth.email_verification_tokens]

    - archivo: "02-core-extensions.sql"
      fecha: "2026-01-07"
      estado: IMPLEMENTADO
      sprint: 6
      specs:
        - BE-013: Currency Exchange Rates
      tablas: 1
      detalle:
        currency: [core.currency_rates]

    - archivo: "09-system-extensions.sql"
      fecha: "2026-01-07"
      estado: IMPLEMENTADO
      sprint: 6
      specs:
        - BE-016: Settings Service 3-Level
      tablas: 3
      funciones: 1
      triggers: 3
      detalle:
        settings: [system.system_settings, tenants.tenant_settings, auth.user_preferences]
      caracteristicas:
        - Cascada 3 niveles (User -> Tenant -> System)
        - RLS Policies por tenant y usuario
        - Seed data con configuraciones base

    - archivo: "13-audit.sql"
      fecha: "2026-01-07"
      estado: IMPLEMENTADO
      sprint: 7
      specs:
        - BE-017: Audit Trail
        - BE-018: Access Logs
        - BE-019: Security Events
      tablas: 3
      funciones: 3
      detalle:
        audit: [audit.audit_logs, audit.access_logs, audit.security_events]
      enums:
        - audit.audit_action (INSERT, UPDATE, DELETE)
        - audit.access_event_type (LOGIN_SUCCESS, LOGIN_FAILED, LOGOUT, etc)
        - audit.security_severity (LOW, MEDIUM, HIGH, CRITICAL)
      caracteristicas:
        - Funciones de limpieza automatica
        - Indices optimizados para consultas
        - Partial indexes para filtros comunes

    - archivo: "14-reports.sql"
      fecha: "2026-01-07"
      estado: IMPLEMENTADO
      sprint: 8
      specs:
        - RF-REPORT-001: Reportes Predefinidos
        - RF-REPORT-002: Dashboards
        - RF-REPORT-003: Report Builder
        - RF-REPORT-004: Reportes Programados
        - BE-021: DashboardsService
        - BE-022: WidgetsService
        - BE-023: ExportService
      tablas: 12
      funciones: 0
      detalle:
        reportes: [reports.report_definitions, reports.report_executions, reports.report_schedules, reports.report_recipients, reports.schedule_executions, reports.custom_reports]
        dashboards: [reports.dashboards, reports.dashboard_widgets, reports.widget_queries]
        data_model: [reports.data_model_entities, reports.data_model_fields, reports.data_model_relationships]
      enums:
        - reports.report_type (financial, accounting, tax, management, operational, custom)
        - reports.execution_status (pending, running, completed, failed, cancelled)
        - reports.export_format (pdf, xlsx, csv, json, html)
        - reports.delivery_method (none, email, storage, webhook)
        - reports.widget_type (15 tipos)
        - reports.param_type (string, number, date, etc)
        - reports.filter_operator (eq, ne, gt, gte, lt, lte, like, in, etc)
      caracteristicas:
        - RLS policies para aislamiento multi-tenant
        - 15 tipos de widgets soportados
        - Soporte para reportes programados con cron
        - Data model para Report Builder visual

# =============================================================================
# SCHEMAS
# =============================================================================

schemas:

  # ---------------------------------------------------------------------------
  # CORE_AUTH - Autenticacion
  # ---------------------------------------------------------------------------
  - name: core_auth
    description: Autenticacion, sesiones y tokens
    module: MGN-001
    rf: [RF-AUTH-001, RF-AUTH-002, RF-AUTH-003, RF-AUTH-004]
    status: documented
    ddl_file: apps/database/ddl/schemas/core_auth/

    tables:
      - name: users_auth
        description: Credenciales de autenticacion
        rf: RF-AUTH-001
        columns:
          - {name: id, type: UUID, pk: true}
          - {name: tenant_id, type: UUID, fk: core_tenants.tenants.id}
          - {name: user_id, type: UUID, fk: core_users.users.id}
          - {name: email, type: VARCHAR(255), unique: true}
          - {name: password_hash, type: VARCHAR(255)}
          - {name: is_active, type: BOOLEAN, default: true}
          - {name: email_verified_at, type: TIMESTAMPTZ}
          - {name: last_login_at, type: TIMESTAMPTZ}
          - {name: created_at, type: TIMESTAMPTZ, default: NOW()}
          - {name: updated_at, type: TIMESTAMPTZ, default: NOW()}
        indexes:
          - {name: idx_users_auth_email, columns: [email], unique: true}
          - {name: idx_users_auth_tenant, columns: [tenant_id]}
        rls_policies:
          - {name: tenant_isolation, using: "tenant_id = current_setting('app.current_tenant_id')::uuid"}

      - name: sessions
        description: Sesiones activas
        rf: RF-AUTH-002
        columns:
          - {name: id, type: UUID, pk: true}
          - {name: user_id, type: UUID, fk: users_auth.id}
          - {name: token_hash, type: VARCHAR(255)}
          - {name: ip_address, type: INET}
          - {name: user_agent, type: TEXT}
          - {name: expires_at, type: TIMESTAMPTZ}
          - {name: created_at, type: TIMESTAMPTZ, default: NOW()}
        indexes:
          - {name: idx_sessions_user, columns: [user_id]}
          - {name: idx_sessions_expires, columns: [expires_at]}

      - name: refresh_tokens
        description: Tokens de refresco JWT
        rf: RF-AUTH-002
        columns:
          - {name: id, type: UUID, pk: true}
          - {name: user_id, type: UUID, fk: users_auth.id}
          - {name: token_hash, type: VARCHAR(255)}
          - {name: expires_at, type: TIMESTAMPTZ}
          - {name: revoked_at, type: TIMESTAMPTZ}
          - {name: created_at, type: TIMESTAMPTZ, default: NOW()}
        indexes:
          - {name: idx_refresh_tokens_user, columns: [user_id]}

      - name: password_resets
        description: Tokens de recuperacion de password
        rf: RF-AUTH-003
        columns:
          - {name: id, type: UUID, pk: true}
          - {name: user_id, type: UUID, fk: users_auth.id}
          - {name: token_hash, type: VARCHAR(255)}
          - {name: expires_at, type: TIMESTAMPTZ}
          - {name: used_at, type: TIMESTAMPTZ}
          - {name: created_at, type: TIMESTAMPTZ, default: NOW()}

      - name: login_attempts
        description: Intentos de login fallidos (brute force protection)
        rf: RF-AUTH-004
        columns:
          - {name: id, type: UUID, pk: true}
          - {name: email, type: VARCHAR(255)}
          - {name: ip_address, type: INET}
          - {name: attempted_at, type: TIMESTAMPTZ, default: NOW()}
          - {name: success, type: BOOLEAN}
        indexes:
          - {name: idx_login_attempts_email, columns: [email]}
          - {name: idx_login_attempts_ip, columns: [ip_address]}

      - name: oauth_accounts
        description: Cuentas OAuth vinculadas
        rf: RF-AUTH-005
        columns:
          - {name: id, type: UUID, pk: true}
          - {name: user_id, type: UUID, fk: users_auth.id}
          - {name: provider, type: VARCHAR(50)}
          - {name: provider_user_id, type: VARCHAR(255)}
          - {name: access_token, type: TEXT}
          - {name: refresh_token, type: TEXT}
          - {name: expires_at, type: TIMESTAMPTZ}
          - {name: created_at, type: TIMESTAMPTZ, default: NOW()}
        indexes:
          - {name: idx_oauth_provider_user, columns: [provider, provider_user_id], unique: true}

    functions:
      - name: validate_password
        params: [user_id UUID, password TEXT]
        returns: BOOLEAN
        rf: RF-AUTH-001
        description: Valida password contra hash

      - name: cleanup_expired_sessions
        params: []
        returns: INTEGER
        rf: RF-AUTH-002
        description: Elimina sesiones expiradas

    triggers:
      - name: trg_update_users_auth_timestamp
        table: users_auth
        event: BEFORE UPDATE
        function: update_updated_at()

  # ---------------------------------------------------------------------------
  # CORE_USERS - Usuarios
  # ---------------------------------------------------------------------------
  - name: core_users
    description: Gestion de usuarios y perfiles
    module: MGN-002
    rf: [RF-USERS-001, RF-USERS-002, RF-USERS-003]
    status: documented
    ddl_file: apps/database/ddl/schemas/core_users/

    tables:
      - name: users
        description: Usuarios del sistema
        rf: RF-USERS-001
        columns:
          - {name: id, type: UUID, pk: true}
          - {name: tenant_id, type: UUID, fk: core_tenants.tenants.id}
          - {name: first_name, type: VARCHAR(100)}
          - {name: last_name, type: VARCHAR(100)}
          - {name: display_name, type: VARCHAR(200)}
          - {name: avatar_url, type: TEXT}
          - {name: phone, type: VARCHAR(20)}
          - {name: timezone, type: VARCHAR(50), default: "'UTC'"}
          - {name: locale, type: VARCHAR(10), default: "'es-MX'"}
          - {name: is_active, type: BOOLEAN, default: true}
          - {name: created_at, type: TIMESTAMPTZ, default: NOW()}
          - {name: updated_at, type: TIMESTAMPTZ, default: NOW()}
          - {name: deleted_at, type: TIMESTAMPTZ}
        indexes:
          - {name: idx_users_tenant, columns: [tenant_id]}
          - {name: idx_users_name_search, columns: [first_name, last_name], type: gin_trgm}
        rls_policies:
          - {name: tenant_isolation, using: "tenant_id = current_setting('app.current_tenant_id')::uuid"}

      - name: user_profiles
        description: Perfil extendido de usuarios
        rf: RF-USERS-002
        columns:
          - {name: id, type: UUID, pk: true}
          - {name: user_id, type: UUID, fk: users.id, unique: true}
          - {name: bio, type: TEXT}
          - {name: company, type: VARCHAR(200)}
          - {name: job_title, type: VARCHAR(100)}
          - {name: address, type: JSONB}
          - {name: social_links, type: JSONB}
          - {name: created_at, type: TIMESTAMPTZ, default: NOW()}
          - {name: updated_at, type: TIMESTAMPTZ, default: NOW()}

      - name: user_preferences
        description: Preferencias de usuario
        rf: RF-USERS-003
        columns:
          - {name: id, type: UUID, pk: true}
          - {name: user_id, type: UUID, fk: users.id, unique: true}
          - {name: theme, type: VARCHAR(20), default: "'light'"}
          - {name: notifications_email, type: BOOLEAN, default: true}
          - {name: notifications_push, type: BOOLEAN, default: true}
          - {name: language, type: VARCHAR(10), default: "'es'"}
          - {name: date_format, type: VARCHAR(20), default: "'DD/MM/YYYY'"}
          - {name: created_at, type: TIMESTAMPTZ, default: NOW()}
          - {name: updated_at, type: TIMESTAMPTZ, default: NOW()}

  # ---------------------------------------------------------------------------
  # CORE_RBAC - Roles y Permisos
  # ---------------------------------------------------------------------------
  - name: core_rbac
    description: Control de acceso basado en roles
    module: MGN-003
    rf: [RF-RBAC-001, RF-RBAC-002, RF-RBAC-003]
    status: documented
    ddl_file: apps/database/ddl/schemas/core_rbac/

    tables:
      - name: roles
        description: Definicion de roles
        rf: RF-RBAC-001
        columns:
          - {name: id, type: UUID, pk: true}
          - {name: tenant_id, type: UUID, fk: core_tenants.tenants.id}
          - {name: name, type: VARCHAR(100)}
          - {name: slug, type: VARCHAR(100)}
          - {name: description, type: TEXT}
          - {name: is_system, type: BOOLEAN, default: false}
          - {name: is_active, type: BOOLEAN, default: true}
          - {name: created_at, type: TIMESTAMPTZ, default: NOW()}
          - {name: updated_at, type: TIMESTAMPTZ, default: NOW()}
        indexes:
          - {name: idx_roles_tenant_slug, columns: [tenant_id, slug], unique: true}
        rls_policies:
          - {name: tenant_isolation, using: "tenant_id = current_setting('app.current_tenant_id')::uuid"}

      - name: permissions
        description: Definicion de permisos
        rf: RF-RBAC-002
        columns:
          - {name: id, type: UUID, pk: true}
          - {name: module, type: VARCHAR(50)}
          - {name: action, type: VARCHAR(50)}
          - {name: resource, type: VARCHAR(100)}
          - {name: description, type: TEXT}
          - {name: created_at, type: TIMESTAMPTZ, default: NOW()}
        indexes:
          - {name: idx_permissions_module_action, columns: [module, action, resource], unique: true}

      - name: role_permissions
        description: Asignacion permisos a roles
        rf: RF-RBAC-002
        columns:
          - {name: role_id, type: UUID, fk: roles.id}
          - {name: permission_id, type: UUID, fk: permissions.id}
          - {name: created_at, type: TIMESTAMPTZ, default: NOW()}
        constraints:
          - {type: pk, columns: [role_id, permission_id]}

      - name: user_roles
        description: Asignacion roles a usuarios
        rf: RF-RBAC-003
        columns:
          - {name: user_id, type: UUID, fk: core_users.users.id}
          - {name: role_id, type: UUID, fk: roles.id}
          - {name: assigned_at, type: TIMESTAMPTZ, default: NOW()}
          - {name: assigned_by, type: UUID, fk: core_users.users.id}
        constraints:
          - {type: pk, columns: [user_id, role_id]}

    functions:
      - name: user_has_permission
        params: [p_user_id UUID, p_module VARCHAR, p_action VARCHAR, p_resource VARCHAR]
        returns: BOOLEAN
        rf: RF-RBAC-002
        description: Verifica si usuario tiene permiso especifico

      - name: get_user_permissions
        params: [p_user_id UUID]
        returns: TABLE
        rf: RF-RBAC-002
        description: Retorna todos los permisos del usuario

  # ---------------------------------------------------------------------------
  # CORE_TENANTS - Multi-tenancy
  # ---------------------------------------------------------------------------
  - name: core_tenants
    description: Gestion multi-tenant
    module: MGN-004
    rf: [RF-TENANTS-001, RF-TENANTS-002, RF-TENANTS-003]
    status: documented
    ddl_file: apps/database/ddl/schemas/core_tenants/

    tables:
      - name: tenants
        description: Organizaciones/empresas
        rf: RF-TENANTS-001
        columns:
          - {name: id, type: UUID, pk: true}
          - {name: name, type: VARCHAR(200)}
          - {name: slug, type: VARCHAR(100), unique: true}
          - {name: domain, type: VARCHAR(255)}
          - {name: logo_url, type: TEXT}
          - {name: settings, type: JSONB, default: "'{}'"}
          - {name: is_active, type: BOOLEAN, default: true}
          - {name: trial_ends_at, type: TIMESTAMPTZ}
          - {name: created_at, type: TIMESTAMPTZ, default: NOW()}
          - {name: updated_at, type: TIMESTAMPTZ, default: NOW()}
          - {name: deleted_at, type: TIMESTAMPTZ}
        indexes:
          - {name: idx_tenants_slug, columns: [slug], unique: true}
          - {name: idx_tenants_domain, columns: [domain]}

      - name: tenant_settings
        description: Configuracion por tenant
        rf: RF-TENANTS-002
        columns:
          - {name: id, type: UUID, pk: true}
          - {name: tenant_id, type: UUID, fk: tenants.id, unique: true}
          - {name: currency, type: VARCHAR(3), default: "'MXN'"}
          - {name: timezone, type: VARCHAR(50), default: "'America/Mexico_City'"}
          - {name: date_format, type: VARCHAR(20), default: "'DD/MM/YYYY'"}
          - {name: fiscal_config, type: JSONB}
          - {name: modules_enabled, type: JSONB}
          - {name: created_at, type: TIMESTAMPTZ, default: NOW()}
          - {name: updated_at, type: TIMESTAMPTZ, default: NOW()}

      - name: plans
        description: Planes de suscripcion
        rf: RF-TENANTS-003
        columns:
          - {name: id, type: UUID, pk: true}
          - {name: name, type: VARCHAR(100)}
          - {name: slug, type: VARCHAR(50), unique: true}
          - {name: description, type: TEXT}
          - {name: price_monthly, type: DECIMAL(10,2)}
          - {name: price_yearly, type: DECIMAL(10,2)}
          - {name: features, type: JSONB}
          - {name: limits, type: JSONB}
          - {name: is_active, type: BOOLEAN, default: true}
          - {name: created_at, type: TIMESTAMPTZ, default: NOW()}

      - name: subscriptions
        description: Suscripciones de tenants
        rf: RF-TENANTS-003
        columns:
          - {name: id, type: UUID, pk: true}
          - {name: tenant_id, type: UUID, fk: tenants.id}
          - {name: plan_id, type: UUID, fk: plans.id}
          - {name: status, type: VARCHAR(20)}
          - {name: starts_at, type: TIMESTAMPTZ}
          - {name: ends_at, type: TIMESTAMPTZ}
          - {name: canceled_at, type: TIMESTAMPTZ}
          - {name: created_at, type: TIMESTAMPTZ, default: NOW()}
        indexes:
          - {name: idx_subscriptions_tenant, columns: [tenant_id]}
          - {name: idx_subscriptions_status, columns: [status]}

  # ---------------------------------------------------------------------------
  # CORE_CATALOGS - Catalogos Maestros
  # ---------------------------------------------------------------------------
  - name: core_catalogs
    description: Catalogos maestros compartidos
    module: MGN-005
    rf: [RF-CATALOGS-001, RF-CATALOGS-002, RF-CATALOGS-003]
    status: planned
    ddl_file: apps/database/ddl/schemas/core_catalogs/

    tables:
      - name: partners
        description: Clientes, proveedores, contactos
        rf: RF-CATALOGS-001
        columns:
          - {name: id, type: UUID, pk: true}
          - {name: tenant_id, type: UUID, fk: core_tenants.tenants.id}
          - {name: name, type: VARCHAR(200)}
          - {name: tax_id, type: VARCHAR(20)}
          - {name: email, type: VARCHAR(255)}
          - {name: phone, type: VARCHAR(20)}
          - {name: is_customer, type: BOOLEAN, default: false}
          - {name: is_supplier, type: BOOLEAN, default: false}
          - {name: is_contact, type: BOOLEAN, default: false}
          - {name: credit_limit, type: DECIMAL(15,2), default: 0}
          - {name: is_active, type: BOOLEAN, default: true}
          - {name: created_at, type: TIMESTAMPTZ, default: NOW()}
          - {name: updated_at, type: TIMESTAMPTZ, default: NOW()}
          - {name: deleted_at, type: TIMESTAMPTZ}
        indexes:
          - {name: idx_partners_tenant_tax, columns: [tenant_id, tax_id], unique: true}
          - {name: idx_partners_name_search, columns: [name], type: gin_trgm}
        rls_policies:
          - {name: tenant_isolation, using: "tenant_id = current_setting('app.current_tenant_id')::uuid"}

      - name: products
        description: Productos y servicios
        rf: RF-CATALOGS-002
        columns:
          - {name: id, type: UUID, pk: true}
          - {name: tenant_id, type: UUID, fk: core_tenants.tenants.id}
          - {name: sku, type: VARCHAR(50)}
          - {name: name, type: VARCHAR(200)}
          - {name: description, type: TEXT}
          - {name: category_id, type: UUID, fk: categories.id}
          - {name: unit_id, type: UUID, fk: units.id}
          - {name: price, type: DECIMAL(15,4)}
          - {name: cost, type: DECIMAL(15,4)}
          - {name: is_service, type: BOOLEAN, default: false}
          - {name: is_active, type: BOOLEAN, default: true}
          - {name: created_at, type: TIMESTAMPTZ, default: NOW()}
          - {name: updated_at, type: TIMESTAMPTZ, default: NOW()}
        indexes:
          - {name: idx_products_tenant_sku, columns: [tenant_id, sku], unique: true}

      - name: categories
        description: Categorias jerarquicas
        rf: RF-CATALOGS-002
        columns:
          - {name: id, type: UUID, pk: true}
          - {name: tenant_id, type: UUID, fk: core_tenants.tenants.id}
          - {name: parent_id, type: UUID, fk: categories.id}
          - {name: name, type: VARCHAR(100)}
          - {name: slug, type: VARCHAR(100)}
          - {name: path, type: LTREE}
          - {name: is_active, type: BOOLEAN, default: true}
          - {name: created_at, type: TIMESTAMPTZ, default: NOW()}

      - name: units
        description: Unidades de medida
        rf: RF-CATALOGS-003
        columns:
          - {name: id, type: UUID, pk: true}
          - {name: code, type: VARCHAR(10)}
          - {name: name, type: VARCHAR(50)}
          - {name: category, type: VARCHAR(20)}
          - {name: is_base, type: BOOLEAN, default: false}
          - {name: conversion_factor, type: DECIMAL(15,6), default: 1}

      - name: currencies
        description: Monedas
        rf: RF-CATALOGS-003
        columns:
          - {name: id, type: UUID, pk: true}
          - {name: code, type: VARCHAR(3)}
          - {name: name, type: VARCHAR(50)}
          - {name: symbol, type: VARCHAR(5)}
          - {name: decimal_places, type: INTEGER, default: 2}
          - {name: is_active, type: BOOLEAN, default: true}

      - name: countries
        description: Paises
        rf: RF-CATALOGS-003
        columns:
          - {name: id, type: UUID, pk: true}
          - {name: code, type: VARCHAR(3)}
          - {name: name, type: VARCHAR(100)}
          - {name: phone_code, type: VARCHAR(5)}
          - {name: is_active, type: BOOLEAN, default: true}

      - name: states
        description: Estados/Provincias
        rf: RF-CATALOGS-003
        columns:
          - {name: id, type: UUID, pk: true}
          - {name: country_id, type: UUID, fk: countries.id}
          - {name: code, type: VARCHAR(10)}
          - {name: name, type: VARCHAR(100)}
          - {name: is_active, type: BOOLEAN, default: true}

  # ---------------------------------------------------------------------------
  # CORE_AUDIT - Auditoria
  # ---------------------------------------------------------------------------
  - name: core_audit
    description: Logs de auditoria
    module: MGN-019
    rf: [RF-AUDIT-001, RF-AUDIT-002]
    status: planned
    ddl_file: apps/database/ddl/schemas/core_audit/

    tables:
      - name: audit_logs
        description: Log de cambios en entidades
        rf: RF-AUDIT-001
        columns:
          - {name: id, type: UUID, pk: true}
          - {name: tenant_id, type: UUID}
          - {name: user_id, type: UUID}
          - {name: entity_type, type: VARCHAR(100)}
          - {name: entity_id, type: UUID}
          - {name: action, type: VARCHAR(20)}
          - {name: old_values, type: JSONB}
          - {name: new_values, type: JSONB}
          - {name: ip_address, type: INET}
          - {name: user_agent, type: TEXT}
          - {name: created_at, type: TIMESTAMPTZ, default: NOW()}
        indexes:
          - {name: idx_audit_tenant_entity, columns: [tenant_id, entity_type, entity_id]}
          - {name: idx_audit_created, columns: [created_at]}
        partitioning:
          type: RANGE
          column: created_at
          interval: MONTHLY

      - name: activity_logs
        description: Log de actividad de usuarios
        rf: RF-AUDIT-002
        columns:
          - {name: id, type: UUID, pk: true}
          - {name: tenant_id, type: UUID}
          - {name: user_id, type: UUID}
          - {name: action, type: VARCHAR(100)}
          - {name: resource, type: VARCHAR(100)}
          - {name: details, type: JSONB}
          - {name: ip_address, type: INET}
          - {name: created_at, type: TIMESTAMPTZ, default: NOW()}

  # ---------------------------------------------------------------------------
  # CORE_NOTIFICATIONS - Notificaciones
  # ---------------------------------------------------------------------------
  - name: core_notifications
    description: Sistema de notificaciones
    module: MGN-008
    rf: [RF-NOTIF-001, RF-NOTIF-002]
    status: planned
    ddl_file: apps/database/ddl/schemas/core_notifications/

    tables:
      - name: notifications
        description: Notificaciones de usuarios
        rf: RF-NOTIF-001
        columns:
          - {name: id, type: UUID, pk: true}
          - {name: tenant_id, type: UUID}
          - {name: user_id, type: UUID}
          - {name: type, type: VARCHAR(50)}
          - {name: title, type: VARCHAR(200)}
          - {name: message, type: TEXT}
          - {name: data, type: JSONB}
          - {name: read_at, type: TIMESTAMPTZ}
          - {name: created_at, type: TIMESTAMPTZ, default: NOW()}
        indexes:
          - {name: idx_notifications_user_read, columns: [user_id, read_at]}
        rls_policies:
          - {name: tenant_isolation, using: "tenant_id = current_setting('app.current_tenant_id')::uuid"}

      - name: notification_templates
        description: Templates de notificaciones
        rf: RF-NOTIF-002
        columns:
          - {name: id, type: UUID, pk: true}
          - {name: tenant_id, type: UUID}
          - {name: code, type: VARCHAR(50)}
          - {name: name, type: VARCHAR(100)}
          - {name: subject, type: VARCHAR(200)}
          - {name: body, type: TEXT}
          - {name: channels, type: JSONB}
          - {name: is_active, type: BOOLEAN, default: true}
          - {name: created_at, type: TIMESTAMPTZ, default: NOW()}

# =============================================================================
# CONVENCIONES Y ESTANDARES
# =============================================================================

conventions:
  naming:
    schemas: "core_{nombre}"
    tables: snake_case_plural
    columns: snake_case
    foreign_keys: "fk_{tabla_origen}_{tabla_destino}"
    indexes: "idx_{tabla}_{columnas}"
    primary_keys: "{tabla}_pkey"
    triggers: "trg_{tabla}_{evento}"
    functions: snake_case

  mandatory_columns:
    - {name: id, type: UUID, description: "Primary key"}
    - {name: tenant_id, type: UUID, description: "Multi-tenant isolation (si aplica)"}
    - {name: created_at, type: TIMESTAMPTZ, default: NOW()}
    - {name: updated_at, type: TIMESTAMPTZ, default: NOW()}

  soft_delete:
    column: deleted_at
    type: TIMESTAMPTZ

  rls:
    pattern: tenant_isolation
    context_variable: app.current_tenant_id

  mandatory_indexes:
    - "idx_{tabla}_tenant_id"
    - "idx_{tabla}_created_at"

# =============================================================================
# HISTORIAL
# =============================================================================

history:
  - date: "2026-01-07"
    action: "Implementacion DDL Sprint 8 - Reports & Dashboards"
    author: Database-Agent
    changes:
      - "Creado 14-reports.sql (12 tablas: reportes, dashboards, data model)"
      - "Actualizado create-database.sh con 20 DDL files"
      - "Validado recreacion completa de BD con --force"
    sprints_cubiertos:
      - Sprint 8: Reports & Dashboards
    specs_cubiertas:
      - RF-REPORT-001: Reportes Predefinidos
      - RF-REPORT-002: Dashboards
      - RF-REPORT-003: Report Builder
      - RF-REPORT-004: Reportes Programados
    tablas_nuevas: 12
    enums_nuevos: 7
    rls_policies_nuevas: 7

  - date: "2026-01-07"
    action: "Implementacion DDL Sprint 5-7"
    author: Backend-Agent
    changes:
      - "Creado 01-auth-mfa-email-verification.sql (2 tablas: MFA + Email Verification)"
      - "Creado 02-core-extensions.sql (1 tabla: currency_rates)"
      - "Creado 09-system-extensions.sql (3 tablas: settings 3-level cascade)"
      - "Creado 13-audit.sql (3 tablas: audit_logs, access_logs, security_events)"
      - "Creado recreate-database.sh (wrapper para recreacion)"
      - "Actualizado create-database.sh con 19 DDL files"
      - "Validado recreacion completa de BD"
    sprints_cubiertos:
      - Sprint 5: MFA + Email Verification
      - Sprint 6: Catalogs & Settings
      - Sprint 7: Audit & Notifications
    tablas_nuevas: 9
    funciones_nuevas: 4
    triggers_nuevos: 3

  - date: "2025-12-08"
    action: "Implementacion DDL extensiones Auth e Inventory"
    author: Database-Agent
    changes:
      - "Creado 01-auth-extensions.sql (16 tablas, 6 funciones, 2 vistas)"
      - "Creado 05-inventory-extensions.sql (10 tablas, 7 funciones, 3 vistas)"
      - "Actualizado create-database.sh con orden de ejecucion completo"
      - "Agregada seccion clean_load_policy y ddl_files"
    specs_cubiertas:
      - SPEC-TWO-FACTOR-AUTHENTICATION.md
      - SPEC-SEGURIDAD-API-KEYS-PERMISOS.md
      - SPEC-OAUTH2-SOCIAL-LOGIN.md
      - SPEC-VALORACION-INVENTARIO.md
      - SPEC-TRAZABILIDAD-LOTES-SERIES.md
      - SPEC-INVENTARIOS-CICLICOS.md

  - date: "2025-12-05"
    action: "Reestructuracion completa siguiendo filosofia GAMILIT"
    author: Requirements-Analyst
    changes:
      - "Agregada trazabilidad RF por tabla"
      - "Documentadas columnas con tipos"
      - "Agregados indices y RLS policies"
      - "Documentadas funciones y triggers"