SET lock_timeout = '10s';
SET statement_timeout = '120000';

CREATE SCHEMA IF NOT EXISTS auth;

CREATE TABLE IF NOT EXISTS auth.login_firmas_digitales (
    id_login_firma_digital BIGSERIAL PRIMARY KEY,
    business_id VARCHAR(100) NOT NULL,
    id_login INTEGER NOT NULL,
    mime_type VARCHAR(50) NOT NULL DEFAULT 'image/svg+xml',
    firma_svg_ciphertext TEXT NOT NULL,
    firma_svg_iv TEXT NOT NULL,
    firma_svg_tag TEXT NOT NULL,
    firma_svg_sha256 VARCHAR(64) NOT NULL,
    version_firma INTEGER NOT NULL DEFAULT 1,
    key_version VARCHAR(64) NOT NULL,
    validado_con_password_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by_login_id INTEGER NOT NULL,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_by_login_id INTEGER,
    soft_delete BOOLEAN NOT NULL DEFAULT FALSE,
    soft_delete_date TIMESTAMPTZ,
    deleted_by_login_id INTEGER,
    delete_reason TEXT,
    CONSTRAINT fk_login_firmas_digitales_login
        FOREIGN KEY (id_login)
        REFERENCES auth.login (id_login)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT fk_login_firmas_digitales_created_by
        FOREIGN KEY (created_by_login_id)
        REFERENCES auth.login (id_login)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT fk_login_firmas_digitales_updated_by
        FOREIGN KEY (updated_by_login_id)
        REFERENCES auth.login (id_login)
        ON UPDATE CASCADE
        ON DELETE SET NULL,
    CONSTRAINT fk_login_firmas_digitales_deleted_by
        FOREIGN KEY (deleted_by_login_id)
        REFERENCES auth.login (id_login)
        ON UPDATE CASCADE
        ON DELETE SET NULL,
    CONSTRAINT chk_login_firmas_digitales_business_id_not_empty
        CHECK (length(trim(business_id)) > 0),
    CONSTRAINT chk_login_firmas_digitales_id_login_positive
        CHECK (id_login > 0),
    CONSTRAINT chk_login_firmas_digitales_mime_type_svg
        CHECK (mime_type = 'image/svg+xml'),
    CONSTRAINT chk_login_firmas_digitales_ciphertext_not_empty
        CHECK (length(trim(firma_svg_ciphertext)) > 0),
    CONSTRAINT chk_login_firmas_digitales_iv_not_empty
        CHECK (length(trim(firma_svg_iv)) > 0),
    CONSTRAINT chk_login_firmas_digitales_tag_not_empty
        CHECK (length(trim(firma_svg_tag)) > 0),
    CONSTRAINT chk_login_firmas_digitales_sha256_hex
        CHECK (firma_svg_sha256 ~ '^[a-f0-9]{64}$'),
    CONSTRAINT chk_login_firmas_digitales_version_positive
        CHECK (version_firma > 0),
    CONSTRAINT chk_login_firmas_digitales_key_version_not_empty
        CHECK (length(trim(key_version)) > 0),
    CONSTRAINT chk_login_firmas_digitales_created_by_positive
        CHECK (created_by_login_id > 0),
    CONSTRAINT chk_login_firmas_digitales_updated_by_positive
        CHECK (updated_by_login_id IS NULL OR updated_by_login_id > 0),
    CONSTRAINT chk_login_firmas_digitales_deleted_by_positive
        CHECK (deleted_by_login_id IS NULL OR deleted_by_login_id > 0),
    CONSTRAINT chk_login_firmas_digitales_soft_delete_consistency
        CHECK (
            (soft_delete IS FALSE AND soft_delete_date IS NULL)
            OR (soft_delete IS TRUE AND soft_delete_date IS NOT NULL)
        )
);

CREATE INDEX IF NOT EXISTS login_firmas_digitales_business_id_idx
    ON auth.login_firmas_digitales (business_id);

CREATE INDEX IF NOT EXISTS login_firmas_digitales_business_login_idx
    ON auth.login_firmas_digitales (business_id, id_login);

CREATE INDEX IF NOT EXISTS login_firmas_digitales_business_updated_at_idx
    ON auth.login_firmas_digitales (business_id, updated_at DESC);

CREATE UNIQUE INDEX IF NOT EXISTS uq_login_firmas_digitales_activa_por_login
    ON auth.login_firmas_digitales (business_id, id_login)
    WHERE soft_delete IS NOT TRUE;

COMMENT ON TABLE auth.login_firmas_digitales IS 'Perfil reusable de firma digital SVG por login, cifrado y auditable.';
COMMENT ON COLUMN auth.login_firmas_digitales.firma_svg_ciphertext IS 'SVG saneado cifrado antes de persistirse en PostgreSQL.';
COMMENT ON COLUMN auth.login_firmas_digitales.firma_svg_iv IS 'Nonce o vector de inicializacion del cifrado autenticado del SVG.';
COMMENT ON COLUMN auth.login_firmas_digitales.firma_svg_tag IS 'Tag de autenticacion del cifrado autenticado del SVG.';
COMMENT ON COLUMN auth.login_firmas_digitales.firma_svg_sha256 IS 'SHA-256 hex del SVG saneado en texto plano, previo al cifrado.';
COMMENT ON COLUMN auth.login_firmas_digitales.version_firma IS 'Version monotona de la firma reusable del login.';
COMMENT ON COLUMN auth.login_firmas_digitales.key_version IS 'Version del material criptografico usado para cifrar la firma.';
COMMENT ON COLUMN auth.login_firmas_digitales.validado_con_password_at IS 'Momento de la reautorizacion con password actual que habilito la ultima mutacion.';
COMMENT ON COLUMN auth.login_firmas_digitales.delete_reason IS 'Razon explicita de invalidacion administrativa cuando aplica.';

ANALYZE auth.login_firmas_digitales;
