# DDL-SPEC: Schema core_users

## Identificacion

| Campo | Valor |
|-------|-------|
| **Schema** | core_users |
| **Modulo** | MGN-002 |
| **Version** | 1.0 |
| **Estado** | En Diseño |
| **Autor** | System |
| **Fecha** | 2025-12-05 |

---

## Descripcion General

El schema `core_users` contiene la tabla principal de usuarios y todas las tablas relacionadas con la gestion de perfiles, preferencias y cambios de identidad. Es el schema central de identidad del sistema.

### Alcance

- Tabla principal de usuarios
- Avatares e historial de imagenes
- Solicitudes de cambio de email
- Preferencias de usuario
- Activacion de cuentas

> **Nota:** El historial de passwords reside en `core_auth.password_history` por coherencia con el modulo de autenticacion.

---

## Diagrama Entidad-Relacion

```mermaid
erDiagram
    tenants ||--o{ users : "contains"
    users ||--o| user_preferences : "has"
    users ||--o{ user_avatars : "uploads"
    users ||--o{ email_change_requests : "requests"
    users ||--o{ user_activation_tokens : "receives"
    users }o--o{ user_roles : "has"
    roles ||--o{ user_roles : "assigned to"

    users {
        uuid id PK
        uuid tenant_id FK
        varchar email UK
        varchar password_hash
        varchar first_name
        varchar last_name
        varchar phone
        varchar avatar_url
        varchar avatar_thumbnail_url
        enum status
        boolean is_active
        timestamptz email_verified_at
        timestamptz last_login_at
        integer failed_login_attempts
        timestamptz locked_until
        jsonb metadata
        timestamptz created_at
        uuid created_by FK
        timestamptz updated_at
        uuid updated_by FK
        timestamptz deleted_at
        uuid deleted_by FK
    }

    user_preferences {
        uuid id PK
        uuid user_id FK
        uuid tenant_id FK
        varchar language
        varchar timezone
        varchar date_format
        varchar time_format
        varchar theme
        boolean sidebar_collapsed
        boolean compact_mode
        varchar font_size
        jsonb notifications
        jsonb dashboard
        jsonb metadata
        timestamptz created_at
        timestamptz updated_at
    }

    user_avatars {
        uuid id PK
        uuid user_id FK
        uuid tenant_id FK
        varchar original_url
        varchar main_url
        varchar thumbnail_url
        varchar mime_type
        integer file_size
        boolean is_current
        timestamptz created_at
    }

    email_change_requests {
        uuid id PK
        uuid user_id FK
        uuid tenant_id FK
        varchar current_email
        varchar new_email
        varchar token_hash
        timestamptz expires_at
        timestamptz completed_at
        timestamptz created_at
    }

    user_activation_tokens {
        uuid id PK
        uuid user_id FK
        uuid tenant_id FK
        varchar token_hash
        timestamptz expires_at
        timestamptz used_at
        timestamptz created_at
    }

    user_roles {
        uuid id PK
        uuid user_id FK
        uuid role_id FK
        uuid tenant_id FK
        timestamptz created_at
        uuid created_by FK
    }

    roles {
        uuid id PK
        uuid tenant_id FK
        varchar name
        varchar description
        boolean is_system
        timestamptz created_at
    }
```

---

## Tablas

### 1. users

Tabla principal que almacena la informacion de todos los usuarios del sistema.

#### Estructura

| Columna | Tipo | Nullable | Default | Descripcion |
|---------|------|----------|---------|-------------|
| `id` | UUID | NOT NULL | gen_random_uuid() | PK |
| `tenant_id` | UUID | NOT NULL | - | FK → core_tenants.tenants |
| `email` | VARCHAR(255) | NOT NULL | - | Email unico por tenant |
| `password_hash` | VARCHAR(255) | NOT NULL | - | Hash bcrypt del password |
| `first_name` | VARCHAR(100) | NOT NULL | - | Nombre |
| `last_name` | VARCHAR(100) | NOT NULL | - | Apellido |
| `phone` | VARCHAR(20) | NULL | - | Telefono E.164 |
| `avatar_url` | VARCHAR(500) | NULL | - | URL avatar principal |
| `avatar_thumbnail_url` | VARCHAR(500) | NULL | - | URL thumbnail 50x50 |
| `status` | user_status | NOT NULL | 'pending_activation' | Estado del usuario |
| `is_active` | BOOLEAN | NOT NULL | false | Si puede operar |
| `email_verified_at` | TIMESTAMPTZ | NULL | - | Cuando verifico email |
| `last_login_at` | TIMESTAMPTZ | NULL | - | Ultimo login |
| `failed_login_attempts` | INTEGER | NOT NULL | 0 | Intentos fallidos |
| `locked_until` | TIMESTAMPTZ | NULL | - | Bloqueado hasta |
| `metadata` | JSONB | NULL | '{}' | Datos adicionales |
| `created_at` | TIMESTAMPTZ | NOT NULL | NOW() | Fecha creacion |
| `created_by` | UUID | NULL | - | Usuario que creo |
| `updated_at` | TIMESTAMPTZ | NOT NULL | NOW() | Ultima actualizacion |
| `updated_by` | UUID | NULL | - | Usuario que actualizo |
| `deleted_at` | TIMESTAMPTZ | NULL | - | Soft delete timestamp |
| `deleted_by` | UUID | NULL | - | Usuario que elimino |

#### Enum user_status

```sql
CREATE TYPE core_users.user_status AS ENUM (
    'pending_activation',
    'active',
    'inactive',
    'locked'
);
```

#### Constraints

```sql
-- Primary Key
CONSTRAINT pk_users PRIMARY KEY (id),

-- Unique (email unico por tenant, excluyendo eliminados)
CONSTRAINT uk_users_tenant_email UNIQUE (tenant_id, email) WHERE deleted_at IS NULL,

-- Foreign Keys
CONSTRAINT fk_users_tenant
    FOREIGN KEY (tenant_id) REFERENCES core_tenants.tenants(id) ON DELETE CASCADE,
CONSTRAINT fk_users_created_by
    FOREIGN KEY (created_by) REFERENCES core_users.users(id) ON DELETE SET NULL,
CONSTRAINT fk_users_updated_by
    FOREIGN KEY (updated_by) REFERENCES core_users.users(id) ON DELETE SET NULL,
CONSTRAINT fk_users_deleted_by
    FOREIGN KEY (deleted_by) REFERENCES core_users.users(id) ON DELETE SET NULL,

-- Check
CONSTRAINT chk_users_email_format
    CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
CONSTRAINT chk_users_phone_format
    CHECK (phone IS NULL OR phone ~* '^\+[0-9]{10,15}$'),
CONSTRAINT chk_users_failed_attempts
    CHECK (failed_login_attempts >= 0)
```

#### Indices

```sql
CREATE INDEX idx_users_tenant_id ON core_users.users(tenant_id);
CREATE INDEX idx_users_email ON core_users.users(email);
CREATE INDEX idx_users_status ON core_users.users(status) WHERE deleted_at IS NULL;
CREATE INDEX idx_users_is_active ON core_users.users(is_active) WHERE deleted_at IS NULL;
CREATE INDEX idx_users_created_at ON core_users.users(created_at DESC);
CREATE INDEX idx_users_full_name ON core_users.users(tenant_id, first_name, last_name) WHERE deleted_at IS NULL;
CREATE INDEX idx_users_not_deleted ON core_users.users(tenant_id) WHERE deleted_at IS NULL;
```

---

### 2. user_preferences

Almacena las preferencias personalizadas de cada usuario.

#### Estructura

| Columna | Tipo | Nullable | Default | Descripcion |
|---------|------|----------|---------|-------------|
| `id` | UUID | NOT NULL | gen_random_uuid() | PK |
| `user_id` | UUID | NOT NULL | - | FK → users (UNIQUE) |
| `tenant_id` | UUID | NOT NULL | - | FK → tenants |
| `language` | VARCHAR(5) | NOT NULL | 'es' | Codigo idioma ISO |
| `timezone` | VARCHAR(50) | NOT NULL | 'America/Mexico_City' | IANA timezone |
| `date_format` | VARCHAR(20) | NOT NULL | 'DD/MM/YYYY' | Formato fecha |
| `time_format` | VARCHAR(5) | NOT NULL | '24h' | 12h o 24h |
| `currency` | VARCHAR(3) | NOT NULL | 'MXN' | Moneda ISO 4217 |
| `number_format` | VARCHAR(10) | NOT NULL | 'es-MX' | Formato numeros |
| `theme` | VARCHAR(10) | NOT NULL | 'system' | light, dark, system |
| `sidebar_collapsed` | BOOLEAN | NOT NULL | false | Sidebar colapsado |
| `compact_mode` | BOOLEAN | NOT NULL | false | Modo compacto |
| `font_size` | VARCHAR(10) | NOT NULL | 'medium' | small, medium, large |
| `notifications` | JSONB | NOT NULL | '{}' | Config notificaciones |
| `dashboard` | JSONB | NOT NULL | '{}' | Config dashboard |
| `metadata` | JSONB | NULL | '{}' | Datos adicionales |
| `created_at` | TIMESTAMPTZ | NOT NULL | NOW() | Fecha creacion |
| `updated_at` | TIMESTAMPTZ | NOT NULL | NOW() | Ultima actualizacion |

#### Constraints

```sql
CONSTRAINT pk_user_preferences PRIMARY KEY (id),
CONSTRAINT uk_user_preferences_user UNIQUE (user_id),
CONSTRAINT fk_user_preferences_user
    FOREIGN KEY (user_id) REFERENCES core_users.users(id) ON DELETE CASCADE,
CONSTRAINT fk_user_preferences_tenant
    FOREIGN KEY (tenant_id) REFERENCES core_tenants.tenants(id) ON DELETE CASCADE,
CONSTRAINT chk_user_preferences_language
    CHECK (language IN ('es', 'en', 'pt')),
CONSTRAINT chk_user_preferences_theme
    CHECK (theme IN ('light', 'dark', 'system')),
CONSTRAINT chk_user_preferences_font_size
    CHECK (font_size IN ('small', 'medium', 'large')),
CONSTRAINT chk_user_preferences_time_format
    CHECK (time_format IN ('12h', '24h'))
```

#### Indices

```sql
CREATE INDEX idx_user_preferences_user_id ON core_users.user_preferences(user_id);
CREATE INDEX idx_user_preferences_tenant_id ON core_users.user_preferences(tenant_id);
```

---

### 3. user_avatars

Historial de avatares subidos por usuarios.

#### Estructura

| Columna | Tipo | Nullable | Default | Descripcion |
|---------|------|----------|---------|-------------|
| `id` | UUID | NOT NULL | gen_random_uuid() | PK |
| `user_id` | UUID | NOT NULL | - | FK → users |
| `tenant_id` | UUID | NOT NULL | - | FK → tenants |
| `original_url` | VARCHAR(500) | NOT NULL | - | URL original |
| `main_url` | VARCHAR(500) | NOT NULL | - | URL 200x200 |
| `thumbnail_url` | VARCHAR(500) | NOT NULL | - | URL 50x50 |
| `mime_type` | VARCHAR(50) | NOT NULL | - | image/jpeg, etc |
| `file_size` | INTEGER | NOT NULL | - | Tamano en bytes |
| `is_current` | BOOLEAN | NOT NULL | true | Si es el actual |
| `created_at` | TIMESTAMPTZ | NOT NULL | NOW() | Fecha subida |

#### Constraints

```sql
CONSTRAINT pk_user_avatars PRIMARY KEY (id),
CONSTRAINT fk_user_avatars_user
    FOREIGN KEY (user_id) REFERENCES core_users.users(id) ON DELETE CASCADE,
CONSTRAINT fk_user_avatars_tenant
    FOREIGN KEY (tenant_id) REFERENCES core_tenants.tenants(id) ON DELETE CASCADE,
CONSTRAINT chk_user_avatars_mime_type
    CHECK (mime_type IN ('image/jpeg', 'image/png', 'image/webp')),
CONSTRAINT chk_user_avatars_file_size
    CHECK (file_size > 0 AND file_size <= 10485760) -- 10MB max
```

#### Indices

```sql
CREATE INDEX idx_user_avatars_user_id ON core_users.user_avatars(user_id);
CREATE INDEX idx_user_avatars_current ON core_users.user_avatars(user_id) WHERE is_current = true;
```

---

### 4. email_change_requests

Solicitudes de cambio de email pendientes.

#### Estructura

| Columna | Tipo | Nullable | Default | Descripcion |
|---------|------|----------|---------|-------------|
| `id` | UUID | NOT NULL | gen_random_uuid() | PK |
| `user_id` | UUID | NOT NULL | - | FK → users |
| `tenant_id` | UUID | NOT NULL | - | FK → tenants |
| `current_email` | VARCHAR(255) | NOT NULL | - | Email actual |
| `new_email` | VARCHAR(255) | NOT NULL | - | Nuevo email |
| `token_hash` | VARCHAR(255) | NOT NULL | - | Hash del token |
| `expires_at` | TIMESTAMPTZ | NOT NULL | - | Expiracion (24h) |
| `completed_at` | TIMESTAMPTZ | NULL | - | Cuando se completo |
| `created_at` | TIMESTAMPTZ | NOT NULL | NOW() | Fecha solicitud |

#### Constraints

```sql
CONSTRAINT pk_email_change_requests PRIMARY KEY (id),
CONSTRAINT fk_email_change_requests_user
    FOREIGN KEY (user_id) REFERENCES core_users.users(id) ON DELETE CASCADE,
CONSTRAINT fk_email_change_requests_tenant
    FOREIGN KEY (tenant_id) REFERENCES core_tenants.tenants(id) ON DELETE CASCADE
```

#### Indices

```sql
CREATE INDEX idx_email_change_requests_user ON core_users.email_change_requests(user_id);
CREATE INDEX idx_email_change_requests_active ON core_users.email_change_requests(user_id)
    WHERE completed_at IS NULL AND expires_at > NOW();
```

---

### 5. user_activation_tokens

Tokens para activacion de cuentas nuevas.

#### Estructura

| Columna | Tipo | Nullable | Default | Descripcion |
|---------|------|----------|---------|-------------|
| `id` | UUID | NOT NULL | gen_random_uuid() | PK |
| `user_id` | UUID | NOT NULL | - | FK → users |
| `tenant_id` | UUID | NOT NULL | - | FK → tenants |
| `token_hash` | VARCHAR(255) | NOT NULL | - | Hash del token |
| `expires_at` | TIMESTAMPTZ | NOT NULL | - | Expiracion (24h) |
| `used_at` | TIMESTAMPTZ | NULL | - | Cuando se uso |
| `created_at` | TIMESTAMPTZ | NOT NULL | NOW() | Fecha creacion |

#### Constraints

```sql
CONSTRAINT pk_user_activation_tokens PRIMARY KEY (id),
CONSTRAINT fk_user_activation_tokens_user
    FOREIGN KEY (user_id) REFERENCES core_users.users(id) ON DELETE CASCADE,
CONSTRAINT fk_user_activation_tokens_tenant
    FOREIGN KEY (tenant_id) REFERENCES core_tenants.tenants(id) ON DELETE CASCADE
```

#### Indices

```sql
CREATE INDEX idx_user_activation_tokens_user ON core_users.user_activation_tokens(user_id);
CREATE INDEX idx_user_activation_tokens_active ON core_users.user_activation_tokens(user_id)
    WHERE used_at IS NULL AND expires_at > NOW();
```

---

### 6. user_roles (Junction Table)

Relacion muchos-a-muchos entre usuarios y roles.

#### Estructura

| Columna | Tipo | Nullable | Default | Descripcion |
|---------|------|----------|---------|-------------|
| `id` | UUID | NOT NULL | gen_random_uuid() | PK |
| `user_id` | UUID | NOT NULL | - | FK → users |
| `role_id` | UUID | NOT NULL | - | FK → roles |
| `tenant_id` | UUID | NOT NULL | - | FK → tenants |
| `created_at` | TIMESTAMPTZ | NOT NULL | NOW() | Fecha asignacion |
| `created_by` | UUID | NULL | - | Quien asigno |

#### Constraints

```sql
CONSTRAINT pk_user_roles PRIMARY KEY (id),
CONSTRAINT uk_user_roles UNIQUE (user_id, role_id),
CONSTRAINT fk_user_roles_user
    FOREIGN KEY (user_id) REFERENCES core_users.users(id) ON DELETE CASCADE,
CONSTRAINT fk_user_roles_role
    FOREIGN KEY (role_id) REFERENCES core_roles.roles(id) ON DELETE CASCADE,
CONSTRAINT fk_user_roles_tenant
    FOREIGN KEY (tenant_id) REFERENCES core_tenants.tenants(id) ON DELETE CASCADE,
CONSTRAINT fk_user_roles_created_by
    FOREIGN KEY (created_by) REFERENCES core_users.users(id) ON DELETE SET NULL
```

#### Indices

```sql
CREATE INDEX idx_user_roles_user ON core_users.user_roles(user_id);
CREATE INDEX idx_user_roles_role ON core_users.user_roles(role_id);
CREATE INDEX idx_user_roles_tenant ON core_users.user_roles(tenant_id);
```

---

## Row Level Security (RLS)

### Politicas de Seguridad

```sql
-- Habilitar RLS
ALTER TABLE core_users.users ENABLE ROW LEVEL SECURITY;
ALTER TABLE core_users.user_preferences ENABLE ROW LEVEL SECURITY;
ALTER TABLE core_users.user_avatars ENABLE ROW LEVEL SECURITY;
ALTER TABLE core_users.email_change_requests ENABLE ROW LEVEL SECURITY;
ALTER TABLE core_users.user_activation_tokens ENABLE ROW LEVEL SECURITY;
ALTER TABLE core_users.user_roles ENABLE ROW LEVEL SECURITY;

-- Politica: Usuarios solo ven usuarios de su tenant
CREATE POLICY tenant_isolation_users ON core_users.users
    FOR ALL
    USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

-- Politica: Usuario ve su propio perfil o es admin
CREATE POLICY self_or_admin_users ON core_users.users
    FOR SELECT
    USING (
        id = current_setting('app.current_user_id')::uuid
        OR current_setting('app.is_admin')::boolean = true
    );

-- Politica: Solo el usuario ve sus preferencias
CREATE POLICY owner_only_preferences ON core_users.user_preferences
    FOR ALL
    USING (user_id = current_setting('app.current_user_id')::uuid);

-- Politica: Solo el usuario ve sus avatares
CREATE POLICY owner_only_avatars ON core_users.user_avatars
    FOR ALL
    USING (user_id = current_setting('app.current_user_id')::uuid);

-- Politica: Solo el usuario ve sus solicitudes de cambio
CREATE POLICY owner_only_email_changes ON core_users.email_change_requests
    FOR ALL
    USING (user_id = current_setting('app.current_user_id')::uuid);
```

---

## Triggers

### 1. Actualizar updated_at automaticamente

```sql
CREATE OR REPLACE FUNCTION core_users.update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_users_updated_at
BEFORE UPDATE ON core_users.users
FOR EACH ROW
EXECUTE FUNCTION core_users.update_updated_at();

CREATE TRIGGER trg_user_preferences_updated_at
BEFORE UPDATE ON core_users.user_preferences
FOR EACH ROW
EXECUTE FUNCTION core_users.update_updated_at();
```

### 2. Crear preferencias por defecto al crear usuario

```sql
CREATE OR REPLACE FUNCTION core_users.create_default_preferences()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO core_users.user_preferences (user_id, tenant_id)
    VALUES (NEW.id, NEW.tenant_id);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_create_default_preferences
AFTER INSERT ON core_users.users
FOR EACH ROW
EXECUTE FUNCTION core_users.create_default_preferences();
```

### 3. Marcar avatar anterior como no actual

```sql
CREATE OR REPLACE FUNCTION core_users.update_current_avatar()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.is_current = true THEN
        UPDATE core_users.user_avatars
        SET is_current = false
        WHERE user_id = NEW.user_id
          AND id != NEW.id
          AND is_current = true;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_update_current_avatar
AFTER INSERT OR UPDATE ON core_users.user_avatars
FOR EACH ROW
WHEN (NEW.is_current = true)
EXECUTE FUNCTION core_users.update_current_avatar();
```

### 4. Sincronizar avatar_url en users

```sql
CREATE OR REPLACE FUNCTION core_users.sync_avatar_url()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.is_current = true THEN
        UPDATE core_users.users
        SET avatar_url = NEW.main_url,
            avatar_thumbnail_url = NEW.thumbnail_url
        WHERE id = NEW.user_id;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_sync_avatar_url
AFTER INSERT OR UPDATE ON core_users.user_avatars
FOR EACH ROW
WHEN (NEW.is_current = true)
EXECUTE FUNCTION core_users.sync_avatar_url();
```

---

## Funciones de Utilidad

### 1. Buscar usuarios por texto

```sql
CREATE OR REPLACE FUNCTION core_users.search_users(
    p_tenant_id UUID,
    p_search TEXT,
    p_limit INTEGER DEFAULT 20,
    p_offset INTEGER DEFAULT 0
)
RETURNS TABLE (
    id UUID,
    email VARCHAR,
    first_name VARCHAR,
    last_name VARCHAR,
    status user_status,
    avatar_thumbnail_url VARCHAR
) AS $$
BEGIN
    RETURN QUERY
    SELECT
        u.id,
        u.email,
        u.first_name,
        u.last_name,
        u.status,
        u.avatar_thumbnail_url
    FROM core_users.users u
    WHERE u.tenant_id = p_tenant_id
      AND u.deleted_at IS NULL
      AND (
          u.email ILIKE '%' || p_search || '%'
          OR u.first_name ILIKE '%' || p_search || '%'
          OR u.last_name ILIKE '%' || p_search || '%'
          OR CONCAT(u.first_name, ' ', u.last_name) ILIKE '%' || p_search || '%'
      )
    ORDER BY u.first_name, u.last_name
    LIMIT p_limit
    OFFSET p_offset;
END;
$$ LANGUAGE plpgsql STABLE;
```

### 2. Obtener usuario con roles

```sql
CREATE OR REPLACE FUNCTION core_users.get_user_with_roles(p_user_id UUID)
RETURNS TABLE (
    user_data JSONB,
    roles JSONB
) AS $$
BEGIN
    RETURN QUERY
    SELECT
        to_jsonb(u.*) - 'password_hash' AS user_data,
        COALESCE(
            jsonb_agg(
                jsonb_build_object('id', r.id, 'name', r.name)
            ) FILTER (WHERE r.id IS NOT NULL),
            '[]'::jsonb
        ) AS roles
    FROM core_users.users u
    LEFT JOIN core_users.user_roles ur ON u.id = ur.user_id
    LEFT JOIN core_roles.roles r ON ur.role_id = r.id
    WHERE u.id = p_user_id
    GROUP BY u.id;
END;
$$ LANGUAGE plpgsql STABLE;
```

---

## Scripts de Migracion

### Crear Schema

```sql
-- 001_create_schema_core_users.sql
CREATE SCHEMA IF NOT EXISTS core_users;

COMMENT ON SCHEMA core_users IS 'Schema para gestion de usuarios, perfiles y preferencias';

-- Crear enum
CREATE TYPE core_users.user_status AS ENUM (
    'pending_activation',
    'active',
    'inactive',
    'locked'
);
```

### Crear Tablas

```sql
-- 002_create_tables_core_users.sql

-- users
CREATE TABLE IF NOT EXISTS core_users.users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,
    email VARCHAR(255) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    avatar_url VARCHAR(500),
    avatar_thumbnail_url VARCHAR(500),
    status core_users.user_status NOT NULL DEFAULT 'pending_activation',
    is_active BOOLEAN NOT NULL DEFAULT false,
    email_verified_at TIMESTAMPTZ,
    last_login_at TIMESTAMPTZ,
    failed_login_attempts INTEGER NOT NULL DEFAULT 0,
    locked_until TIMESTAMPTZ,
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by UUID,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_by UUID,
    deleted_at TIMESTAMPTZ,
    deleted_by UUID,

    CONSTRAINT uk_users_tenant_email UNIQUE (tenant_id, email),
    CONSTRAINT chk_users_email_format
        CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
    CONSTRAINT chk_users_phone_format
        CHECK (phone IS NULL OR phone ~* '^\+[0-9]{10,15}$'),
    CONSTRAINT chk_users_failed_attempts
        CHECK (failed_login_attempts >= 0)
);

-- user_preferences
CREATE TABLE IF NOT EXISTS core_users.user_preferences (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL UNIQUE,
    tenant_id UUID NOT NULL,
    language VARCHAR(5) NOT NULL DEFAULT 'es',
    timezone VARCHAR(50) NOT NULL DEFAULT 'America/Mexico_City',
    date_format VARCHAR(20) NOT NULL DEFAULT 'DD/MM/YYYY',
    time_format VARCHAR(5) NOT NULL DEFAULT '24h',
    currency VARCHAR(3) NOT NULL DEFAULT 'MXN',
    number_format VARCHAR(10) NOT NULL DEFAULT 'es-MX',
    theme VARCHAR(10) NOT NULL DEFAULT 'system',
    sidebar_collapsed BOOLEAN NOT NULL DEFAULT false,
    compact_mode BOOLEAN NOT NULL DEFAULT false,
    font_size VARCHAR(10) NOT NULL DEFAULT 'medium',
    notifications JSONB NOT NULL DEFAULT '{}',
    dashboard JSONB NOT NULL DEFAULT '{}',
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    CONSTRAINT fk_user_preferences_user
        FOREIGN KEY (user_id) REFERENCES core_users.users(id) ON DELETE CASCADE,
    CONSTRAINT chk_user_preferences_language
        CHECK (language IN ('es', 'en', 'pt')),
    CONSTRAINT chk_user_preferences_theme
        CHECK (theme IN ('light', 'dark', 'system')),
    CONSTRAINT chk_user_preferences_font_size
        CHECK (font_size IN ('small', 'medium', 'large')),
    CONSTRAINT chk_user_preferences_time_format
        CHECK (time_format IN ('12h', '24h'))
);

-- user_avatars
CREATE TABLE IF NOT EXISTS core_users.user_avatars (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL,
    tenant_id UUID NOT NULL,
    original_url VARCHAR(500) NOT NULL,
    main_url VARCHAR(500) NOT NULL,
    thumbnail_url VARCHAR(500) NOT NULL,
    mime_type VARCHAR(50) NOT NULL,
    file_size INTEGER NOT NULL,
    is_current BOOLEAN NOT NULL DEFAULT true,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    CONSTRAINT fk_user_avatars_user
        FOREIGN KEY (user_id) REFERENCES core_users.users(id) ON DELETE CASCADE,
    CONSTRAINT chk_user_avatars_mime_type
        CHECK (mime_type IN ('image/jpeg', 'image/png', 'image/webp')),
    CONSTRAINT chk_user_avatars_file_size
        CHECK (file_size > 0 AND file_size <= 10485760)
);

-- email_change_requests
CREATE TABLE IF NOT EXISTS core_users.email_change_requests (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL,
    tenant_id UUID NOT NULL,
    current_email VARCHAR(255) NOT NULL,
    new_email VARCHAR(255) NOT NULL,
    token_hash VARCHAR(255) NOT NULL,
    expires_at TIMESTAMPTZ NOT NULL,
    completed_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    CONSTRAINT fk_email_change_requests_user
        FOREIGN KEY (user_id) REFERENCES core_users.users(id) ON DELETE CASCADE
);

-- user_activation_tokens
CREATE TABLE IF NOT EXISTS core_users.user_activation_tokens (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL,
    tenant_id UUID NOT NULL,
    token_hash VARCHAR(255) NOT NULL,
    expires_at TIMESTAMPTZ NOT NULL,
    used_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    CONSTRAINT fk_user_activation_tokens_user
        FOREIGN KEY (user_id) REFERENCES core_users.users(id) ON DELETE CASCADE
);

-- user_roles
CREATE TABLE IF NOT EXISTS core_users.user_roles (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL,
    role_id UUID NOT NULL,
    tenant_id UUID NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by UUID,

    CONSTRAINT uk_user_roles UNIQUE (user_id, role_id),
    CONSTRAINT fk_user_roles_user
        FOREIGN KEY (user_id) REFERENCES core_users.users(id) ON DELETE CASCADE,
    CONSTRAINT fk_user_roles_created_by
        FOREIGN KEY (created_by) REFERENCES core_users.users(id) ON DELETE SET NULL
);
```

---

## Consideraciones de Performance

| Tabla | Volumen Esperado | Estrategia |
|-------|------------------|------------|
| users | Medio (miles) | Indices por tenant, paginacion |
| user_preferences | 1:1 con users | Join eficiente por PK |
| user_avatars | Bajo (1-5 por user) | Cleanup de antiguos |
| email_change_requests | Bajo | TTL cleanup |
| user_activation_tokens | Bajo | TTL cleanup |
| user_roles | Bajo (1-5 por user) | Indices compuestos |

---

## Notas de Seguridad

1. **Nunca exponer password_hash** - Excluir en queries SELECT
2. **RLS obligatorio** - Aislamiento por tenant
3. **Soft delete** - Mantener historial para auditoria
4. **Tokens hasheados** - No almacenar tokens planos
5. **Validacion de email** - Prevenir inyeccion

---

## Historial de Cambios

| Version | Fecha | Autor | Cambios |
|---------|-------|-------|---------|
| 1.0 | 2025-12-05 | System | Creacion inicial |

---

## Aprobaciones

| Rol | Nombre | Fecha | Firma |
|-----|--------|-------|-------|
| DBA | - | - | [ ] |
| Tech Lead | - | - | [ ] |
| Security | - | - | [ ] |