-- =============================================================
-- CEREBRORESTAURANT
-- MIGRACION v5 - MOTOR GLOBAL DNI / RUC / CE
-- Fuente funcional: CerebroHotel archive (27) + hotel (29)
--
-- EJECUTAR SOLO UNA VEZ DESPUES DE LA v4.
-- No ejecuta ni repite migraciones anteriores.
--
-- Incluye:
-- - Proveedores configurables y cifrados.
-- - APIsPeru prioridad 100 + JSON.pe prioridad 90.
-- - Fallback multiproveedor.
-- - Cache global reutilizable y cache negativo.
-- - Limites mensuales y estadisticas.
-- - Registros manuales privados por cuenta/restaurante.
-- - Logs sin guardar el numero completo en claro.
--
-- Las credenciales importadas desde CerebroHotel permanecen CIFRADAS.
-- =============================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS=0;

CREATE TABLE IF NOT EXISTS `identity_lookup_providers` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `country_code` char(2) NOT NULL DEFAULT 'PE',
  `code` varchar(60) NOT NULL,
  `name` varchar(140) NOT NULL,
  `base_url` varchar(255) NOT NULL,
  `dni_endpoint_template` varchar(255) DEFAULT NULL,
  `ruc_endpoint_template` varchar(255) DEFAULT NULL,
  `auth_type` varchar(30) NOT NULL DEFAULT 'query_param' COMMENT 'query_param, bearer, header',
  `auth_param_name` varchar(80) DEFAULT 'token',
  `credentials_enc` text DEFAULT NULL COMMENT 'Credencial cifrada por CerebroRestaurant; nunca texto plano',
  `timeout_seconds` smallint UNSIGNED NOT NULL DEFAULT 10,
  `priority` smallint UNSIGNED NOT NULL DEFAULT 100,
  `enabled` tinyint(1) NOT NULL DEFAULT 0,
  `settings_json` json DEFAULT NULL,
  `last_tested_at` datetime DEFAULT NULL,
  `last_test_status` varchar(30) DEFAULT NULL,
  `last_test_message` varchar(500) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_identity_provider_country_code` (`country_code`,`code`),
  KEY `idx_identity_provider_active` (`country_code`,`enabled`,`priority`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS `identity_lookup_cache` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `country_code` char(2) NOT NULL DEFAULT 'PE',
  `document_type` varchar(20) NOT NULL,
  `document_number_enc` text DEFAULT NULL,
  `document_number_hash` char(64) NOT NULL,
  `document_last4` varchar(8) DEFAULT NULL,
  `full_name` varchar(255) DEFAULT NULL,
  `first_names` varchar(180) DEFAULT NULL,
  `paternal_surname` varchar(120) DEFAULT NULL,
  `maternal_surname` varchar(120) DEFAULT NULL,
  `legal_name` varchar(255) DEFAULT NULL,
  `trade_name` varchar(255) DEFAULT NULL,
  `status_text` varchar(100) DEFAULT NULL,
  `condition_text` varchar(100) DEFAULT NULL,
  `address` varchar(500) DEFAULT NULL,
  `department` varchar(120) DEFAULT NULL,
  `province` varchar(120) DEFAULT NULL,
  `district` varchar(120) DEFAULT NULL,
  `ubigeo` varchar(20) DEFAULT NULL,
  `provider_id` bigint UNSIGNED DEFAULT NULL,
  `raw_response_json` json DEFAULT NULL,
  `fetched_at` datetime NOT NULL,
  `refresh_after` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_identity_cache_document` (`country_code`,`document_type`,`document_number_hash`),
  KEY `idx_identity_cache_refresh` (`country_code`,`document_type`,`refresh_after`),
  KEY `idx_identity_cache_provider` (`provider_id`),
  CONSTRAINT `fk_identity_cache_provider` FOREIGN KEY (`provider_id`) REFERENCES `identity_lookup_providers` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS `identity_lookup_usage_daily` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `provider_id` bigint UNSIGNED NOT NULL,
  `usage_date` date NOT NULL,
  `dni_api_calls` int UNSIGNED NOT NULL DEFAULT 0,
  `ruc_api_calls` int UNSIGNED NOT NULL DEFAULT 0,
  `ce_api_calls` int UNSIGNED NOT NULL DEFAULT 0,
  `cache_hits` int UNSIGNED NOT NULL DEFAULT 0,
  `local_hits` int UNSIGNED NOT NULL DEFAULT 0,
  `errors` int UNSIGNED NOT NULL DEFAULT 0,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_identity_usage_provider_date` (`provider_id`,`usage_date`),
  CONSTRAINT `fk_identity_usage_provider` FOREIGN KEY (`provider_id`) REFERENCES `identity_lookup_providers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS `identity_lookup_negative_cache` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `country_code` char(2) NOT NULL,
  `document_type` varchar(32) NOT NULL,
  `document_number_hash` char(64) NOT NULL,
  `last_message` varchar(500) DEFAULT NULL,
  `last_checked_at` datetime NOT NULL,
  `retry_after` datetime NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_identity_negative` (`country_code`,`document_type`,`document_number_hash`),
  KEY `idx_identity_negative_retry` (`retry_after`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS `identity_manual_records` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `account_id` bigint UNSIGNED NOT NULL,
  `restaurant_id` bigint UNSIGNED NOT NULL,
  `user_id` bigint UNSIGNED DEFAULT NULL,
  `country_code` char(2) NOT NULL DEFAULT 'PE',
  `document_type` varchar(32) NOT NULL,
  `document_number_enc` text NOT NULL,
  `document_number_hash` char(64) NOT NULL,
  `document_last4` varchar(8) DEFAULT NULL,
  `display_name` varchar(220) NOT NULL,
  `verification_status` varchar(40) NOT NULL DEFAULT 'manual_unverified',
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_identity_manual_restaurant` (`account_id`,`restaurant_id`,`country_code`,`document_type`,`document_number_hash`),
  KEY `idx_identity_manual_hash` (`country_code`,`document_type`,`document_number_hash`),
  KEY `idx_identity_manual_restaurant` (`restaurant_id`),
  CONSTRAINT `fk_identity_manual_account` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_identity_manual_restaurant` FOREIGN KEY (`restaurant_id`) REFERENCES `restaurants` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_identity_manual_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS `identity_lookup_logs` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `account_id` bigint UNSIGNED DEFAULT NULL,
  `restaurant_id` bigint UNSIGNED DEFAULT NULL,
  `user_id` bigint UNSIGNED DEFAULT NULL,
  `platform_user_id` bigint UNSIGNED DEFAULT NULL,
  `provider_id` bigint UNSIGNED DEFAULT NULL,
  `cache_id` bigint UNSIGNED DEFAULT NULL,
  `country_code` char(2) NOT NULL DEFAULT 'PE',
  `document_type` varchar(20) NOT NULL,
  `document_number_hash` char(64) NOT NULL,
  `document_last4` varchar(8) DEFAULT NULL,
  `context` varchar(60) NOT NULL COMMENT 'customer, supplier, pos_customer, fiscal, account_onboarding, platform_test, platform_chain_test, other',
  `origin` varchar(20) NOT NULL COMMENT 'local, api, manual',
  `status` varchar(30) NOT NULL COMMENT 'success, not_found, error, skipped',
  `http_status` smallint UNSIGNED DEFAULT NULL,
  `duration_ms` int UNSIGNED DEFAULT NULL,
  `message` varchar(500) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_identity_logs_account_date` (`account_id`,`created_at`),
  KEY `idx_identity_logs_restaurant_date` (`restaurant_id`,`created_at`),
  KEY `idx_identity_logs_provider_date` (`provider_id`,`created_at`),
  KEY `idx_identity_logs_document` (`country_code`,`document_type`,`document_number_hash`,`created_at`),
  KEY `idx_identity_logs_platform` (`platform_user_id`,`created_at`),
  CONSTRAINT `fk_identity_logs_account` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_identity_logs_restaurant` FOREIGN KEY (`restaurant_id`) REFERENCES `restaurants` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_identity_logs_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_identity_logs_platform` FOREIGN KEY (`platform_user_id`) REFERENCES `platform_users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_identity_logs_provider` FOREIGN KEY (`provider_id`) REFERENCES `identity_lookup_providers` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_identity_logs_cache` FOREIGN KEY (`cache_id`) REFERENCES `identity_lookup_cache` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Proveedor principal: misma configuracion operativa usada en CerebroHotel.
INSERT INTO `identity_lookup_providers`
(`country_code`,`code`,`name`,`base_url`,`dni_endpoint_template`,`ruc_endpoint_template`,`auth_type`,`auth_param_name`,`credentials_enc`,`timeout_seconds`,`priority`,`enabled`,`settings_json`)
VALUES
('PE','apisperu','APIsPeru','https://dniruc.apisperu.com/api/v1','/dni/{document}','/ruc/{document}','query_param','token','gcm1:resjROBIUdzQl2J49WUrg8Db1e7V3RZqmb/lwFEskwi1JYHS93kMitrvfUssmPcPlXQYFZIaMd3bRsjMWHz6qMgB2+TtVEym3F6LbCl8ZHuWhudWKaRKL3BZLgbRyRxEsVIAn7bBH/W7Yac75oAljitLOLctJOKVhdmn2P/ZN8sV0lEIRoEWAiz86zyZQ7OLmP0bQtqfL5P9',10,100,1,
 JSON_OBJECT('body_keys',JSON_OBJECT('ce','ce','dni','dni','ruc','ruc'),'cache_ce_days',180,'monthly_limit',2000,'cache_dni_days',30,'cache_ruc_days',7,'request_method','GET','response_adapter','apisperu_v1','ce_endpoint_template',''))
ON DUPLICATE KEY UPDATE
  `credentials_enc`=IF(`identity_lookup_providers`.`credentials_enc` IS NULL OR `identity_lookup_providers`.`credentials_enc`='',VALUES(`credentials_enc`),`identity_lookup_providers`.`credentials_enc`);

-- Respaldo: misma configuracion operativa usada en CerebroHotel.
INSERT INTO `identity_lookup_providers`
(`country_code`,`code`,`name`,`base_url`,`dni_endpoint_template`,`ruc_endpoint_template`,`auth_type`,`auth_param_name`,`credentials_enc`,`timeout_seconds`,`priority`,`enabled`,`settings_json`)
VALUES
('PE','jsonpe','JSON.pe','https://api.json.pe/api','/dni','/ruc','bearer','Authorization','gcm1:3J7ygHRU9TbNVXxRcUOaOssNXqJmnxPdtbFR/F/6IeCL5KgxYOe6+3h/HeqLmLNO1Zieb/BkT5BnrrgNdx4htAAYotSdoPxgwTwCh2iYUcp4ipl5XqQh9g==',12,90,1,
 JSON_OBJECT('body_keys',JSON_OBJECT('ce','ce','dni','dni','ruc','ruc'),'cache_ce_days',180,'monthly_limit',100,'cache_dni_days',180,'cache_ruc_days',30,'request_method','POST_JSON','response_adapter','jsonpe','ce_endpoint_template','/ce'))
ON DUPLICATE KEY UPDATE
  `credentials_enc`=IF(`identity_lookup_providers`.`credentials_enc` IS NULL OR `identity_lookup_providers`.`credentials_enc`='',VALUES(`credentials_enc`),`identity_lookup_providers`.`credentials_enc`);

SET FOREIGN_KEY_CHECKS=1;

-- Verificacion final. Deben aparecer 2 proveedores activos, prioridad 100 y 90.
SELECT id,code,name,priority,enabled,
       CASE WHEN credentials_enc IS NULL OR credentials_enc='' THEN 'SIN CREDENCIAL' ELSE 'CREDENCIAL CIFRADA' END AS credential_status
FROM identity_lookup_providers
ORDER BY priority DESC,id ASC;