-- ============================================================
-- vibration_drive – Migration 003: Generalized External Resources
-- ============================================================

SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS `vibration_external_resource` (
    `id`                    INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    `workspace_id`          INT UNSIGNED    NOT NULL,
    `source_url`            TEXT            NOT NULL,
    `normalized_url`        TEXT            NOT NULL,
    `canonical_url`         TEXT            NULL DEFAULT NULL,
    `host`                  VARCHAR(255)    NULL DEFAULT NULL,
    `type`                  ENUM('video', 'audio', 'article', 'webpage', 'other') NOT NULL DEFAULT 'other',
    `provider_name`         VARCHAR(100)    NULL DEFAULT NULL,
    `external_id`           VARCHAR(255)    NULL DEFAULT NULL COMMENT 'ex: youtube/vimeo video_id',
    `title`                 VARCHAR(500)    NULL DEFAULT NULL,
    `description_text`      TEXT            NULL DEFAULT NULL,
    `author_name`           VARCHAR(255)    NULL DEFAULT NULL,
    `author_url`            TEXT            NULL DEFAULT NULL,
    `publisher_name`        VARCHAR(255)    NULL DEFAULT NULL,
    `language`              VARCHAR(10)     NULL DEFAULT NULL,
    `published_at`          DATETIME        NULL DEFAULT NULL,
    `duration_seconds`      INT UNSIGNED    NULL DEFAULT NULL,
    `best_thumbnail_url`    TEXT            NULL DEFAULT NULL,
    `thumbnails_json`       JSON            NULL DEFAULT NULL,
    `embed_html`            TEXT            NULL DEFAULT NULL,
    `extracted_text`        LONGTEXT        NULL DEFAULT NULL,
    `reading_time_minutes`  INT UNSIGNED    NULL DEFAULT NULL,
    `keywords_json`         JSON            NULL DEFAULT NULL,
    `metadata_json`         LONGTEXT        NULL DEFAULT NULL COMMENT 'raw JSON (og/twitter/jsonld/oembed)',
    `status`                ENUM('pending', 'ok', 'failed') NOT NULL DEFAULT 'pending',
    `last_error`            TEXT            NULL DEFAULT NULL,
    `fetched_at`            DATETIME        NULL DEFAULT NULL,
    `created_at`            DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`            DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_ext_workspace` (`workspace_id`),
    INDEX `idx_ext_status`    (`status`),
    INDEX `idx_ext_type`      (`type`),
    INDEX `idx_ext_host`      (`host`),
    CONSTRAINT `fk_ext_workspace_gen` FOREIGN KEY (`workspace_id`) REFERENCES `vibration_workspace`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `vibration_external_resource_link` (
    `id`                    INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    `workspace_id`          INT UNSIGNED    NOT NULL,
    `entity_type`           VARCHAR(50)     NOT NULL COMMENT 'file, article, media, link',
    `entity_id`             INT UNSIGNED    NOT NULL,
    `external_resource_id`  INT UNSIGNED    NOT NULL,
    `role`                  VARCHAR(50)     NOT NULL DEFAULT 'primary',
    `created_at`            DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_ext_link_entity` (`entity_type`, `entity_id`),
    CONSTRAINT `fk_ext_link_res_gen` FOREIGN KEY (`external_resource_id`) REFERENCES `vibration_external_resource`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
