-- ============================================================================
-- Align local `cnmgts_abha_consent_request` with PRODUCTION
-- ----------------------------------------------------------------------------
-- Strategy : DROP & RECREATE  (DESTRUCTIVE - existing rows are removed)
-- Target   : production columns/types + retained local strengths
--            (utf8mb4, bigint id, request_id/health_id/consent_id/artefact_id idx)
-- DB       : run against your LOCAL database (e.g. abdm_local)
--
-- This is the raw-SQL equivalent of:
--   database/migrations/2026_06_24_000000_align_cnmgts_abha_consent_request_with_production.php
-- Use EITHER `php artisan migrate` OR this script - not both.
-- ============================================================================

-- 1) SAFETY BACKUP -- run this FIRST -----------------------------------------
CREATE TABLE `cnmgts_abha_consent_request_backup_20260624`
    LIKE `cnmgts_abha_consent_request`;

INSERT INTO `cnmgts_abha_consent_request_backup_20260624`
    SELECT * FROM `cnmgts_abha_consent_request`;

-- 2) DROP & RECREATE ---------------------------------------------------------
DROP TABLE IF EXISTS `cnmgts_abha_consent_request`;

CREATE TABLE `cnmgts_abha_consent_request` (
  `id`             bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `request_id`     varchar(36)  NOT NULL,
  `name`           varchar(100) NOT NULL,
  `health_id`      varchar(32)  NOT NULL,
  `consent_id`     varchar(36)  DEFAULT NULL,
  `artefact_id`    varchar(40)  DEFAULT NULL,
  `transaction_id` varchar(40)  DEFAULT NULL,
  `request_from`   date         DEFAULT NULL,
  `request_to`     date         DEFAULT NULL,
  `grant_date`     datetime     DEFAULT NULL,
  `expiry_date`    date         DEFAULT NULL,
  `status`         varchar(20)  NOT NULL DEFAULT 'Request sent',
  `req_status`     tinyint(1)   NOT NULL DEFAULT '0' COMMENT '0 - Pending, 1 - HIU request sent',
  `request_payload` longtext,
  `key_material`    longtext,
  `created_at`     timestamp    NULL DEFAULT NULL,
  `updated_at`     timestamp    NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `cnmgts_abha_consent_request_request_id_index` (`request_id`),
  KEY `cnmgts_abha_consent_request_health_id_index`  (`health_id`),
  KEY `cnmgts_abha_consent_request_consent_id_index`  (`consent_id`),
  KEY `cnmgts_abha_consent_request_artefact_id_index` (`artefact_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3) OPTIONAL: restore the previous rows, mapping numeric status -> string ----
--    The old local `status` was tinyint (2). Rows with no grant become
--    'Request sent'; granted rows (grant_date present) become 'GRANTED'.
--    Uncomment to keep the old rows instead of starting empty.
--
-- INSERT INTO `cnmgts_abha_consent_request`
--   (`id`, `request_id`, `name`, `health_id`, `consent_id`,
--    `request_from`, `request_to`, `grant_date`, `expiry_date`,
--    `status`, `request_payload`, `key_material`, `created_at`, `updated_at`)
-- SELECT
--    `id`, `request_id`, `name`, `health_id`, `consent_id`,
--    `request_from`, `request_to`, `grant_date`, `expiry_date`,
--    CASE WHEN `grant_date` IS NULL THEN 'Request sent' ELSE 'GRANTED' END,
--    `request_payload`, `key_material`, `created_at`, `updated_at`
-- FROM `cnmgts_abha_consent_request_backup_20260624`;

-- 4) After verifying everything works, drop the backup -----------------------
-- DROP TABLE `cnmgts_abha_consent_request_backup_20260624`;