-- =========================================================
-- geloraVIEW 2.0
-- DBD-01 — DATA Canonical Foundation
-- Far     : DATA
-- Purpose : Canonical MELIS/SOLIS store + shared job telemetry
-- Source  : ENGINE 2.0 runtime schema + ENG-08 delta contract
-- =========================================================

CREATE TABLE media_sources (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,

    PRIMARY KEY (id),
    KEY idx_media_sources_active (is_active)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;


CREATE TABLE media_mentions (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    source_id BIGINT UNSIGNED NOT NULL,
    mention_key CHAR(64) NOT NULL,
    title TEXT DEFAULT NULL,
    description TEXT DEFAULT NULL,
    content LONGTEXT DEFAULT NULL,
    url TEXT NOT NULL,
    canonical_url TEXT DEFAULT NULL,
    published_at DATETIME DEFAULT NULL,
    ingested_at DATETIME NOT NULL,
    language VARCHAR(20) DEFAULT NULL,
    sentiment VARCHAR(16) DEFAULT NULL,
    sentiment_score DECIMAL(10,4) DEFAULT NULL,
    image_url TEXT DEFAULT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,

    PRIMARY KEY (id),
    UNIQUE KEY uq_media_mentions_key (mention_key),
    KEY idx_media_mentions_ingested (ingested_at),
    KEY idx_media_mentions_source_ingested (source_id, ingested_at),
    KEY idx_media_mentions_updated (updated_at, id),

    CONSTRAINT fk_media_mentions_source
        FOREIGN KEY (source_id)
        REFERENCES media_sources (id)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;


CREATE TABLE social_objects (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    provider VARCHAR(64) NOT NULL,
    provider_project_key VARCHAR(191) NOT NULL,
    object_id VARCHAR(191) NOT NULL,
    object_name TEXT DEFAULT NULL,
    object_platform VARCHAR(64) DEFAULT NULL,
    stream_type VARCHAR(32) DEFAULT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    synced_at DATETIME DEFAULT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,

    PRIMARY KEY (id),
    UNIQUE KEY uq_social_objects_identity (
        provider,
        provider_project_key,
        object_id
    ),
    KEY idx_social_objects_active_provider (
        provider,
        is_active
    )
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;


CREATE TABLE social_mentions (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    platform VARCHAR(32) NOT NULL,
    original_id VARCHAR(191) NOT NULL,
    published_at_utc DATETIME DEFAULT NULL,
    published_at_local DATETIME DEFAULT NULL,
    author_id VARCHAR(191) DEFAULT NULL,
    author_username VARCHAR(255) DEFAULT NULL,
    author_name VARCHAR(255) DEFAULT NULL,
    author_picture TEXT DEFAULT NULL,
    content LONGTEXT DEFAULT NULL,
    content_type VARCHAR(64) DEFAULT NULL,
    post_ownership VARCHAR(32) DEFAULT NULL,
    url TEXT DEFAULT NULL,
    sentiment VARCHAR(16) DEFAULT NULL,
    share_count BIGINT UNSIGNED NOT NULL DEFAULT 0,
    like_count BIGINT UNSIGNED NOT NULL DEFAULT 0,
    comment_count BIGINT UNSIGNED NOT NULL DEFAULT 0,
    engagement BIGINT UNSIGNED NOT NULL DEFAULT 0,
    engagement_rate DECIMAL(12,6) DEFAULT NULL,
    audience BIGINT UNSIGNED NOT NULL DEFAULT 0,
    provider VARCHAR(64) NOT NULL,
    provider_item_id VARCHAR(191) DEFAULT NULL,
    raw_json LONGTEXT
        CHARACTER SET utf8mb4
        COLLATE utf8mb4_bin
        DEFAULT NULL
        CHECK (json_valid(raw_json)),
    first_seen_at DATETIME NOT NULL,
    last_seen_at DATETIME NOT NULL,
    fetched_at DATETIME NOT NULL,
    data_updated_at DATETIME DEFAULT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,

    PRIMARY KEY (id),
    UNIQUE KEY uq_social_mentions_identity (
        platform,
        original_id
    ),
    KEY idx_social_mentions_fetched (fetched_at),
    KEY idx_social_mentions_provider_fetched (
        provider,
        fetched_at
    ),
    KEY idx_social_mentions_first_seen (first_seen_at),
    KEY idx_social_mentions_data_updated (
        data_updated_at,
        id
    )
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;


CREATE TABLE social_mention_objects (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    mention_id BIGINT UNSIGNED NOT NULL,
    social_object_id BIGINT UNSIGNED NOT NULL,
    provider_item_id VARCHAR(191) DEFAULT NULL,
    raw_json LONGTEXT
        CHARACTER SET utf8mb4
        COLLATE utf8mb4_bin
        DEFAULT NULL
        CHECK (json_valid(raw_json)),
    first_seen_at DATETIME NOT NULL,
    last_seen_at DATETIME NOT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,

    PRIMARY KEY (id),
    UNIQUE KEY uq_social_mention_objects (
        mention_id,
        social_object_id
    ),
    KEY idx_social_mention_objects_object (
        social_object_id
    ),
    KEY idx_social_mention_objects_updated (
        updated_at,
        id
    ),

    CONSTRAINT fk_social_mention_objects_mention
        FOREIGN KEY (mention_id)
        REFERENCES social_mentions (id)
        ON DELETE CASCADE,

    CONSTRAINT fk_social_mention_objects_object
        FOREIGN KEY (social_object_id)
        REFERENCES social_objects (id)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;


CREATE TABLE job_runs (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    job_name VARCHAR(128) NOT NULL,
    far VARCHAR(16) NOT NULL,
    run_id CHAR(23) NOT NULL,
    status VARCHAR(16) NOT NULL,
    started_at DATETIME NOT NULL,
    heartbeat_at DATETIME NOT NULL,
    ended_at DATETIME DEFAULT NULL,
    duration_ms BIGINT UNSIGNED DEFAULT NULL,
    rows_found BIGINT UNSIGNED NOT NULL DEFAULT 0,
    rows_inserted BIGINT UNSIGNED NOT NULL DEFAULT 0,
    rows_updated BIGINT UNSIGNED NOT NULL DEFAULT 0,
    rows_skipped BIGINT UNSIGNED NOT NULL DEFAULT 0,
    rows_failed BIGINT UNSIGNED NOT NULL DEFAULT 0,
    error_code VARCHAR(128) DEFAULT NULL,
    error_message TEXT DEFAULT NULL,
    meta LONGTEXT
        CHARACTER SET utf8mb4
        COLLATE utf8mb4_bin
        NOT NULL
        CHECK (json_valid(meta)),
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,

    PRIMARY KEY (id),
    UNIQUE KEY uq_job_runs_run_id (run_id),
    KEY idx_job_runs_job_started (
        job_name,
        started_at
    ),
    KEY idx_job_runs_status_heartbeat (
        status,
        heartbeat_at
    )
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;