-- Migration 002 — Web chat channel + Vale Admin (super-admin)
-- Applies on top of SCHEMA.sql (Sprint 1). Safe to run once.

-- Track A: web channel needs a non-secret embed key per tenant.
ALTER TABLE tenants
    ADD COLUMN web_public_key CHAR(32) NULL AFTER whatsapp_number,
    ADD UNIQUE KEY uq_tenants_web_public_key (web_public_key);

-- Track A: web visitors have no phone; identified by an opaque token.
-- (phone stays NULL for web contacts; MySQL allows multiple NULLs in the
--  existing UNIQUE(tenant_id, phone), so no collision.)
ALTER TABLE contacts
    ADD COLUMN web_visitor_id VARCHAR(64) NULL AFTER phone,
    ADD UNIQUE KEY uq_contacts_tenant_visitor (tenant_id, web_visitor_id);

-- Track B: super-admin lives in its own table (crosses tenants on purpose),
-- separate from users to never contaminate tenant scoping.
CREATE TABLE IF NOT EXISTS admins (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(160) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    name VARCHAR(120) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_admins_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Backfill a web_public_key for existing tenants (32 hex chars).
-- Portable across MySQL 8 and MariaDB (no RANDOM_BYTES dependency):
-- UUID() + RAND() make each row unique; SHA2 -> hex; LEFT(...,32) fits CHAR(32).
UPDATE tenants
SET web_public_key = LEFT(SHA2(CONCAT(id, UUID(), RAND()), 256), 32)
WHERE web_public_key IS NULL;