CREATE TABLE IF NOT EXISTS attachments_media (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    hostname VARCHAR(100) NOT NULL,
    page VARCHAR(255) NOT NULL,
    url_id BIGINT UNSIGNED NULL,
    media_kind VARCHAR(16) NOT NULL,
    mime VARCHAR(128) NULL,
    title VARCHAR(512) NULL,
    descr VARCHAR(1024) NULL,
    descr_source VARCHAR(16) NULL,
    ocr_text MEDIUMTEXT NULL,
    author VARCHAR(255) NULL,
    software VARCHAR(255) NULL,
    artist VARCHAR(255) NULL,
    album VARCHAR(255) NULL,
    video_codec VARCHAR(64) NULL,
    audio_codec VARCHAR(64) NULL,
    duration_raw VARCHAR(64) NULL,
    bit_rate VARCHAR(64) NULL,
    width INT NULL,
    height INT NULL,
    fps VARCHAR(32) NULL,
    sample_rate VARCHAR(64) NULL,
    channels VARCHAR(32) NULL,
    size_bytes BIGINT UNSIGNED NULL,
    http_etag VARCHAR(255) NULL,
    http_last_modified VARCHAR(255) NULL,
    first_seen TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_seen TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_media_url (hostname, page),
    KEY idx_media_kind (media_kind),
    KEY idx_mime (mime),
    KEY idx_url_id (url_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
