-- =====================================================================
--  UPDATE — Convergence Platform : MySQL schema
--  Engine: InnoDB / utf8mb4
--
--  Design notes:
--   * Multi-tenant from day one. Nothing here is CSCAfrica-specific.
--   * `channels` are PLATFORM-level shared lines (one WhatsApp number, one
--     Telegram bot, etc.) — NOT per-org or per-event. The Routing Engine
--     decides which event an inbound message belongs to.
--   * `event_code` is globally unique because it is the routing token that
--     contributors type across shared channels (e.g. "#NGR2027").
--   * `platform_admins` and the `events.auto_publish_*` columns are additions
--     beyond the original spec — see README ("Schema additions").
-- =====================================================================


-- ---------------------------------------------------------------------
-- Organizations
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS organizations (
    id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name          VARCHAR(190)    NOT NULL,
    slug          VARCHAR(190)    NOT NULL,
    contact_email VARCHAR(190)    NOT NULL,
    status        ENUM('active','suspended') NOT NULL DEFAULT 'active',
    -- Browser origins allowed to make AUTHENTICATED cross-origin calls
    -- (a custom admin/moderation console on the org's own domain).
    -- NULL/empty => no cross-origin authenticated access. JSON array of origins.
    allowed_origins JSON          NULL DEFAULT NULL,
    created_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_org_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Organization users (admins / moderators of a given org)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS org_users (
    id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    org_id        BIGINT UNSIGNED NOT NULL,
    name          VARCHAR(190)    NOT NULL,
    email         VARCHAR(190)    NOT NULL,
    password_hash VARCHAR(255)    NOT NULL,
    role          ENUM('admin','moderator') NOT NULL DEFAULT 'moderator',
    status        ENUM('active','disabled') NOT NULL DEFAULT 'active',
    created_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_orguser_email (email),
    KEY idx_orguser_org (org_id),
    CONSTRAINT fk_orguser_org FOREIGN KEY (org_id) REFERENCES organizations (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Platform admins (US — operate shared channels, triage, oversight).
-- Deliberately separate from org_users so platform staff are never
-- accidentally scoped to a single organization.
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS platform_admins (
    id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name          VARCHAR(190)    NOT NULL,
    email         VARCHAR(190)    NOT NULL,
    password_hash VARCHAR(255)    NOT NULL,
    status        ENUM('active','disabled') NOT NULL DEFAULT 'active',
    created_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_padmin_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Events (owned by an org). event_code is the public routing token and
-- is UNIQUE PLATFORM-WIDE.
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS events (
    id                    BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    org_id                BIGINT UNSIGNED NOT NULL,
    title                 VARCHAR(190)    NOT NULL,
    event_code            VARCHAR(40)     NOT NULL,          -- e.g. NGR2027 (stored WITHOUT the leading '#')
    description           TEXT            NULL,
    status                ENUM('draft','live','closed') NOT NULL DEFAULT 'draft',
    -- Auto-publish flag (spec: "design the flag but default it off").
    -- NULL  => auto-publish disabled (everything queues for moderation).
    -- Otherwise, submissions from a contributor at >= this trust level skip
    -- the queue and publish immediately.
    auto_publish_min_trust ENUM('medium','high') NULL DEFAULT NULL,
    -- Per-event channel opt-in. NULL => accept ALL channels (default/back-compat).
    -- Otherwise a JSON array of accepted channel types, e.g. ["whatsapp","web"].
    accepted_channels     JSON            NULL DEFAULT NULL,
    starts_at             DATETIME        NULL,
    ends_at               DATETIME        NULL,
    created_at            DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_event_code (event_code),                  -- fast routing lookup + platform-wide uniqueness
    KEY idx_event_org (org_id),
    KEY idx_event_status (status),
    CONSTRAINT fk_event_org FOREIGN KEY (org_id) REFERENCES organizations (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Channels — PLATFORM-level shared communication lines.
-- One row per channel type (whatsapp/telegram/sms/email/web).
-- config_json holds provider credentials + webhook verification secret.
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS channels (
    id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    channel_type        ENUM('whatsapp','telegram','sms','email','web') NOT NULL,
    platform_identifier VARCHAR(190)    NOT NULL,           -- shared WA number / bot @username / SMS shortcode / inbound email addr
    config_json         JSON            NULL,               -- provider creds, webhook secret, phone_number_id, etc.
    status              ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_channel_type (channel_type)               -- one shared line per type
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Contributor accounts — email-keyed identity a person authenticates as.
-- One account can own many channel identities (phone, telegram, …), so a
-- person is recognized across channels once they've logged in with email+OTP.
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS contributor_accounts (
    id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    email        VARCHAR(190)    NOT NULL,
    username     VARCHAR(120)    NULL,
    display_name VARCHAR(190)    NULL,
    verified     TINYINT(1)      NOT NULL DEFAULT 0,   -- email proven via OTP
    created_at   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_account_email (email),
    UNIQUE KEY uq_account_username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Contributors — a sender identity across any channel.
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS contributors (
    id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    identity_type ENUM('phone','telegram_id','email','app_user') NOT NULL,
    identity_value VARCHAR(190)   NOT NULL,                 -- E.164 phone / telegram user id / email / app user id
    display_name  VARCHAR(190)    NULL,
    verified      TINYINT(1)      NOT NULL DEFAULT 0,
    trust_level   ENUM('low','medium','high') NOT NULL DEFAULT 'low',
    blocked       TINYINT(1)      NOT NULL DEFAULT 0,
    -- Legacy simple-registration flag (superseded by channel login below;
    -- kept for display/back-compat, set on successful login).
    registered    TINYINT(1)      NOT NULL DEFAULT 0,
    registered_at DATETIME        NULL,
    -- Channel login (email + OTP). A channel identity links to an account and
    -- holds a conversational auth state + a time-boxed posting session.
    account_id        BIGINT UNSIGNED NULL,
    auth_state        ENUM('none','awaiting_email','awaiting_register','awaiting_otp','active') NOT NULL DEFAULT 'none',
    pending_email     VARCHAR(190) NULL,           -- email entered, pre-verification
    otp_hash          CHAR(64)     NULL,
    otp_expires_at    DATETIME     NULL,
    otp_attempts      TINYINT UNSIGNED NOT NULL DEFAULT 0,
    session_expires_at DATETIME    NULL,           -- 30-day posting window
    first_seen_at DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_contrib_identity (identity_type, identity_value),
    KEY idx_contrib_trust (trust_level),
    KEY idx_contrib_account (account_id),
    CONSTRAINT fk_contrib_account FOREIGN KEY (account_id) REFERENCES contributor_accounts (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Updates — the normalized unit of content. Every channel funnels here.
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS updates (
    id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    event_id            BIGINT UNSIGNED NOT NULL,
    contributor_id      BIGINT UNSIGNED NOT NULL,
    channel_id          BIGINT UNSIGNED NOT NULL,
    content_text        TEXT            NOT NULL,           -- sanitized content (routing token stripped)
    update_type         ENUM('statement','question') NOT NULL DEFAULT 'statement',
    media_url           VARCHAR(500)    NULL,
    media_type          ENUM('none','image','video','audio','document','link','location') NOT NULL DEFAULT 'none',
    latitude            DECIMAL(10,7)   NULL,               -- geotag (from WhatsApp/Telegram location)
    longitude           DECIMAL(10,7)   NULL,
    like_count          INT UNSIGNED    NOT NULL DEFAULT 0, -- denormalized for fast public feed / top-moments
    status              ENUM('pending','approved','rejected','flagged') NOT NULL DEFAULT 'pending',
    provider_message_id VARCHAR(190)    NULL,               -- for webhook idempotency / dedupe
    moderated_by        BIGINT UNSIGNED NULL,
    moderated_at        DATETIME        NULL,
    submitted_at        DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    raw_payload_json    JSON            NULL,               -- original inbound payload, for audits
    PRIMARY KEY (id),
    KEY idx_updates_event_status (event_id, status, submitted_at),  -- moderation queue + public feed
    KEY idx_updates_contributor (contributor_id),
    KEY idx_updates_channel (channel_id),
    UNIQUE KEY uq_updates_provider_msg (channel_id, provider_message_id),
    CONSTRAINT fk_update_event       FOREIGN KEY (event_id)       REFERENCES events (id)       ON DELETE CASCADE,
    CONSTRAINT fk_update_contributor FOREIGN KEY (contributor_id) REFERENCES contributors (id) ON DELETE CASCADE,
    CONSTRAINT fk_update_channel     FOREIGN KEY (channel_id)     REFERENCES channels (id),
    CONSTRAINT fk_update_moderator   FOREIGN KEY (moderated_by)   REFERENCES org_users (id)    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- NOTE on the unique index above: MySQL treats multiple NULLs as distinct,
-- so rows with a NULL provider_message_id (e.g. web-form submissions) do not
-- collide. Webhook rows that DO carry a provider id are deduped per channel.

-- ---------------------------------------------------------------------
-- Unmatched messages — inbound the Routing Engine could not assign.
-- Reviewed by PLATFORM ADMINS (not org moderators).
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS unmatched_messages (
    id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    channel_id          BIGINT UNSIGNED NOT NULL,
    raw_payload_json    JSON            NULL,
    sender_identity     VARCHAR(190)    NULL,
    parsed_content      TEXT            NULL,               -- best-effort extracted body, to aid triage
    attempted_token     VARCHAR(80)     NULL,               -- what we thought the token was, if any
    reason              ENUM('no_event_code_found','invalid_event_code','ambiguous','line_tag_not_active','channel_not_accepted') NOT NULL,
    provider_message_id VARCHAR(190)    NULL,
    resolved            TINYINT(1)      NOT NULL DEFAULT 0,
    received_at         DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_unmatched_resolved (resolved, received_at),
    KEY idx_unmatched_channel (channel_id),
    CONSTRAINT fk_unmatched_channel FOREIGN KEY (channel_id) REFERENCES channels (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Moderation log — audit trail of every moderation action.
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS moderation_log (
    id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    update_id  BIGINT UNSIGNED NOT NULL,
    actor_id   BIGINT UNSIGNED NULL,                        -- org_users.id (nullable: system/auto actions)
    action     VARCHAR(40)     NOT NULL,                    -- approve / reject / edit / flag_contributor / auto_publish ...
    note       TEXT            NULL,
    created_at DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_modlog_update (update_id),
    CONSTRAINT fk_modlog_update FOREIGN KEY (update_id) REFERENCES updates (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- API keys — for org website widget/API consumers.
-- Only the hash is stored; the plaintext is shown once at creation.
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS api_keys (
    id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    org_id     BIGINT UNSIGNED NOT NULL,
    label      VARCHAR(120)    NULL,
    key_prefix VARCHAR(16)     NOT NULL,                    -- first chars, shown in UI for identification
    key_hash   CHAR(64)        NOT NULL,                    -- sha256(secret)
    scopes     VARCHAR(255)    NOT NULL DEFAULT 'public:read',
    created_at DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    revoked_at DATETIME        NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_apikey_hash (key_hash),
    KEY idx_apikey_org (org_id),
    CONSTRAINT fk_apikey_org FOREIGN KEY (org_id) REFERENCES organizations (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Rate limits — sliding-ish fixed-window counters.
-- subject_type + subject_value keeps it generic (contributor id OR ip).
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS rate_limits (
    id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    subject_type  ENUM('contributor','ip') NOT NULL,
    subject_value VARCHAR(190)    NOT NULL,
    event_id      BIGINT UNSIGNED NULL,
    window_start  DATETIME        NOT NULL,
    count         INT UNSIGNED    NOT NULL DEFAULT 0,
    PRIMARY KEY (id),
    UNIQUE KEY uq_ratelimit_window (subject_type, subject_value, event_id, window_start),
    KEY idx_ratelimit_window (window_start)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Webhook event log — structured logging of every inbound webhook and
-- routing decision, for debugging channel integrations / observability.
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS webhook_log (
    id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    channel_type        VARCHAR(20)     NULL,
    provider_message_id VARCHAR(190)    NULL,
    decision            VARCHAR(40)     NULL,               -- routed / unmatched / duplicate / rejected_signature / line_tag
    event_id            BIGINT UNSIGNED NULL,
    detail              TEXT            NULL,
    created_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_webhooklog_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Update likes — reader engagement from org websites (via the widget).
-- Deduped per (update, voter_token). voter_token is an opaque client id
-- the widget stores in localStorage; no reader account required.
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS update_likes (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    update_id   BIGINT UNSIGNED NOT NULL,
    voter_token VARCHAR(80)     NOT NULL,
    ip          VARCHAR(64)     NULL,
    created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_like_voter (update_id, voter_token),
    KEY idx_like_update (update_id),
    CONSTRAINT fk_like_update FOREIGN KEY (update_id) REFERENCES updates (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Event followers — "Follow" / subscribe capture from org websites.
-- Stores the subscription identity; actual notification delivery is a
-- future concern (email/push), noted in the API docs.
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS event_followers (
    id             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    event_id       BIGINT UNSIGNED NOT NULL,
    identity_type  ENUM('email','device') NOT NULL DEFAULT 'email',
    identity_value VARCHAR(190)    NOT NULL,
    created_at     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_follower (event_id, identity_type, identity_value),
    KEY idx_follower_event (event_id),
    CONSTRAINT fk_follower_event FOREIGN KEY (event_id) REFERENCES events (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Auth tokens — DB-backed REFRESH tokens (access tokens stay stateless JWTs).
-- Enables long-lived custom dashboards without forcing re-login, plus
-- server-side revocation (logout). Only the hash is stored.
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS auth_tokens (
    id             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    principal_type ENUM('org_user','platform_admin') NOT NULL,
    principal_id   BIGINT UNSIGNED NOT NULL,
    token_hash     CHAR(64)        NOT NULL,           -- sha256(refresh secret)
    user_agent     VARCHAR(255)    NULL,
    expires_at     DATETIME        NOT NULL,
    revoked_at     DATETIME        NULL,
    created_at     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_authtoken_hash (token_hash),
    KEY idx_authtoken_principal (principal_type, principal_id),
    KEY idx_authtoken_expiry (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Platform settings — key/value overrides editable in the admin portal.
-- A present value overrides the matching .env default (see src/Config.php).
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS platform_settings (
    skey       VARCHAR(64) NOT NULL,
    svalue     TEXT        NULL,
    updated_at DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (skey)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- =====================================================================
-- Optional seed data (safe to run once). Password for both demo logins
-- below is:  password123   (change immediately in any real deployment)
-- Hash generated with PHP password_hash(..., PASSWORD_DEFAULT).
-- =====================================================================
-- INSERT INTO platform_admins (name, email, password_hash)
--   VALUES ('Platform Admin', 'admin@update.local',
--           '$2y$10$e0MYzXyjpJS7Pd0RVvHwHeFjHNMBpT2i3Xn8p3Zt3s8k9Qb2mLQO');
