SET NAMES utf8mb4;

ALTER TABLE shop_domains
    MODIFY COLUMN host VARCHAR(255) NULL,
    ADD COLUMN network_id BIGINT UNSIGNED NULL AFTER shop_id,
    ADD COLUMN domain VARCHAR(255) NULL AFTER host,
    ADD COLUMN normalized_domain VARCHAR(255) NULL AFTER domain,
    ADD COLUMN type ENUM('platform_subdomain', 'custom_domain') NOT NULL DEFAULT 'platform_subdomain' AFTER normalized_domain,
    ADD COLUMN redirect_to_primary TINYINT(1) NOT NULL DEFAULT 1 AFTER is_primary,
    ADD COLUMN verification_status ENUM('pending', 'verified', 'failed') NOT NULL DEFAULT 'pending' AFTER redirect_to_primary,
    ADD COLUMN ssl_status ENUM('not_required', 'pending', 'active', 'error') NOT NULL DEFAULT 'pending' AFTER verification_status,
    ADD COLUMN ssl_error TEXT NULL AFTER ssl_status,
    ADD COLUMN verification_token VARCHAR(128) NULL AFTER ssl_error,
    ADD COLUMN verified_at DATETIME NULL AFTER verification_token,
    ADD COLUMN updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER created_at;

UPDATE shop_domains sd
INNER JOIN shops s ON s.id = sd.shop_id
SET
    sd.network_id = s.network_id,
    sd.domain = LOWER(COALESCE(NULLIF(TRIM(sd.host), ''), CONCAT(s.slug, '.localhost'))),
    sd.normalized_domain = LOWER(COALESCE(NULLIF(TRIM(sd.host), ''), CONCAT(s.slug, '.localhost'))),
    sd.type = 'platform_subdomain',
    sd.redirect_to_primary = CASE WHEN sd.is_primary = 1 THEN 0 ELSE 1 END,
    sd.verification_status = CASE
        WHEN LOWER(COALESCE(sd.host, '')) LIKE '%.localhost' THEN 'verified'
        ELSE 'pending'
    END,
    sd.ssl_status = CASE
        WHEN LOWER(COALESCE(sd.host, '')) LIKE '%.localhost' THEN 'not_required'
        ELSE 'pending'
    END,
    sd.verification_token = CASE
        WHEN sd.verification_token IS NULL THEN SHA2(CONCAT('shop-domain-', sd.id, '-', sd.shop_id), 256)
        ELSE sd.verification_token
    END,
    sd.verified_at = CASE
        WHEN LOWER(COALESCE(sd.host, '')) LIKE '%.localhost' THEN CURRENT_TIMESTAMP
        ELSE sd.verified_at
    END;

ALTER TABLE shop_domains
    MODIFY COLUMN domain VARCHAR(255) NOT NULL,
    MODIFY COLUMN normalized_domain VARCHAR(255) NOT NULL,
    ADD COLUMN primary_shop_id BIGINT UNSIGNED GENERATED ALWAYS AS (CASE WHEN is_primary = 1 THEN shop_id ELSE NULL END) STORED,
    ADD CONSTRAINT fk_shop_domains_network FOREIGN KEY (network_id) REFERENCES networks(id),
    ADD UNIQUE KEY uq_shop_domains_domain (domain),
    ADD UNIQUE KEY uq_shop_domains_normalized_domain (normalized_domain),
    ADD UNIQUE KEY uq_shop_domains_primary_shop (primary_shop_id),
    ADD INDEX idx_shop_domains_network (network_id);
