-- Phase A5  -  Sites (AI-assisted website builder)
--
-- Architecture: deterministic block templates + AI slot-fill + human approve.
-- Drafts live as JSON (block tree); publish renders to a static HTML cache
-- file under storage/sites/{org_id}/{slug}.html and a publish_targets row
-- decides where the bytes go (b-connex hosting / custom domain CNAME /
-- bring-your-own-host webhook+sftp+s3+netlify+cloudflare+vercel).

CREATE TABLE IF NOT EXISTS sites (
    id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    org_id          BIGINT UNSIGNED NOT NULL,
    slug            VARCHAR(64)    NOT NULL,
    title           VARCHAR(220)   NOT NULL,
    status          ENUM('draft','published','archived') NOT NULL DEFAULT 'draft',
    -- Block tree as authored in the editor; render pipeline turns this into HTML.
    draft_json      MEDIUMTEXT     NOT NULL,
    -- Last published render (for fast read; storage path is canonical).
    published_html_path VARCHAR(255) NULL,
    published_at    DATETIME       NULL,
    -- Locale + palette for the whole site; per-block overrides allowed in draft_json.
    locale          VARCHAR(8)     NOT NULL DEFAULT 'en',
    palette         VARCHAR(32)    NOT NULL DEFAULT 'default',
    created_by      INT UNSIGNED   NULL,
    created_at      DATETIME       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uniq_org_slug (org_id, slug),
    KEY idx_status (status),
    CONSTRAINT fk_sites_org FOREIGN KEY (org_id) REFERENCES organizations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Versioned snapshots of draft_json for undo + audit. One row per save.
CREATE TABLE IF NOT EXISTS site_versions (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    site_id     BIGINT UNSIGNED NOT NULL,
    version_no  INT UNSIGNED   NOT NULL,
    -- Where the change came from: 'human' edit, 'ai' suggestion accepted,
    -- 'publish' snapshot, 'rollback' restore.
    source      ENUM('human','ai','publish','rollback') NOT NULL DEFAULT 'human',
    snapshot_json MEDIUMTEXT   NOT NULL,
    note        VARCHAR(255)   NULL,
    created_by  INT UNSIGNED   NULL,
    created_at  DATETIME       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uniq_site_version (site_id, version_no),
    CONSTRAINT fk_sv_site FOREIGN KEY (site_id) REFERENCES sites(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Static asset uploads (images, files) referenced from blocks.
CREATE TABLE IF NOT EXISTS site_assets (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    site_id     BIGINT UNSIGNED NOT NULL,
    kind        ENUM('image','file','video') NOT NULL DEFAULT 'image',
    storage_path VARCHAR(255)  NOT NULL,
    mime        VARCHAR(80)    NULL,
    bytes       INT UNSIGNED   NULL,
    alt_text    VARCHAR(255)   NULL,
    created_at  DATETIME       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_site (site_id),
    CONSTRAINT fk_sa_site FOREIGN KEY (site_id) REFERENCES sites(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Where a published site's bytes get pushed.
--   - bcx_hosted     : default; served from /s/{slug} under our domain
--   - custom_domain  : Scale+; CNAME to b-connex, we still render
--   - webhook        : POST published HTML payload to a tenant-supplied URL
--   - sftp           : push files via SFTP creds
--   - s3             : push to S3-compatible bucket
--   - netlify / cloudflare_pages / vercel : provider deploy hook
-- credentials_enc holds JSON encrypted via App\Core\Crypto so secrets never sit in cleartext.
CREATE TABLE IF NOT EXISTS site_publish_targets (
    id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    site_id         BIGINT UNSIGNED NOT NULL,
    kind            ENUM('bcx_hosted','custom_domain','webhook','sftp','s3','netlify','cloudflare_pages','vercel') NOT NULL DEFAULT 'bcx_hosted',
    label           VARCHAR(120)   NULL,
    target_url      VARCHAR(255)   NULL,
    credentials_enc TEXT           NULL,
    last_pushed_at  DATETIME       NULL,
    last_status     ENUM('ok','error','pending') NULL,
    last_error      VARCHAR(255)   NULL,
    is_active       TINYINT(1)     NOT NULL DEFAULT 1,
    created_at      DATETIME       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_site (site_id),
    KEY idx_kind (kind),
    CONSTRAINT fk_spt_site FOREIGN KEY (site_id) REFERENCES sites(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
