-- ICTWorld Payment Server: additive central website registry + credential migration
-- Back up the database first. Run once. No existing table is altered or dropped.
CREATE TABLE IF NOT EXISTS website_registry (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 client_id BIGINT UNSIGNED NOT NULL,
 external_website_id BIGINT UNSIGNED NULL,
 site_name VARCHAR(190) NOT NULL DEFAULT '',
 domain VARCHAR(253) NOT NULL,
 framework VARCHAR(30) NOT NULL,
 connector_type VARCHAR(30) NOT NULL,
 status ENUM('pending','active','revoked') NOT NULL DEFAULT 'pending',
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NULL DEFAULT NULL,
 UNIQUE KEY uniq_website_domain (domain),
 UNIQUE KEY uniq_external_website (client_id, external_website_id),
 INDEX ix_registry_client (client_id),
 CONSTRAINT fk_wr_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS website_credentials (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 client_id BIGINT UNSIGNED NOT NULL,
 website_id BIGINT UNSIGNED NOT NULL,
 domain VARCHAR(253) NOT NULL,
 framework VARCHAR(30) NOT NULL,
 connector_type VARCHAR(30) NOT NULL,
 key_hash CHAR(64) NOT NULL UNIQUE,
 status ENUM('active','revoked') NOT NULL DEFAULT 'active',
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 revoked_at DATETIME NULL DEFAULT NULL,
 last_used_at DATETIME NULL DEFAULT NULL,
 INDEX ix_wc_client (client_id),
 INDEX ix_wc_website (website_id),
 CONSTRAINT fk_wc_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
 CONSTRAINT fk_wc_website FOREIGN KEY (website_id) REFERENCES website_registry(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;