# ==============================================================================
# DATABASES REGISTRY - Control Plane
# ==============================================================================
# Proposito: Registro centralizado de bases de datos, roles y permisos
# Mantenido por: DevOps-Agent + Database-Agent
# Actualizado: 2025-12-18
# ==============================================================================

version: "1.0.0"
updated: "2025-12-18"

# ------------------------------------------------------------------------------
# INSTANCIA POSTGRESQL
# ------------------------------------------------------------------------------
instance:
  type: "postgresql"
  version: "15"
  host:
    local: "localhost"
    development: "postgres.dev.internal"
    production: "postgres.prod.internal"
  port: 5432
  max_connections: 100

# ------------------------------------------------------------------------------
# CONVENCION DE ROLES
# ------------------------------------------------------------------------------
role_conventions:
  owner:
    description: "DDL y permisos - Solo para migraciones y CI/CD"
    permissions:
      - "CREATE"
      - "ALTER"
      - "DROP"
      - "GRANT"
    suffix: "_owner"

  runtime:
    description: "CRUD de aplicacion - Usado por servicios en runtime"
    permissions:
      - "SELECT"
      - "INSERT"
      - "UPDATE"
      - "DELETE"
    suffix: "_app"

  migrator:
    description: "ALTER para migraciones - Usado por scripts de migracion"
    permissions:
      - "SELECT"
      - "INSERT"
      - "UPDATE"
      - "DELETE"
      - "ALTER"
    suffix: "_migrator"

  readonly:
    description: "Solo lectura - Para reportes y analytics"
    permissions:
      - "SELECT"
    suffix: "_readonly"

# ------------------------------------------------------------------------------
# BASES DE DATOS POR PROYECTO
# ------------------------------------------------------------------------------
databases:

  # ============================================================================
  # GAMILIT
  # ============================================================================
  gamilit:
    database: "gamilit_db"
    description: "BD principal de Gamilit"
    charset: "UTF8"
    collation: "en_US.UTF-8"

    roles:
      owner: "gamilit_owner"
      runtime: "gamilit_app"
      migrator: "gamilit_migrator"
      readonly: "gamilit_readonly"

    schemas:
      - name: "public"
        description: "Schema default"
      - name: "auth"
        description: "Autenticacion y usuarios"
      - name: "gamification"
        description: "Sistema de gamificacion"
      - name: "progress_tracking"
        description: "Seguimiento de progreso"
      - name: "content"
        description: "Contenido educativo"

    extensions:
      - "uuid-ossp"
      - "pgcrypto"

  # ============================================================================
  # ERP CORE
  # ============================================================================
  erp_core:
    database: "erp_core_db"
    description: "BD core del ERP Suite"

    roles:
      owner: "erp_core_owner"
      runtime: "erp_core_app"
      migrator: "erp_core_migrator"
      readonly: "erp_core_readonly"

    schemas:
      - name: "public"
        description: "Schema default"
      - name: "auth"
        description: "Autenticacion centralizada"
      - name: "tenants"
        description: "Multi-tenancy"
      - name: "config"
        description: "Configuracion global"
      - name: "core"
        description: "Entidades core (partners, products, etc.)"

    extensions:
      - "uuid-ossp"
      - "pgcrypto"

  # ============================================================================
  # ERP CONSTRUCCION
  # ============================================================================
  erp_construccion:
    database: "erp_construccion_db"
    description: "BD de vertical construccion"

    roles:
      owner: "erp_construccion_owner"
      runtime: "erp_construccion_app"
      migrator: "erp_construccion_migrator"
      readonly: "erp_construccion_readonly"

    schemas:
      - name: "public"
      - name: "construction"
        description: "Fraccionamientos, etapas, lotes"
      - name: "hr"
        description: "Recursos humanos"
      - name: "hse"
        description: "Seguridad e higiene"
      - name: "estimates"
        description: "Estimaciones"
      - name: "progress"
        description: "Avance de obra"
      - name: "contracts"
        description: "Contratos"
      - name: "budgets"
        description: "Presupuestos"

    extensions:
      - "uuid-ossp"
      - "postgis"  # Para datos geograficos

  # ============================================================================
  # ERP MECANICAS
  # ============================================================================
  erp_mecanicas:
    database: "erp_mecanicas_db"
    description: "BD de vertical mecanicas diesel"

    roles:
      owner: "erp_mecanicas_owner"
      runtime: "erp_mecanicas_app"
      migrator: "erp_mecanicas_migrator"
      readonly: "erp_mecanicas_readonly"

    schemas:
      - name: "public"
      - name: "workshop"
        description: "Ordenes de servicio, diagnosticos"
      - name: "inventory"
        description: "Inventario de refacciones"
      - name: "customers"
        description: "Clientes y vehiculos"
      - name: "quotes"
        description: "Cotizaciones"

    extensions:
      - "uuid-ossp"

  # ============================================================================
  # TRADING
  # ============================================================================
  trading:
    database: "trading_db"
    description: "BD de plataforma de trading"

    roles:
      owner: "trading_owner"
      runtime: "trading_app"
      migrator: "trading_migrator"
      readonly: "trading_readonly"

    schemas:
      - name: "public"
      - name: "market_data"
        description: "Datos de mercado"
      - name: "strategies"
        description: "Estrategias de trading"
      - name: "backtest"
        description: "Backtesting"
      - name: "ml_models"
        description: "Modelos ML"

    extensions:
      - "uuid-ossp"
      - "timescaledb"  # Para series de tiempo

  # ============================================================================
  # BETTING
  # ============================================================================
  betting:
    database: "betting_db"
    description: "BD de betting analytics"
    status: "planned"

    roles:
      owner: "betting_owner"
      runtime: "betting_app"
      migrator: "betting_migrator"
      readonly: "betting_readonly"

    schemas:
      - name: "public"
      - name: "events"
      - name: "predictions"
      - name: "analytics"

# ------------------------------------------------------------------------------
# POLITICAS DE SEGURIDAD
# ------------------------------------------------------------------------------
security_policies:
  password_policy:
    min_length: 16
    require_special: true
    rotation_days: 90

  connection_policy:
    ssl_required: true
    allowed_hosts:
      local: ["localhost", "127.0.0.1", "docker.host.internal"]
      development: ["*.dev.internal"]
      production: ["*.prod.internal"]

  backup_policy:
    frequency: "daily"
    retention_days: 30
    encryption: true

# ------------------------------------------------------------------------------
# VALIDACION
# ------------------------------------------------------------------------------
validation:
  script: "devtools/scripts/validation/validate-databases.sh"

  erp_vidrio:
    name: erp_vidrio_db
    host: postgres
    port: 5432
    roles:
      owner: erp_vidrio_owner
      runtime: erp_vidrio_app
      migrator: erp_vidrio_migrator
      readonly: erp_vidrio_readonly
    schemas:
      - public
      - inventory
      - production
      - sales
    extensions:
      - uuid-ossp
    status: planned

  erp_retail:
    name: erp_retail_db
    host: postgres
    port: 5432
    roles:
      owner: erp_retail_owner
      runtime: erp_retail_app
      migrator: erp_retail_migrator
      readonly: erp_retail_readonly
    schemas:
      - public
      - inventory
      - pos
      - sales
      - customers
    extensions:
      - uuid-ossp
    status: planned

  inmobiliaria:
    name: inmobiliaria_db
    host: postgres
    port: 5432
    roles:
      owner: inmobiliaria_owner
      runtime: inmobiliaria_app
      migrator: inmobiliaria_migrator
      readonly: inmobiliaria_readonly
    schemas:
      - public
      - properties
      - analytics
      - crm
    extensions:
      - uuid-ossp
      - postgis
    status: planned

  platform_marketing:
    name: platform_marketing_db
    host: postgres
    port: 5432
    roles:
      owner: marketing_owner
      runtime: marketing_app
      migrator: marketing_migrator
      readonly: marketing_readonly
    schemas:
      - public
      - content
      - campaigns
      - analytics
    extensions:
      - uuid-ossp
    status: active