-- Mark the existing development tenant as staging and add its staging hostname.
-- Run on webdemoo_simple_saas_platform only after DNS and AutoSSL are verified.
-- Safe to run again.

START TRANSACTION;

SET @staging_tenant_id := (
  SELECT `tenant_id`
  FROM `platform_domain_tb`
  WHERE `hostname` IN ('staging.simple.edu.vn', 'se.webdemo258.io.vn')
  ORDER BY (`hostname` = 'staging.simple.edu.vn') DESC
  LIMIT 1
);

SET @existing_staging_domain_tenant_id := (
  SELECT `tenant_id`
  FROM `platform_domain_tb`
  WHERE `hostname` = 'staging.simple.edu.vn'
  LIMIT 1
);

SET @staging_guard_sql := IF(
  @staging_tenant_id IS NULL,
  'SIGNAL SQLSTATE ''45000'' SET MESSAGE_TEXT = ''Staging tenant domain was not found''',
  IF(
    @existing_staging_domain_tenant_id IS NOT NULL
      AND @existing_staging_domain_tenant_id <> @staging_tenant_id,
    'SIGNAL SQLSTATE ''45000'' SET MESSAGE_TEXT = ''Staging hostname already belongs to another tenant''',
    'SELECT 1'
  )
);
PREPARE staging_guard_statement FROM @staging_guard_sql;
EXECUTE staging_guard_statement;
DEALLOCATE PREPARE staging_guard_statement;

INSERT INTO `platform_domain_tb` (
  `tenant_id`,
  `hostname`,
  `domain_type`,
  `is_primary`,
  `dns_status`,
  `ssl_status`,
  `status`,
  `verified_at`
)
SELECT
  @staging_tenant_id,
  'staging.simple.edu.vn',
  'subdomain',
  1,
  'verified',
  'active',
  'active',
  NOW()
WHERE @staging_tenant_id IS NOT NULL
ON DUPLICATE KEY UPDATE
  `domain_type` = VALUES(`domain_type`),
  `is_primary` = VALUES(`is_primary`),
  `dns_status` = VALUES(`dns_status`),
  `ssl_status` = VALUES(`ssl_status`),
  `status` = VALUES(`status`),
  `verified_at` = COALESCE(`verified_at`, VALUES(`verified_at`));

UPDATE `platform_domain_tb`
SET `is_primary` = 0
WHERE `tenant_id` = @staging_tenant_id
  AND `hostname` <> 'staging.simple.edu.vn';

UPDATE `platform_tenant_tb`
SET `environment` = 'staging'
WHERE `id` = @staging_tenant_id;

INSERT INTO `platform_tenant_log_tb` (`tenant_id`, `action`, `actor_name`, `metadata_json`)
SELECT
  @staging_tenant_id,
  'staging_environment_configured',
  'manual_setup',
  '{"hostname":"staging.simple.edu.vn","previous_hostname":"se.webdemo258.io.vn"}'
WHERE @staging_tenant_id IS NOT NULL
  AND NOT EXISTS (
    SELECT 1
    FROM `platform_tenant_log_tb`
    WHERE `tenant_id` = @staging_tenant_id
      AND `action` = 'staging_environment_configured'
  );

COMMIT;

SELECT
  t.`id`,
  t.`name`,
  t.`slug`,
  t.`environment`,
  t.`database_name`,
  d.`hostname`,
  d.`is_primary`,
  d.`dns_status`,
  d.`ssl_status`,
  d.`status` AS `domain_status`
FROM `platform_tenant_tb` t
INNER JOIN `platform_domain_tb` d ON d.`tenant_id` = t.`id`
WHERE t.`id` = @staging_tenant_id
ORDER BY d.`is_primary` DESC, d.`hostname`;