-- =====================================================
-- 00_extraer_menu_tarjetas_BD_ANTIGUA.sql
-- =====================================================
-- INSTRUCCIONES:
--   1. Ejecutar en la BASE DE DATOS ANTERIOR (la que funciona)
--   2. Revisar los resultados de cada SELECT
--   3. Copiar los datos y ejecutar los INSERTs en la BD nueva
-- =====================================================

USE [Sis2000]; -- Cambiar si la BD anterior tiene otro nombre
GO

-- =====================================================
-- PASO 1: INSERTs para seprogramas
-- =====================================================
SELECT
    'IF NOT EXISTS (SELECT 1 FROM seprogramas WHERE cprograma = ''' + LTRIM(RTRIM(cprograma)) + ''') ' +
    'INSERT INTO seprogramas (cprograma, u_version, xprograma, xruta, istatus, fingreso) VALUES (''' +
    LTRIM(RTRIM(cprograma))   + ''', ''' +
    LTRIM(RTRIM(ISNULL(u_version,'')))  + ''', ''' +
    REPLACE(LTRIM(RTRIM(ISNULL(xprograma,''))), '''', '''''') + ''', ''' +
    REPLACE(LTRIM(RTRIM(ISNULL(xruta,''))),     '''', '''''') + ''', ''' +
    ISNULL(istatus,'V') + ''', GETDATE());' AS insert_sql
FROM seprogramas
WHERE LOWER(CAST(xprograma AS VARCHAR(500))) LIKE '%tarjeta%'
   OR LOWER(CAST(xruta     AS VARCHAR(500))) LIKE '%tarjeta%'
   OR LOWER(CAST(xruta     AS VARCHAR(500))) LIKE '%card%'
   OR cprograma IN (
       SELECT DISTINCT cprograma FROM sesubprog
       WHERE LOWER(CAST(xsubprograma AS VARCHAR(500))) LIKE '%tarjeta%'
          OR LOWER(CAST(xruta        AS VARCHAR(500))) LIKE '%tarjeta%'
          OR LOWER(CAST(xruta        AS VARCHAR(500))) LIKE '%card%'
   );
GO

-- =====================================================
-- PASO 2: INSERTs para sesubprog
-- =====================================================
SELECT
    'IF NOT EXISTS (SELECT 1 FROM sesubprog WHERE cprograma = ''' + LTRIM(RTRIM(cprograma)) +
    ''' AND csubprograma = ''' + LTRIM(RTRIM(csubprograma)) + ''') ' +
    'INSERT INTO sesubprog (cprograma, csubprograma, u_version, xsubprograma, xruta) VALUES (''' +
    LTRIM(RTRIM(cprograma))      + ''', ''' +
    LTRIM(RTRIM(csubprograma))   + ''', ''' +
    LTRIM(RTRIM(ISNULL(u_version,'')))    + ''', ''' +
    REPLACE(LTRIM(RTRIM(ISNULL(xsubprograma,''))), '''', '''''') + ''', ''' +
    REPLACE(LTRIM(RTRIM(ISNULL(xruta,''))),         '''', '''''') + ''');' AS insert_sql
FROM sesubprog
WHERE LOWER(CAST(xsubprograma AS VARCHAR(500))) LIKE '%tarjeta%'
   OR LOWER(CAST(xruta        AS VARCHAR(500))) LIKE '%tarjeta%'
   OR LOWER(CAST(xruta        AS VARCHAR(500))) LIKE '%card%'
   OR cprograma IN (
       SELECT cprograma FROM seprogramas
       WHERE LOWER(CAST(xprograma AS VARCHAR(500))) LIKE '%tarjeta%'
          OR LOWER(CAST(xruta     AS VARCHAR(500))) LIKE '%tarjeta%'
          OR LOWER(CAST(xruta     AS VARCHAR(500))) LIKE '%card%'
   );
GO

-- =====================================================
-- PASO 3: INSERTs para secatprog (permisos por categoría)
-- =====================================================
SELECT
    'IF NOT EXISTS (SELECT 1 FROM secatprog WHERE ccategoria = ' + CAST(ccategoria AS VARCHAR) +
    ' AND cprograma = ''' + LTRIM(RTRIM(cprograma)) + ''') ' +
    'INSERT INTO secatprog (ccategoria, cprograma, u_version, istatus, fingreso) VALUES (' +
    CAST(ccategoria AS VARCHAR) + ', ''' +
    LTRIM(RTRIM(cprograma)) + ''', ''' +
    LTRIM(RTRIM(ISNULL(u_version,''))) + ''', ''' +
    ISNULL(istatus,'V') + ''', GETDATE());' AS insert_sql
FROM secatprog
WHERE cprograma IN (
    SELECT cprograma FROM seprogramas
    WHERE LOWER(CAST(xprograma AS VARCHAR(500))) LIKE '%tarjeta%'
       OR LOWER(CAST(xruta     AS VARCHAR(500))) LIKE '%tarjeta%'
       OR LOWER(CAST(xruta     AS VARCHAR(500))) LIKE '%card%'
    UNION
    SELECT DISTINCT cprograma FROM sesubprog
    WHERE LOWER(CAST(xsubprograma AS VARCHAR(500))) LIKE '%tarjeta%'
       OR LOWER(CAST(xruta        AS VARCHAR(500))) LIKE '%tarjeta%'
       OR LOWER(CAST(xruta        AS VARCHAR(500))) LIKE '%card%'
);
GO

PRINT 'Copia los valores de la columna insert_sql de los 3 resultados y ejecútalos en la BD nueva.';
GO
