-- =====================================================
-- Migración: Crear tabla webauthn_credentials
-- Descripción: Almacena las credenciales WebAuthn/Passkeys de los usuarios
-- Fecha: 2024
-- =====================================================

-- Verificar si la tabla ya existe y crearla si no existe
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT FROM information_schema.tables 
        WHERE table_schema = 'public' 
        AND table_name = 'webauthn_credentials'
    ) THEN
        -- Crear la tabla
        CREATE TABLE webauthn_credentials (
            id_credential SERIAL PRIMARY KEY,
            fk_id_usuario INTEGER NOT NULL,
            credential_id TEXT NOT NULL UNIQUE,
            public_key TEXT NOT NULL,
            counter BIGINT NOT NULL DEFAULT 0,
            device_name VARCHAR(255),
            last_used TIMESTAMP,
            activo BOOLEAN DEFAULT true,
            f_reg TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            f_mod TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            CONSTRAINT fk_usuario_webauthn FOREIGN KEY (fk_id_usuario) 
                REFERENCES usuarios(id_usuario) ON DELETE CASCADE
        );

        -- Crear índices para mejorar el rendimiento
        CREATE INDEX idx_webauthn_usuario ON webauthn_credentials(fk_id_usuario);
        CREATE INDEX idx_webauthn_credential_id ON webauthn_credentials(credential_id);
        CREATE INDEX idx_webauthn_activo ON webauthn_credentials(activo) WHERE activo = true;
        CREATE INDEX idx_webauthn_last_used ON webauthn_credentials(last_used DESC);

        -- Agregar comentarios
        COMMENT ON TABLE webauthn_credentials IS 'Almacena las credenciales WebAuthn/Passkeys de los usuarios';
        COMMENT ON COLUMN webauthn_credentials.credential_id IS 'ID único de la credencial (base64url)';
        COMMENT ON COLUMN webauthn_credentials.public_key IS 'Clave pública de la credencial (base64)';
        COMMENT ON COLUMN webauthn_credentials.counter IS 'Contador para prevenir ataques de replay';
        COMMENT ON COLUMN webauthn_credentials.device_name IS 'Nombre descriptivo del dispositivo (ej: iPhone de Juan)';
        COMMENT ON COLUMN webauthn_credentials.last_used IS 'Fecha y hora del último uso de esta credencial';

        RAISE NOTICE 'Tabla webauthn_credentials creada exitosamente';
    ELSE
        RAISE NOTICE 'La tabla webauthn_credentials ya existe, omitiendo creación';
    END IF;
END $$;

-- Verificar y crear índices si no existen
DO $$
BEGIN
    -- Solo crear índices si la tabla existe
    IF EXISTS (
        SELECT FROM information_schema.tables 
        WHERE table_schema = 'public' 
        AND table_name = 'webauthn_credentials'
    ) THEN
        -- Índice por usuario
        IF NOT EXISTS (
            SELECT 1 FROM pg_indexes 
            WHERE tablename = 'webauthn_credentials' 
            AND indexname = 'idx_webauthn_usuario'
        ) THEN
            CREATE INDEX idx_webauthn_usuario ON webauthn_credentials(fk_id_usuario);
        END IF;

        -- Índice por credential_id
        IF NOT EXISTS (
            SELECT 1 FROM pg_indexes 
            WHERE tablename = 'webauthn_credentials' 
            AND indexname = 'idx_webauthn_credential_id'
        ) THEN
            CREATE INDEX idx_webauthn_credential_id ON webauthn_credentials(credential_id);
        END IF;

        -- Índice por activo (parcial)
        IF NOT EXISTS (
            SELECT 1 FROM pg_indexes 
            WHERE tablename = 'webauthn_credentials' 
            AND indexname = 'idx_webauthn_activo'
        ) THEN
            CREATE INDEX idx_webauthn_activo ON webauthn_credentials(activo) WHERE activo = true;
        END IF;

        -- Índice por last_used
        IF NOT EXISTS (
            SELECT 1 FROM pg_indexes 
            WHERE tablename = 'webauthn_credentials' 
            AND indexname = 'idx_webauthn_last_used'
        ) THEN
            CREATE INDEX idx_webauthn_last_used ON webauthn_credentials(last_used DESC);
        END IF;
    END IF;
END $$;

-- Función para actualizar f_mod automáticamente
CREATE OR REPLACE FUNCTION update_webauthn_credentials_modtime()
RETURNS TRIGGER AS $$
BEGIN
    NEW.f_mod = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger para actualizar f_mod
DROP TRIGGER IF EXISTS trigger_update_webauthn_credentials_modtime ON webauthn_credentials;
CREATE TRIGGER trigger_update_webauthn_credentials_modtime
    BEFORE UPDATE ON webauthn_credentials
    FOR EACH ROW
    EXECUTE FUNCTION update_webauthn_credentials_modtime();
