erp-construccion-database-v2/schemas/10-documents-schema-ddl.sql

-- ============================================================================
-- 10-documents-schema-ddl.sql
-- Schema: documents
-- ERP Construccion - Modulo Gestion Documental (MAE-016)
-- ============================================================================
-- Descripcion: Sistema de gestion documental incluyendo:
--   - Repositorio de documentos estructurado
--   - Control de versiones
--   - Flujos de aprobacion
--   - Permisos granulares
--   - Anotaciones sobre planos
--   - Integracion con almacenamiento externo (S3)
-- ============================================================================
-- Autor: Claude-Arquitecto-Orquestador
-- Fecha: 2026-01-25
-- Version: 1.0.0
-- ============================================================================

-- Crear schema si no existe
CREATE SCHEMA IF NOT EXISTS documents;

-- ============================================================================
-- ENUMS
-- ============================================================================

-- Tipo de documento
CREATE TYPE documents.document_type AS ENUM (
    'plan',           -- Planos arquitectonicos/estructurales
    'specification',  -- Especificaciones tecnicas
    'contract',       -- Contratos
    'permit',         -- Permisos y licencias
    'report',         -- Reportes
    'photograph',     -- Fotografias
    'drawing',        -- Dibujos y croquis
    'manual',         -- Manuales
    'procedure',      -- Procedimientos
    'form',           -- Formatos
    'correspondence', -- Correspondencia
    'invoice',        -- Facturas
    'estimate',       -- Estimaciones/Presupuestos
    'other'           -- Otro
);

-- Estado del documento
CREATE TYPE documents.document_status AS ENUM (
    'draft',          -- Borrador
    'pending_review', -- Pendiente de revision
    'in_review',      -- En revision
    'approved',       -- Aprobado
    'rejected',       -- Rechazado
    'obsolete',       -- Obsoleto
    'archived'        -- Archivado
);

-- Nivel de acceso
CREATE TYPE documents.access_level AS ENUM (
    'public',       -- Publico (todos en el tenant)
    'internal',     -- Interno (empleados)
    'confidential', -- Confidencial
    'restricted'    -- Restringido (solo autorizados)
);

-- Estado de version
CREATE TYPE documents.version_status AS ENUM (
    'current',    -- Version actual
    'superseded', -- Reemplazada
    'archived'    -- Archivada
);

-- Estado de flujo de aprobacion
CREATE TYPE documents.workflow_status AS ENUM (
    'draft',       -- Borrador
    'pending',     -- Pendiente
    'in_progress', -- En progreso
    'approved',    -- Aprobado
    'rejected',    -- Rechazado
    'cancelled'    -- Cancelado
);

-- Tipo de paso de aprobacion
CREATE TYPE documents.approval_step_type AS ENUM (
    'review',    -- Revision
    'approval',  -- Aprobacion
    'signature', -- Firma
    'comment'    -- Solo comentario
);

-- Accion de aprobacion
CREATE TYPE documents.approval_action AS ENUM (
    'approve',        -- Aprobar
    'reject',         -- Rechazar
    'request_changes' -- Solicitar cambios
);

-- Tipo de anotacion
CREATE TYPE documents.annotation_type AS ENUM (
    'comment',   -- Comentario
    'markup',    -- Marcado
    'highlight', -- Resaltado
    'arrow',     -- Flecha
    'dimension', -- Dimension
    'stamp',     -- Sello
    'signature'  -- Firma
);

-- ============================================================================
-- TABLAS
-- ============================================================================

-- ----------------------------------------------------------------------------
-- 1. Categorias de Documentos
-- ----------------------------------------------------------------------------
CREATE TABLE documents.document_categories (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,

    -- Informacion basica
    code VARCHAR(50) NOT NULL,
    name VARCHAR(100) NOT NULL,
    description TEXT,

    -- Jerarquia
    parent_id UUID REFERENCES documents.document_categories(id),
    level INTEGER NOT NULL DEFAULT 1,
    path VARCHAR(500), -- Path completo separado por /

    -- Configuracion
    default_access_level documents.access_level DEFAULT 'internal',
    requires_approval BOOLEAN DEFAULT FALSE,
    retention_days INTEGER, -- Tiempo de retencion en dias
    allowed_extensions VARCHAR(255)[], -- Extensiones permitidas

    -- Icono y color
    icon VARCHAR(50),
    color VARCHAR(20),

    -- Estado
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    sort_order INTEGER DEFAULT 0,

    -- Metadatos
    metadata JSONB,

    -- Auditoria
    created_by UUID,
    updated_by UUID,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    deleted_at TIMESTAMPTZ,

    CONSTRAINT uq_document_categories_tenant_code UNIQUE (tenant_id, code)
);

-- ----------------------------------------------------------------------------
-- 2. Documentos (Registro Principal)
-- ----------------------------------------------------------------------------
CREATE TABLE documents.documents (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,

    -- Identificacion
    document_code VARCHAR(100) NOT NULL,
    title VARCHAR(500) NOT NULL,
    description TEXT,

    -- Clasificacion
    category_id UUID REFERENCES documents.document_categories(id),
    document_type documents.document_type NOT NULL,
    status documents.document_status NOT NULL DEFAULT 'draft',
    access_level documents.access_level NOT NULL DEFAULT 'internal',

    -- Version actual
    current_version_id UUID, -- Se actualiza con trigger
    current_version_number VARCHAR(20) DEFAULT '1.0',

    -- Proyecto/Contexto
    project_id UUID,
    project_code VARCHAR(50),
    project_name VARCHAR(255),

    -- Metadatos del documento
    author VARCHAR(255),
    keywords VARCHAR(255)[],
    tags VARCHAR(100)[],

    -- Fechas importantes
    document_date DATE,
    effective_date DATE,
    expiry_date DATE,
    review_date DATE,

    -- Origen
    source VARCHAR(100), -- internal, external, contractor
    external_reference VARCHAR(255), -- Referencia externa si aplica
    original_filename VARCHAR(500),

    -- Relaciones
    parent_document_id UUID REFERENCES documents.documents(id), -- Documento padre
    related_documents UUID[], -- Array de IDs relacionados

    -- Flujo de aprobacion
    requires_approval BOOLEAN DEFAULT FALSE,
    current_workflow_id UUID,
    approved_by_id UUID,
    approved_at TIMESTAMPTZ,

    -- Estadisticas
    view_count INTEGER DEFAULT 0,
    download_count INTEGER DEFAULT 0,
    last_accessed_at TIMESTAMPTZ,

    -- Flags
    is_template BOOLEAN DEFAULT FALSE,
    is_locked BOOLEAN DEFAULT FALSE,
    locked_by_id UUID,
    locked_at TIMESTAMPTZ,

    -- Notas y metadatos
    notes TEXT,
    custom_fields JSONB,
    metadata JSONB,

    -- Auditoria
    created_by UUID,
    updated_by UUID,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    deleted_at TIMESTAMPTZ,

    CONSTRAINT uq_documents_tenant_code UNIQUE (tenant_id, document_code)
);

-- ----------------------------------------------------------------------------
-- 3. Versiones de Documentos
-- ----------------------------------------------------------------------------
CREATE TABLE documents.document_versions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,

    -- Documento padre
    document_id UUID NOT NULL REFERENCES documents.documents(id) ON DELETE CASCADE,

    -- Version
    version_number VARCHAR(20) NOT NULL,
    version_label VARCHAR(100), -- Etiqueta opcional (ej: "Final", "Para construccion")
    status documents.version_status NOT NULL DEFAULT 'current',

    -- Archivo
    file_name VARCHAR(500) NOT NULL,
    file_extension VARCHAR(20) NOT NULL,
    file_size_bytes BIGINT NOT NULL,
    mime_type VARCHAR(100),
    checksum_md5 VARCHAR(32),
    checksum_sha256 VARCHAR(64),

    -- Almacenamiento
    storage_provider VARCHAR(50) NOT NULL DEFAULT 's3', -- s3, azure, local
    storage_bucket VARCHAR(255),
    storage_key VARCHAR(1000) NOT NULL, -- Path en el storage
    storage_url VARCHAR(2000), -- URL de acceso
    thumbnail_url VARCHAR(2000),
    preview_url VARCHAR(2000),

    -- Procesamiento
    is_processed BOOLEAN DEFAULT FALSE,
    ocr_text TEXT, -- Texto extraido por OCR
    extracted_metadata JSONB,

    -- Paginas (para PDFs/planos)
    page_count INTEGER,
    page_dimensions JSONB, -- Dimensiones por pagina

    -- Cambios
    change_summary TEXT,
    change_type VARCHAR(50), -- major, minor, patch

    -- Subido por
    uploaded_by_id UUID,
    uploaded_by_name VARCHAR(255),
    upload_source VARCHAR(50), -- web, mobile, api, email

    -- Auditoria
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    superseded_at TIMESTAMPTZ,
    superseded_by_version_id UUID
);

-- ----------------------------------------------------------------------------
-- 4. Permisos de Documentos
-- ----------------------------------------------------------------------------
CREATE TABLE documents.document_permissions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,

    -- Objeto
    document_id UUID REFERENCES documents.documents(id) ON DELETE CASCADE,
    category_id UUID REFERENCES documents.document_categories(id) ON DELETE CASCADE,

    -- Sujeto (quien tiene el permiso)
    user_id UUID,
    role_id UUID,
    team_id UUID,

    -- Permisos
    can_view BOOLEAN DEFAULT FALSE,
    can_download BOOLEAN DEFAULT FALSE,
    can_edit BOOLEAN DEFAULT FALSE,
    can_delete BOOLEAN DEFAULT FALSE,
    can_share BOOLEAN DEFAULT FALSE,
    can_approve BOOLEAN DEFAULT FALSE,
    can_annotate BOOLEAN DEFAULT FALSE,

    -- Vigencia
    valid_from TIMESTAMPTZ,
    valid_until TIMESTAMPTZ,

    -- Otorgado por
    granted_by_id UUID,
    granted_at TIMESTAMPTZ DEFAULT NOW(),

    -- Auditoria
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    CONSTRAINT chk_permission_target CHECK (
        (document_id IS NOT NULL AND category_id IS NULL) OR
        (document_id IS NULL AND category_id IS NOT NULL)
    ),
    CONSTRAINT chk_permission_subject CHECK (
        (user_id IS NOT NULL AND role_id IS NULL AND team_id IS NULL) OR
        (user_id IS NULL AND role_id IS NOT NULL AND team_id IS NULL) OR
        (user_id IS NULL AND role_id IS NULL AND team_id IS NOT NULL)
    )
);

-- ----------------------------------------------------------------------------
-- 5. Flujos de Aprobacion (Definicion)
-- ----------------------------------------------------------------------------
CREATE TABLE documents.approval_workflows (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,

    -- Identificacion
    workflow_code VARCHAR(50) NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT,

    -- Aplica a
    category_id UUID REFERENCES documents.document_categories(id),
    document_type documents.document_type,

    -- Pasos (JSON array con la definicion)
    steps JSONB NOT NULL, -- [{step_number, name, type, approvers[], required_count}]

    -- Configuracion
    allow_parallel BOOLEAN DEFAULT FALSE, -- Pasos pueden ser paralelos
    allow_skip BOOLEAN DEFAULT FALSE, -- Permitir saltar pasos
    auto_archive_on_approval BOOLEAN DEFAULT FALSE,

    -- Estado
    is_active BOOLEAN NOT NULL DEFAULT TRUE,

    -- Metadatos
    metadata JSONB,

    -- Auditoria
    created_by UUID,
    updated_by UUID,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    deleted_at TIMESTAMPTZ,

    CONSTRAINT uq_approval_workflows_tenant_code UNIQUE (tenant_id, workflow_code)
);

-- ----------------------------------------------------------------------------
-- 6. Instancias de Flujo de Aprobacion
-- ----------------------------------------------------------------------------
CREATE TABLE documents.approval_instances (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,

    -- Flujo y documento
    workflow_id UUID NOT NULL REFERENCES documents.approval_workflows(id),
    document_id UUID NOT NULL REFERENCES documents.documents(id),
    version_id UUID REFERENCES documents.document_versions(id),

    -- Estado
    status documents.workflow_status NOT NULL DEFAULT 'draft',
    current_step INTEGER DEFAULT 1,
    total_steps INTEGER NOT NULL,

    -- Fechas
    started_at TIMESTAMPTZ,
    completed_at TIMESTAMPTZ,
    due_date TIMESTAMPTZ,

    -- Iniciador
    initiated_by_id UUID,
    initiated_by_name VARCHAR(255),

    -- Resultado final
    final_action documents.approval_action,
    final_comments TEXT,
    final_approver_id UUID,

    -- Notas
    notes TEXT,
    metadata JSONB,

    -- Auditoria
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- ----------------------------------------------------------------------------
-- 7. Pasos de Aprobacion (Instancias)
-- ----------------------------------------------------------------------------
CREATE TABLE documents.approval_steps (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,

    -- Instancia
    instance_id UUID NOT NULL REFERENCES documents.approval_instances(id) ON DELETE CASCADE,

    -- Paso
    step_number INTEGER NOT NULL,
    step_name VARCHAR(255) NOT NULL,
    step_type documents.approval_step_type NOT NULL,

    -- Aprobadores requeridos
    required_approvers UUID[], -- IDs de usuarios requeridos
    required_count INTEGER DEFAULT 1, -- Cuantas aprobaciones se necesitan

    -- Estado
    status documents.workflow_status NOT NULL DEFAULT 'pending',

    -- Fechas
    started_at TIMESTAMPTZ,
    completed_at TIMESTAMPTZ,
    due_date TIMESTAMPTZ,

    -- Resultado
    action_taken documents.approval_action,
    approved_by UUID[],
    rejected_by UUID[],

    -- Notas
    notes TEXT,

    -- Auditoria
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- ----------------------------------------------------------------------------
-- 8. Acciones de Aprobacion
-- ----------------------------------------------------------------------------
CREATE TABLE documents.approval_actions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,

    -- Paso
    step_id UUID NOT NULL REFERENCES documents.approval_steps(id) ON DELETE CASCADE,
    instance_id UUID NOT NULL REFERENCES documents.approval_instances(id),

    -- Accion
    action documents.approval_action NOT NULL,
    comments TEXT,

    -- Usuario que toma la accion
    user_id UUID NOT NULL,
    user_name VARCHAR(255),

    -- Firma digital (si aplica)
    signature_data TEXT,
    signature_timestamp TIMESTAMPTZ,
    signature_ip VARCHAR(45),

    -- Auditoria
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- ----------------------------------------------------------------------------
-- 9. Anotaciones sobre Documentos
-- ----------------------------------------------------------------------------
CREATE TABLE documents.annotations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,

    -- Documento y version
    document_id UUID NOT NULL REFERENCES documents.documents(id) ON DELETE CASCADE,
    version_id UUID REFERENCES documents.document_versions(id),

    -- Tipo
    annotation_type documents.annotation_type NOT NULL,

    -- Ubicacion en el documento
    page_number INTEGER,
    x_position DECIMAL(10,4),
    y_position DECIMAL(10,4),
    width DECIMAL(10,4),
    height DECIMAL(10,4),
    rotation DECIMAL(5,2),

    -- Contenido
    content TEXT, -- Texto del comentario o SVG del dibujo
    style JSONB, -- Estilos (color, grosor, fuente, etc.)

    -- Respuesta a otra anotacion
    parent_annotation_id UUID REFERENCES documents.annotations(id),

    -- Estado
    is_resolved BOOLEAN DEFAULT FALSE,
    resolved_by_id UUID,
    resolved_at TIMESTAMPTZ,

    -- Visibilidad
    is_private BOOLEAN DEFAULT FALSE,
    visible_to UUID[], -- Array de user_ids si es privado

    -- Autor
    author_id UUID NOT NULL,
    author_name VARCHAR(255),

    -- Auditoria
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    deleted_at TIMESTAMPTZ
);

-- ----------------------------------------------------------------------------
-- 10. Historial de Acceso a Documentos
-- ----------------------------------------------------------------------------
CREATE TABLE documents.access_logs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,

    -- Documento
    document_id UUID NOT NULL REFERENCES documents.documents(id),
    version_id UUID REFERENCES documents.document_versions(id),

    -- Accion
    action VARCHAR(50) NOT NULL, -- view, download, print, share, edit
    action_details JSONB,

    -- Usuario
    user_id UUID NOT NULL,
    user_name VARCHAR(255),
    user_ip VARCHAR(45),
    user_agent TEXT,

    -- Contexto
    session_id VARCHAR(100),
    request_source VARCHAR(50), -- web, mobile, api

    -- Timestamp
    accessed_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- ----------------------------------------------------------------------------
-- 11. Compartidos Externos
-- ----------------------------------------------------------------------------
CREATE TABLE documents.document_shares (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,

    -- Documento
    document_id UUID NOT NULL REFERENCES documents.documents(id) ON DELETE CASCADE,
    version_id UUID REFERENCES documents.document_versions(id),

    -- Link compartido
    share_token VARCHAR(100) NOT NULL UNIQUE,
    share_url VARCHAR(2000),

    -- Destinatario
    shared_with_email VARCHAR(255),
    shared_with_name VARCHAR(255),

    -- Permisos
    can_download BOOLEAN DEFAULT FALSE,
    can_comment BOOLEAN DEFAULT FALSE,

    -- Seguridad
    password_hash VARCHAR(255), -- Si requiere password
    max_downloads INTEGER,
    download_count INTEGER DEFAULT 0,

    -- Vigencia
    expires_at TIMESTAMPTZ,
    is_revoked BOOLEAN DEFAULT FALSE,
    revoked_at TIMESTAMPTZ,
    revoked_by_id UUID,

    -- Compartido por
    shared_by_id UUID NOT NULL,
    shared_by_name VARCHAR(255),

    -- Notificacion
    notification_sent BOOLEAN DEFAULT FALSE,
    notification_sent_at TIMESTAMPTZ,

    -- Auditoria
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    last_accessed_at TIMESTAMPTZ
);

-- ============================================================================
-- INDICES
-- ============================================================================

-- Document Categories
CREATE INDEX idx_document_categories_tenant ON documents.document_categories(tenant_id);
CREATE INDEX idx_document_categories_parent ON documents.document_categories(parent_id);

-- Documents
CREATE INDEX idx_documents_tenant ON documents.documents(tenant_id);
CREATE INDEX idx_documents_tenant_category ON documents.documents(tenant_id, category_id);
CREATE INDEX idx_documents_tenant_type ON documents.documents(tenant_id, document_type);
CREATE INDEX idx_documents_tenant_status ON documents.documents(tenant_id, status);
CREATE INDEX idx_documents_tenant_project ON documents.documents(tenant_id, project_id);
CREATE INDEX idx_documents_keywords ON documents.documents USING gin(keywords);
CREATE INDEX idx_documents_tags ON documents.documents USING gin(tags);
CREATE INDEX idx_documents_fulltext ON documents.documents USING gin(
    to_tsvector('spanish', coalesce(title, '') || ' ' || coalesce(description, ''))
);

-- Document Versions
CREATE INDEX idx_document_versions_tenant ON documents.document_versions(tenant_id);
CREATE INDEX idx_document_versions_document ON documents.document_versions(document_id);
CREATE INDEX idx_document_versions_status ON documents.document_versions(status);

-- Document Permissions
CREATE INDEX idx_document_permissions_document ON documents.document_permissions(document_id);
CREATE INDEX idx_document_permissions_category ON documents.document_permissions(category_id);
CREATE INDEX idx_document_permissions_user ON documents.document_permissions(user_id);
CREATE INDEX idx_document_permissions_role ON documents.document_permissions(role_id);

-- Approval Workflows
CREATE INDEX idx_approval_workflows_tenant ON documents.approval_workflows(tenant_id);
CREATE INDEX idx_approval_workflows_category ON documents.approval_workflows(category_id);

-- Approval Instances
CREATE INDEX idx_approval_instances_tenant ON documents.approval_instances(tenant_id);
CREATE INDEX idx_approval_instances_document ON documents.approval_instances(document_id);
CREATE INDEX idx_approval_instances_status ON documents.approval_instances(status);

-- Approval Steps
CREATE INDEX idx_approval_steps_instance ON documents.approval_steps(instance_id);
CREATE INDEX idx_approval_steps_status ON documents.approval_steps(status);

-- Approval Actions
CREATE INDEX idx_approval_actions_step ON documents.approval_actions(step_id);
CREATE INDEX idx_approval_actions_user ON documents.approval_actions(user_id);

-- Annotations
CREATE INDEX idx_annotations_tenant ON documents.annotations(tenant_id);
CREATE INDEX idx_annotations_document ON documents.annotations(document_id);
CREATE INDEX idx_annotations_version ON documents.annotations(version_id);
CREATE INDEX idx_annotations_author ON documents.annotations(author_id);

-- Access Logs
CREATE INDEX idx_access_logs_tenant ON documents.access_logs(tenant_id);
CREATE INDEX idx_access_logs_document ON documents.access_logs(document_id);
CREATE INDEX idx_access_logs_user ON documents.access_logs(user_id);
CREATE INDEX idx_access_logs_accessed ON documents.access_logs(accessed_at);

-- Document Shares
CREATE INDEX idx_document_shares_tenant ON documents.document_shares(tenant_id);
CREATE INDEX idx_document_shares_document ON documents.document_shares(document_id);
CREATE INDEX idx_document_shares_token ON documents.document_shares(share_token);

-- ============================================================================
-- ROW LEVEL SECURITY (RLS)
-- ============================================================================

ALTER TABLE documents.document_categories ENABLE ROW LEVEL SECURITY;
ALTER TABLE documents.documents ENABLE ROW LEVEL SECURITY;
ALTER TABLE documents.document_versions ENABLE ROW LEVEL SECURITY;
ALTER TABLE documents.document_permissions ENABLE ROW LEVEL SECURITY;
ALTER TABLE documents.approval_workflows ENABLE ROW LEVEL SECURITY;
ALTER TABLE documents.approval_instances ENABLE ROW LEVEL SECURITY;
ALTER TABLE documents.approval_steps ENABLE ROW LEVEL SECURITY;
ALTER TABLE documents.approval_actions ENABLE ROW LEVEL SECURITY;
ALTER TABLE documents.annotations ENABLE ROW LEVEL SECURITY;
ALTER TABLE documents.access_logs ENABLE ROW LEVEL SECURITY;
ALTER TABLE documents.document_shares ENABLE ROW LEVEL SECURITY;

-- ============================================================================
-- TRIGGERS DE AUDITORIA
-- ============================================================================

CREATE OR REPLACE FUNCTION documents.set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_document_categories_updated_at
    BEFORE UPDATE ON documents.document_categories
    FOR EACH ROW EXECUTE FUNCTION documents.set_updated_at();

CREATE TRIGGER trg_documents_updated_at
    BEFORE UPDATE ON documents.documents
    FOR EACH ROW EXECUTE FUNCTION documents.set_updated_at();

CREATE TRIGGER trg_document_permissions_updated_at
    BEFORE UPDATE ON documents.document_permissions
    FOR EACH ROW EXECUTE FUNCTION documents.set_updated_at();

CREATE TRIGGER trg_approval_workflows_updated_at
    BEFORE UPDATE ON documents.approval_workflows
    FOR EACH ROW EXECUTE FUNCTION documents.set_updated_at();

CREATE TRIGGER trg_approval_instances_updated_at
    BEFORE UPDATE ON documents.approval_instances
    FOR EACH ROW EXECUTE FUNCTION documents.set_updated_at();

CREATE TRIGGER trg_approval_steps_updated_at
    BEFORE UPDATE ON documents.approval_steps
    FOR EACH ROW EXECUTE FUNCTION documents.set_updated_at();

CREATE TRIGGER trg_annotations_updated_at
    BEFORE UPDATE ON documents.annotations
    FOR EACH ROW EXECUTE FUNCTION documents.set_updated_at();

-- ============================================================================
-- FUNCIONES AUXILIARES
-- ============================================================================

-- Actualizar version actual del documento
CREATE OR REPLACE FUNCTION documents.update_current_version()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.status = 'current' THEN
        -- Marcar versiones anteriores como superseded
        UPDATE documents.document_versions
        SET status = 'superseded',
            superseded_at = NOW(),
            superseded_by_version_id = NEW.id
        WHERE document_id = NEW.document_id
          AND id != NEW.id
          AND status = 'current';

        -- Actualizar documento con version actual
        UPDATE documents.documents
        SET current_version_id = NEW.id,
            current_version_number = NEW.version_number,
            updated_at = NOW()
        WHERE id = NEW.document_id;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_update_current_version
    AFTER INSERT OR UPDATE ON documents.document_versions
    FOR EACH ROW EXECUTE FUNCTION documents.update_current_version();

-- Incrementar contador de vistas
CREATE OR REPLACE FUNCTION documents.increment_view_count()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.action = 'view' THEN
        UPDATE documents.documents
        SET view_count = view_count + 1,
            last_accessed_at = NOW()
        WHERE id = NEW.document_id;
    ELSIF NEW.action = 'download' THEN
        UPDATE documents.documents
        SET download_count = download_count + 1,
            last_accessed_at = NOW()
        WHERE id = NEW.document_id;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_increment_view_count
    AFTER INSERT ON documents.access_logs
    FOR EACH ROW EXECUTE FUNCTION documents.increment_view_count();

-- Actualizar path de categoria
CREATE OR REPLACE FUNCTION documents.update_category_path()
RETURNS TRIGGER AS $$
DECLARE
    v_parent_path VARCHAR(500);
BEGIN
    IF NEW.parent_id IS NULL THEN
        NEW.path := '/' || NEW.code;
        NEW.level := 1;
    ELSE
        SELECT path, level + 1 INTO v_parent_path, NEW.level
        FROM documents.document_categories
        WHERE id = NEW.parent_id;

        NEW.path := v_parent_path || '/' || NEW.code;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_update_category_path
    BEFORE INSERT OR UPDATE ON documents.document_categories
    FOR EACH ROW EXECUTE FUNCTION documents.update_category_path();

-- ============================================================================
-- COMENTARIOS DE DOCUMENTACION
-- ============================================================================

COMMENT ON SCHEMA documents IS 'Modulo de Gestion Documental (MAE-016) - ERP Construccion';

COMMENT ON TABLE documents.document_categories IS 'Categorias jerarquicas para clasificar documentos';
COMMENT ON TABLE documents.documents IS 'Registro principal de documentos';
COMMENT ON TABLE documents.document_versions IS 'Versiones de documentos con archivos';
COMMENT ON TABLE documents.document_permissions IS 'Permisos granulares por documento o categoria';
COMMENT ON TABLE documents.approval_workflows IS 'Definicion de flujos de aprobacion';
COMMENT ON TABLE documents.approval_instances IS 'Instancias de flujos de aprobacion activos';
COMMENT ON TABLE documents.approval_steps IS 'Pasos de aprobacion por instancia';
COMMENT ON TABLE documents.approval_actions IS 'Acciones tomadas en pasos de aprobacion';
COMMENT ON TABLE documents.annotations IS 'Anotaciones y comentarios sobre documentos';
COMMENT ON TABLE documents.access_logs IS 'Historial de acceso a documentos';
COMMENT ON TABLE documents.document_shares IS 'Links de compartido externo';

-- ============================================================================
-- FK CONSTRAINTS ADICIONALES
-- NOTA: Fortalecer referencias internas y externas del schema documents.
-- ============================================================================

-- FK: documents.parent_document_id → documents.documents (auto-referencia)
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'fk_documents_parent'
    ) THEN
        ALTER TABLE documents.documents
        ADD CONSTRAINT fk_documents_parent
        FOREIGN KEY (parent_document_id) REFERENCES documents.documents(id)
        ON DELETE SET NULL;
        RAISE NOTICE 'FK creada: documents.parent_document_id → documents.documents';
    END IF;
EXCEPTION WHEN OTHERS THEN
    RAISE NOTICE 'FK documents.parent_document_id no creada: %', SQLERRM;
END $$;

-- FK: access_logs.document_id → documents.documents (para mantener logs aunque doc se elimine)
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'fk_access_logs_document'
    ) THEN
        ALTER TABLE documents.access_logs
        ADD CONSTRAINT fk_access_logs_document
        FOREIGN KEY (document_id) REFERENCES documents.documents(id)
        ON DELETE CASCADE;
        RAISE NOTICE 'FK creada: access_logs.document_id → documents.documents';
    END IF;
EXCEPTION WHEN OTHERS THEN
    RAISE NOTICE 'FK access_logs.document_id no creada: %', SQLERRM;
END $$;

-- FK: access_logs.user_id → auth.users
DO $$
BEGIN
    IF EXISTS (SELECT 1 FROM pg_tables WHERE schemaname = 'auth' AND tablename = 'users') THEN
        IF NOT EXISTS (
            SELECT 1 FROM pg_constraint WHERE conname = 'fk_access_logs_user'
        ) THEN
            ALTER TABLE documents.access_logs
            ADD CONSTRAINT fk_access_logs_user
            FOREIGN KEY (user_id) REFERENCES auth.users(id)
            ON DELETE SET NULL;
            RAISE NOTICE 'FK creada: access_logs.user_id → auth.users';
        END IF;
    END IF;
EXCEPTION WHEN OTHERS THEN
    RAISE NOTICE 'FK access_logs.user_id no creada: %', SQLERRM;
END $$;

-- FK: document_shares.shared_by_id → auth.users
DO $$
BEGIN
    IF EXISTS (SELECT 1 FROM pg_tables WHERE schemaname = 'auth' AND tablename = 'users') THEN
        IF NOT EXISTS (
            SELECT 1 FROM pg_constraint WHERE conname = 'fk_document_shares_shared_by'
        ) THEN
            ALTER TABLE documents.document_shares
            ADD CONSTRAINT fk_document_shares_shared_by
            FOREIGN KEY (shared_by_id) REFERENCES auth.users(id)
            ON DELETE SET NULL;
            RAISE NOTICE 'FK creada: document_shares.shared_by_id → auth.users';
        END IF;
    END IF;
EXCEPTION WHEN OTHERS THEN
    RAISE NOTICE 'FK document_shares.shared_by_id no creada: %', SQLERRM;
END $$;

-- Índices adicionales para campos sin índice
CREATE INDEX IF NOT EXISTS idx_documents_parent ON documents.documents(parent_document_id);
CREATE INDEX IF NOT EXISTS idx_document_shares_shared_by ON documents.document_shares(shared_by_id);

-- ============================================================================
-- GAP-006: FIRMAS DIGITALES
-- ============================================================================

-- Tipo de firma digital
CREATE TYPE documents.signature_type AS ENUM (
    'simple',     -- Firma simple (click to sign)
    'advanced',   -- Firma avanzada (con certificado)
    'qualified'   -- Firma cualificada (certificado oficial)
);

-- Estado de la firma
CREATE TYPE documents.signature_status AS ENUM (
    'pending',    -- Pendiente de firma
    'signed',     -- Firmado
    'rejected',   -- Rechazado
    'expired',    -- Expirado
    'revoked'     -- Revocado
);

-- Tabla de firmas digitales
CREATE TABLE IF NOT EXISTS documents.digital_signatures (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,

    -- Documento firmado
    document_id UUID NOT NULL,
    version_id UUID,

    -- Firmante
    signer_id UUID NOT NULL,
    signer_name VARCHAR(200) NOT NULL,
    signer_email VARCHAR(255),
    signer_role VARCHAR(100),

    -- Tipo y estado
    signature_type documents.signature_type NOT NULL DEFAULT 'simple',
    status documents.signature_status NOT NULL DEFAULT 'pending',

    -- Datos de firma
    signature_data TEXT,
    signature_hash VARCHAR(128),
    hash_algorithm VARCHAR(20) DEFAULT 'SHA-256',

    -- Certificado (para firmas avanzadas/cualificadas)
    certificate_info JSONB,
    certificate_serial VARCHAR(100),
    certificate_issuer VARCHAR(255),
    certificate_valid_from TIMESTAMPTZ,
    certificate_valid_to TIMESTAMPTZ,

    -- Timestamps
    requested_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    signed_at TIMESTAMPTZ,
    expires_at TIMESTAMPTZ,

    -- Contexto de firma
    ip_address INET,
    user_agent TEXT,
    geolocation JSONB,

    -- Validación
    is_valid BOOLEAN DEFAULT true,
    validation_errors JSONB,
    last_validated_at TIMESTAMPTZ,

    -- Razón (para rechazo/revocación)
    reason TEXT,

    -- Metadatos
    metadata JSONB DEFAULT '{}',

    -- Auditoría
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    -- Foreign keys
    CONSTRAINT fk_digital_signatures_tenant
        FOREIGN KEY (tenant_id) REFERENCES auth.tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_digital_signatures_document
        FOREIGN KEY (document_id) REFERENCES documents.documents(id) ON DELETE CASCADE,
    CONSTRAINT fk_digital_signatures_version
        FOREIGN KEY (version_id) REFERENCES documents.document_versions(id) ON DELETE SET NULL
);

-- Índices para digital_signatures
CREATE INDEX IF NOT EXISTS idx_digital_signatures_tenant ON documents.digital_signatures(tenant_id);
CREATE INDEX IF NOT EXISTS idx_digital_signatures_document ON documents.digital_signatures(document_id);
CREATE INDEX IF NOT EXISTS idx_digital_signatures_signer ON documents.digital_signatures(signer_id);
CREATE INDEX IF NOT EXISTS idx_digital_signatures_status ON documents.digital_signatures(tenant_id, status);
CREATE INDEX IF NOT EXISTS idx_digital_signatures_signed_at ON documents.digital_signatures(signed_at);

-- RLS para digital_signatures
ALTER TABLE documents.digital_signatures ENABLE ROW LEVEL SECURITY;

CREATE POLICY digital_signatures_tenant_isolation ON documents.digital_signatures
    FOR ALL
    USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);

-- FK opcional: signer_id → auth.users
DO $$
BEGIN
    IF EXISTS (SELECT 1 FROM pg_tables WHERE schemaname = 'auth' AND tablename = 'users') THEN
        IF NOT EXISTS (
            SELECT 1 FROM pg_constraint WHERE conname = 'fk_digital_signatures_signer'
        ) THEN
            ALTER TABLE documents.digital_signatures
            ADD CONSTRAINT fk_digital_signatures_signer
            FOREIGN KEY (signer_id) REFERENCES auth.users(id)
            ON DELETE SET NULL;
            RAISE NOTICE 'FK creada: digital_signatures.signer_id → auth.users';
        END IF;
    END IF;
EXCEPTION WHEN OTHERS THEN
    RAISE NOTICE 'FK digital_signatures.signer_id no creada: %', SQLERRM;
END $$;

COMMENT ON TABLE documents.digital_signatures IS 'GAP-006: Firmas digitales para documentos';

-- ============================================================================
-- FIN DEL SCRIPT
-- FK constraints adicionales: 5 (incluyendo digital_signatures)
-- ============================================================================