-- NGBVD existing database reconciliation script
-- Purpose: mark already-existing framework/custom tables as migrated and apply only safe audit-column widening.
-- Run once BEFORE php artisan migrate when working with the existing database dump.
-- This script is non-destructive and does not drop/recreate data tables.

-- 1) Mark dangerous/superseded UUID primary-key conversion as handled.
-- The current database already uses integer primary keys internally plus public UUID columns.
INSERT INTO `migrations` (`migration`, `batch`)
SELECT '2026_06_02_000001_convert_core_ngbvd_tables_to_uuid_primary_keys', COALESCE((SELECT MAX(m2.batch) FROM `migrations` m2),0)+1
WHERE NOT EXISTS (SELECT 1 FROM `migrations` WHERE `migration` = '2026_06_02_000001_convert_core_ngbvd_tables_to_uuid_primary_keys');

-- 2) Mark Passport OAuth migrations as handled when the tables already exist.
INSERT INTO `migrations` (`migration`, `batch`)
SELECT '2016_06_01_000001_create_oauth_auth_codes_table', COALESCE((SELECT MAX(m2.batch) FROM `migrations` m2),0)+1
WHERE NOT EXISTS (SELECT 1 FROM `migrations` WHERE `migration` = '2016_06_01_000001_create_oauth_auth_codes_table')
  AND EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'oauth_auth_codes');

INSERT INTO `migrations` (`migration`, `batch`)
SELECT '2016_06_01_000002_create_oauth_access_tokens_table', COALESCE((SELECT MAX(m2.batch) FROM `migrations` m2),0)+1
WHERE NOT EXISTS (SELECT 1 FROM `migrations` WHERE `migration` = '2016_06_01_000002_create_oauth_access_tokens_table')
  AND EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'oauth_access_tokens');

INSERT INTO `migrations` (`migration`, `batch`)
SELECT '2016_06_01_000003_create_oauth_refresh_tokens_table', COALESCE((SELECT MAX(m2.batch) FROM `migrations` m2),0)+1
WHERE NOT EXISTS (SELECT 1 FROM `migrations` WHERE `migration` = '2016_06_01_000003_create_oauth_refresh_tokens_table')
  AND EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'oauth_refresh_tokens');

INSERT INTO `migrations` (`migration`, `batch`)
SELECT '2016_06_01_000004_create_oauth_clients_table', COALESCE((SELECT MAX(m2.batch) FROM `migrations` m2),0)+1
WHERE NOT EXISTS (SELECT 1 FROM `migrations` WHERE `migration` = '2016_06_01_000004_create_oauth_clients_table')
  AND EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'oauth_clients');

INSERT INTO `migrations` (`migration`, `batch`)
SELECT '2016_06_01_000005_create_oauth_personal_access_clients_table', COALESCE((SELECT MAX(m2.batch) FROM `migrations` m2),0)+1
WHERE NOT EXISTS (SELECT 1 FROM `migrations` WHERE `migration` = '2016_06_01_000005_create_oauth_personal_access_clients_table')
  AND EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'oauth_personal_access_clients');

-- 3) Mark Sanctum token migration as handled when the table already exists.
INSERT INTO `migrations` (`migration`, `batch`)
SELECT '2019_12_14_000001_create_personal_access_tokens_table', COALESCE((SELECT MAX(m2.batch) FROM `migrations` m2),0)+1
WHERE NOT EXISTS (SELECT 1 FROM `migrations` WHERE `migration` = '2019_12_14_000001_create_personal_access_tokens_table')
  AND EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'personal_access_tokens');

-- 4) Mark app schema migrations as handled where their tables/columns already exist.
INSERT INTO `migrations` (`migration`, `batch`)
SELECT '2026_06_04_000100_create_api_clients_integration_logs_and_user_scopes', COALESCE((SELECT MAX(m2.batch) FROM `migrations` m2),0)+1
WHERE NOT EXISTS (SELECT 1 FROM `migrations` WHERE `migration` = '2026_06_04_000100_create_api_clients_integration_logs_and_user_scopes')
  AND EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'api_clients')
  AND EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'integration_logs')
  AND EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'user_data_scopes');

INSERT INTO `migrations` (`migration`, `batch`)
SELECT '2026_06_04_000110_add_public_uuid_columns_to_core_tables', COALESCE((SELECT MAX(m2.batch) FROM `migrations` m2),0)+1
WHERE NOT EXISTS (SELECT 1 FROM `migrations` WHERE `migration` = '2026_06_04_000110_add_public_uuid_columns_to_core_tables')
  AND EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'victim' AND column_name = 'uuid')
  AND EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'incident_data' AND column_name = 'uuid')
  AND EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'perpetrators' AND column_name = 'uuid')
  AND EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'witnesses' AND column_name = 'uuid')
  AND EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'actions' AND column_name = 'uuid')
  AND EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'incident_status' AND column_name = 'uuid');

INSERT INTO `migrations` (`migration`, `batch`)
SELECT '2026_06_04_000130_create_audit_logs_table', COALESCE((SELECT MAX(m2.batch) FROM `migrations` m2),0)+1
WHERE NOT EXISTS (SELECT 1 FROM `migrations` WHERE `migration` = '2026_06_04_000130_create_audit_logs_table')
  AND EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'audit_logs');

-- 5) The only structural correction required by the reviewed dump: widen audit fields that are too short.
ALTER TABLE `audit_trails` MODIFY `resource` VARCHAR(191) NULL;
ALTER TABLE `audit_trails` MODIFY `action` VARCHAR(255) NULL;
ALTER TABLE `audit_trails` MODIFY `response` TEXT NULL;
ALTER TABLE `audit_trails` MODIFY `browser` TEXT NULL;
ALTER TABLE `audit_trails` MODIFY `mac_address` VARCHAR(255) NULL;
ALTER TABLE `audit_trails` MODIFY `ip_address` VARCHAR(191) NULL;

ALTER TABLE `audit_logs` MODIFY `action` VARCHAR(150) NOT NULL;
ALTER TABLE `audit_logs` MODIFY `url` TEXT NULL;
ALTER TABLE `audit_logs` MODIFY `user_agent` TEXT NULL;
