-- ============================================================================
-- Script de Migración COMPLETO para Producción
-- Base de datos: chariot_demo (producción)
-- Fecha: 15 de noviembre de 2025
-- Versión: 1.0 - Checkpoint Estable
-- ============================================================================
-- 
-- ESTE SCRIPT ES SEGURO PARA PRODUCCIÓN:
-- - Usa IF NOT EXISTS para todas las operaciones
-- - No elimina ni modifica datos existentes
-- - Solo agrega estructuras faltantes
-- - Valida cada paso antes de ejecutar
-- 
-- ============================================================================

BEGIN;

-- ============================================================================
-- PARTE 1: TABLAS DE PASSPORT (OAuth para API móvil)
-- ============================================================================

-- Tabla: oauth_auth_codes
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'oauth_auth_codes') THEN
        CREATE TABLE oauth_auth_codes (
            id VARCHAR(100) PRIMARY KEY,
            user_id BIGINT NOT NULL,
            client_id BIGINT NOT NULL,
            scopes TEXT,
            revoked BOOLEAN NOT NULL,
            expires_at TIMESTAMP
        );
        CREATE INDEX oauth_auth_codes_user_id_index ON oauth_auth_codes(user_id);
        RAISE NOTICE '✅ Tabla oauth_auth_codes creada';
    ELSE
        RAISE NOTICE '⏭️  Tabla oauth_auth_codes ya existe';
    END IF;
END $$;

-- Tabla: oauth_access_tokens
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'oauth_access_tokens') THEN
        CREATE TABLE oauth_access_tokens (
            id VARCHAR(100) PRIMARY KEY,
            user_id BIGINT,
            client_id BIGINT NOT NULL,
            name VARCHAR(255),
            scopes TEXT,
            revoked BOOLEAN NOT NULL,
            created_at TIMESTAMP,
            updated_at TIMESTAMP,
            expires_at TIMESTAMP
        );
        CREATE INDEX oauth_access_tokens_user_id_index ON oauth_access_tokens(user_id);
        RAISE NOTICE '✅ Tabla oauth_access_tokens creada';
    ELSE
        RAISE NOTICE '⏭️  Tabla oauth_access_tokens ya existe';
    END IF;
END $$;

-- Tabla: oauth_refresh_tokens
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'oauth_refresh_tokens') THEN
        CREATE TABLE oauth_refresh_tokens (
            id VARCHAR(100) PRIMARY KEY,
            access_token_id VARCHAR(100) NOT NULL,
            revoked BOOLEAN NOT NULL,
            expires_at TIMESTAMP
        );
        CREATE INDEX oauth_refresh_tokens_access_token_id_index ON oauth_refresh_tokens(access_token_id);
        RAISE NOTICE '✅ Tabla oauth_refresh_tokens creada';
    ELSE
        RAISE NOTICE '⏭️  Tabla oauth_refresh_tokens ya existe';
    END IF;
END $$;

-- Tabla: oauth_clients
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'oauth_clients') THEN
        CREATE TABLE oauth_clients (
            id BIGSERIAL PRIMARY KEY,
            user_id BIGINT,
            name VARCHAR(255) NOT NULL,
            secret VARCHAR(100),
            provider VARCHAR(255),
            redirect TEXT NOT NULL,
            personal_access_client BOOLEAN NOT NULL,
            password_client BOOLEAN NOT NULL,
            revoked BOOLEAN NOT NULL,
            created_at TIMESTAMP,
            updated_at TIMESTAMP
        );
        CREATE INDEX oauth_clients_user_id_index ON oauth_clients(user_id);
        RAISE NOTICE '✅ Tabla oauth_clients creada';
    ELSE
        RAISE NOTICE '⏭️  Tabla oauth_clients ya existe';
    END IF;
END $$;

-- Tabla: oauth_personal_access_clients
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'oauth_personal_access_clients') THEN
        CREATE TABLE oauth_personal_access_clients (
            id BIGSERIAL PRIMARY KEY,
            client_id BIGINT NOT NULL,
            created_at TIMESTAMP,
            updated_at TIMESTAMP
        );
        RAISE NOTICE '✅ Tabla oauth_personal_access_clients creada';
    ELSE
        RAISE NOTICE '⏭️  Tabla oauth_personal_access_clients ya existe';
    END IF;
END $$;

-- ============================================================================
-- PARTE 2: TABLA ORIGIN_PLATFORMS (Integración con sistemas externos)
-- ============================================================================

DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'origin_platforms') THEN
        CREATE TABLE origin_platforms (
            id SERIAL PRIMARY KEY,
            name VARCHAR(255) NOT NULL,
            base_url VARCHAR(255) NOT NULL,
            create_trip_path VARCHAR(255),
            auth_config JSON,
            is_active BOOLEAN DEFAULT true NOT NULL,
            created_at TIMESTAMP,
            updated_at TIMESTAMP,
            deleted_at TIMESTAMP
        );
        RAISE NOTICE '✅ Tabla origin_platforms creada';
    ELSE
        RAISE NOTICE '⏭️  Tabla origin_platforms ya existe';
    END IF;
END $$;

-- Insertar datos por defecto en origin_platforms
DO $$ 
DECLARE
    max_id INTEGER;
BEGIN
    IF NOT EXISTS (SELECT 1 FROM origin_platforms WHERE id = 1) THEN
        INSERT INTO origin_platforms (id, name, base_url, create_trip_path, is_active, created_at, updated_at)
        VALUES 
            (1, 'interno', 'http://127.0.0.1:8000', '/api/web/services', true, NOW(), NOW()),
            (2, 'e-ride', 'http://127.0.0.1:8080', '/api/trip/update-assignment', true, NOW(), NOW());
        
        -- Actualizar secuencia si existe
        IF EXISTS (SELECT 1 FROM pg_class WHERE relname = 'origin_platforms_id_seq') THEN
            SELECT MAX(id) INTO max_id FROM origin_platforms;
            PERFORM setval('origin_platforms_id_seq', max_id);
            RAISE NOTICE '✅ Secuencia origin_platforms_id_seq actualizada a %', max_id;
        END IF;
        
        RAISE NOTICE '✅ Datos iniciales insertados en origin_platforms';
    ELSE
        RAISE NOTICE '⏭️  origin_platforms ya tiene datos';
    END IF;
END $$;

-- ============================================================================
-- PARTE 3: COLUMNAS ADICIONALES EN SERVICES
-- ============================================================================

-- Agregar columna id_origin
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name = 'services' AND column_name = 'id_origin') THEN
        ALTER TABLE services ADD COLUMN id_origin INTEGER DEFAULT 1 NOT NULL;
        RAISE NOTICE '✅ Columna id_origin agregada a services';
    ELSE
        RAISE NOTICE '⏭️  Columna id_origin ya existe en services';
    END IF;
END $$;

-- Agregar columna external_trip_id
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name = 'services' AND column_name = 'external_trip_id') THEN
        ALTER TABLE services ADD COLUMN external_trip_id INTEGER;
        RAISE NOTICE '✅ Columna external_trip_id agregada a services';
    ELSE
        RAISE NOTICE '⏭️  Columna external_trip_id ya existe en services';
    END IF;
END $$;

-- Agregar foreign key de id_origin (SOLO si no existe)
DO $$ 
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.table_constraints 
        WHERE constraint_name = 'services_id_origin_foreign'
    ) THEN
        -- Antes de agregar FK, asegurar que todos los id_origin existen
        UPDATE services SET id_origin = 1 WHERE id_origin = 0 OR id_origin IS NULL;
        
        ALTER TABLE services 
            ADD CONSTRAINT services_id_origin_foreign 
            FOREIGN KEY (id_origin) REFERENCES origin_platforms(id);
        
        RAISE NOTICE '✅ Foreign key services_id_origin_foreign creada';
    ELSE
        RAISE NOTICE '⏭️  Foreign key services_id_origin_foreign ya existe';
    END IF;
END $$;

-- ============================================================================
-- PARTE 4: TABLAS DE BOUNCER (Sistema de Permisos)
-- ============================================================================

-- Tabla: abilities
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'abilities') THEN
        CREATE TABLE abilities (
            id BIGSERIAL PRIMARY KEY,
            name VARCHAR(255) NOT NULL,
            title VARCHAR(255),
            entity_id BIGINT,
            entity_type VARCHAR(255),
            only_owned BOOLEAN DEFAULT false NOT NULL,
            options JSON,
            scope INTEGER,
            created_at TIMESTAMP,
            updated_at TIMESTAMP
        );
        CREATE INDEX abilities_scope_index ON abilities(scope);
        RAISE NOTICE '✅ Tabla abilities creada';
    ELSE
        RAISE NOTICE '⏭️  Tabla abilities ya existe';
    END IF;
END $$;

-- Actualizar tabla roles con columnas de Bouncer
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name = 'roles' AND column_name = 'title') THEN
        ALTER TABLE roles ADD COLUMN title VARCHAR(255);
        RAISE NOTICE '✅ Columna title agregada a roles';
    ELSE
        RAISE NOTICE '⏭️  Columna title ya existe en roles';
    END IF;
    
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name = 'roles' AND column_name = 'scope') THEN
        ALTER TABLE roles ADD COLUMN scope INTEGER;
        CREATE INDEX roles_scope_index ON roles(scope);
        RAISE NOTICE '✅ Columna scope agregada a roles';
    ELSE
        RAISE NOTICE '⏭️  Columna scope ya existe en roles';
    END IF;
END $$;

-- Tabla: assigned_roles
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'assigned_roles') THEN
        CREATE TABLE assigned_roles (
            id BIGSERIAL PRIMARY KEY,
            role_id BIGINT NOT NULL,
            entity_id BIGINT NOT NULL,
            entity_type VARCHAR(255) NOT NULL,
            restricted_to_id BIGINT,
            restricted_to_type VARCHAR(255),
            scope INTEGER
        );
        CREATE INDEX assigned_roles_role_id_index ON assigned_roles(role_id);
        CREATE INDEX assigned_roles_scope_index ON assigned_roles(scope);
        CREATE INDEX assigned_roles_entity_index ON assigned_roles(entity_id, entity_type, scope);
        ALTER TABLE assigned_roles 
            ADD CONSTRAINT assigned_roles_role_id_foreign 
            FOREIGN KEY (role_id) REFERENCES roles(id) 
            ON UPDATE CASCADE ON DELETE CASCADE;
        RAISE NOTICE '✅ Tabla assigned_roles creada';
    ELSE
        RAISE NOTICE '⏭️  Tabla assigned_roles ya existe';
    END IF;
END $$;

-- Tabla: permissions
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'permissions') THEN
        CREATE TABLE permissions (
            id BIGSERIAL PRIMARY KEY,
            ability_id BIGINT NOT NULL,
            entity_id BIGINT,
            entity_type VARCHAR(255),
            forbidden BOOLEAN DEFAULT false NOT NULL,
            scope INTEGER
        );
        CREATE INDEX permissions_ability_id_index ON permissions(ability_id);
        CREATE INDEX permissions_scope_index ON permissions(scope);
        CREATE INDEX permissions_entity_index ON permissions(entity_id, entity_type, scope);
        ALTER TABLE permissions 
            ADD CONSTRAINT permissions_ability_id_foreign 
            FOREIGN KEY (ability_id) REFERENCES abilities(id) 
            ON UPDATE CASCADE ON DELETE CASCADE;
        RAISE NOTICE '✅ Tabla permissions creada';
    ELSE
        RAISE NOTICE '⏭️  Tabla permissions ya existe';
    END IF;
END $$;

-- ============================================================================
-- PARTE 5: COLUMNAS LEVEL EN USERS Y ROLES (Jerarquía)
-- ============================================================================

-- Agregar level a users
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name = 'users' AND column_name = 'level') THEN
        ALTER TABLE users ADD COLUMN level INTEGER DEFAULT 1;
        RAISE NOTICE '✅ Columna level agregada a users';
    ELSE
        RAISE NOTICE '⏭️  Columna level ya existe en users';
    END IF;
END $$;

-- Agregar level a roles
DO $$ 
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name = 'roles' AND column_name = 'level') THEN
        ALTER TABLE roles ADD COLUMN level INTEGER DEFAULT 1;
        RAISE NOTICE '✅ Columna level agregada a roles';
    ELSE
        RAISE NOTICE '⏭️  Columna level ya existe en roles';
    END IF;
END $$;

-- ============================================================================
-- PARTE 6: ÍNDICES DE OPTIMIZACIÓN PARA ANALYTICS
-- ============================================================================

DO $$ 
BEGIN
    CREATE INDEX IF NOT EXISTS idx_services_id_driver ON services(id_driver);
    CREATE INDEX IF NOT EXISTS idx_drivers_flag_active_canceled ON drivers(flag_active, canceled);
    CREATE INDEX IF NOT EXISTS idx_vehicles_id_type ON vehicles(id_type);
    CREATE INDEX IF NOT EXISTS idx_times_time_request ON times(time_request);
    CREATE INDEX IF NOT EXISTS idx_services_id_status ON services(id_status);
    CREATE INDEX IF NOT EXISTS idx_services_driver_status ON services(id_driver, id_status);
    
    RAISE NOTICE '✅ Índices de optimización creados';
END $$;

-- ============================================================================
-- PARTE 7: REGISTRO EN MIGRATIONS
-- ============================================================================

-- Registrar migraciones en la tabla migrations
DO $$ 
DECLARE
    current_batch INTEGER;
    migration_name TEXT;
    migration_names TEXT[] := ARRAY[
        '2016_06_01_000001_create_oauth_auth_codes_table',
        '2016_06_01_000002_create_oauth_access_tokens_table',
        '2016_06_01_000003_create_oauth_refresh_tokens_table',
        '2016_06_01_000004_create_oauth_clients_table',
        '2016_06_01_000005_create_oauth_personal_access_clients_table',
        '2020_01_24_180000_create_origin_platforms_table',
        '2025_03_05_132325_create_bouncer_tables',
        '2025_03_10_131158_add_level_to_users_and_roles',
        '2025_10_20_215631_add_origin_and_external_trip_id_to_services'
    ];
BEGIN
    -- Obtener el batch actual
    SELECT COALESCE(MAX(batch), 0) + 1 INTO current_batch FROM migrations;
    
    -- Registrar cada migración si no existe
    FOREACH migration_name IN ARRAY migration_names
    LOOP
        IF NOT EXISTS (SELECT 1 FROM migrations WHERE migration = migration_name) THEN
            INSERT INTO migrations (migration, batch) VALUES (migration_name, current_batch);
            RAISE NOTICE '✅ Registrada migración: %', migration_name;
        ELSE
            RAISE NOTICE '⏭️  Migración ya existe: %', migration_name;
        END IF;
    END LOOP;
    
    RAISE NOTICE '✅ Proceso de registro de migraciones completado';
END $$;

-- ============================================================================
-- PARTE 8: VALIDACIÓN FINAL
-- ============================================================================

DO $$ 
DECLARE
    missing_tables TEXT := '';
    missing_columns TEXT := '';
BEGIN
    -- Validar tablas OAuth
    IF NOT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'oauth_clients') THEN
        missing_tables := missing_tables || 'oauth_clients, ';
    END IF;
    
    -- Validar tablas Bouncer
    IF NOT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'abilities') THEN
        missing_tables := missing_tables || 'abilities, ';
    END IF;
    
    IF NOT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'assigned_roles') THEN
        missing_tables := missing_tables || 'assigned_roles, ';
    END IF;
    
    -- Validar tabla origin_platforms
    IF NOT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'origin_platforms') THEN
        missing_tables := missing_tables || 'origin_platforms, ';
    END IF;
    
    -- Validar columnas críticas
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name = 'services' AND column_name = 'id_origin') THEN
        missing_columns := missing_columns || 'services.id_origin, ';
    END IF;
    
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name = 'drivers' AND column_name = 'canceled') THEN
        missing_columns := missing_columns || 'drivers.canceled, ';
    END IF;
    
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                   WHERE table_name = 'vehicles' AND column_name = 'id_type') THEN
        missing_columns := missing_columns || 'vehicles.id_type, ';
    END IF;
    
    -- Reportar resultado
    IF missing_tables != '' THEN
        RAISE EXCEPTION '❌ TABLAS FALTANTES: %', missing_tables;
    END IF;
    
    IF missing_columns != '' THEN
        RAISE EXCEPTION '❌ COLUMNAS FALTANTES: %', missing_columns;
    END IF;
    
    RAISE NOTICE '';
    RAISE NOTICE '============================================================';
    RAISE NOTICE '✅ MIGRACIÓN COMPLETADA EXITOSAMENTE';
    RAISE NOTICE '============================================================';
    RAISE NOTICE '';
    RAISE NOTICE '📋 CHECKPOINT ESTABLE - Versión 1.0';
    RAISE NOTICE '';
    RAISE NOTICE '✅ Tablas creadas:';
    RAISE NOTICE '   - oauth_* (5 tablas de Passport)';
    RAISE NOTICE '   - origin_platforms (integraciones)';
    RAISE NOTICE '   - abilities, assigned_roles, permissions (Bouncer)';
    RAISE NOTICE '';
    RAISE NOTICE '✅ Columnas agregadas:';
    RAISE NOTICE '   - services: id_origin, external_trip_id';
    RAISE NOTICE '   - users: level';
    RAISE NOTICE '   - roles: title, scope, level';
    RAISE NOTICE '';
    RAISE NOTICE '✅ Índices de optimización creados';
    RAISE NOTICE '';
    RAISE NOTICE '📋 PRÓXIMOS PASOS:';
    RAISE NOTICE '   1. php artisan passport:install';
    RAISE NOTICE '   2. php artisan db:seed --class=BouncerRolesSeeder';
    RAISE NOTICE '   3. php artisan cache:clear && php artisan config:cache';
    RAISE NOTICE '   4. npm run production';
    RAISE NOTICE '';
    RAISE NOTICE '============================================================';
END $$;

COMMIT;
