CREATE TABLE IF NOT EXISTS resource_types (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(120) NOT NULL,
    description VARCHAR(500) NULL,
    icon VARCHAR(50) NOT NULL DEFAULT 'folder',
    sort_order INT NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_resource_types_slug (slug),
    KEY idx_resource_types_active_sort (is_active, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS resources (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    resource_type_id BIGINT UNSIGNED NOT NULL,
    title VARCHAR(255) NOT NULL,
    description TEXT NULL,
    thumbnail_url VARCHAR(500) NULL,
    file_url VARCHAR(500) NOT NULL,
    media_type VARCHAR(20) NOT NULL,
    mime_type VARCHAR(120) NOT NULL,
    file_size BIGINT UNSIGNED NOT NULL DEFAULT 0,
    duration_seconds INT UNSIGNED NULL,
    view_count INT UNSIGNED NOT NULL DEFAULT 0,
    download_count INT UNSIGNED NOT NULL DEFAULT 0,
    is_published TINYINT(1) NOT NULL DEFAULT 1,
    published_at DATETIME NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    CONSTRAINT fk_resources_type FOREIGN KEY (resource_type_id) REFERENCES resource_types(id),
    KEY idx_resources_type_published (resource_type_id, is_published, published_at),
    KEY idx_resources_media_type (media_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO resource_types (name, slug, description, icon, sort_order)
VALUES ('Magazines', 'magazines', 'IMM magazines and publications', 'menu_book', 10)
ON DUPLICATE KEY UPDATE name = VALUES(name);

INSERT INTO resources (
    resource_type_id, title, description, thumbnail_url, file_url, media_type,
    mime_type, view_count, download_count, published_at, created_at
)
SELECT rt.id, m.title, m.description, m.cover_image_url, m.file_url, 'pdf',
       'application/pdf', m.view_count, m.download_count, m.created_at, m.created_at
FROM magazines m
JOIN resource_types rt ON rt.slug = 'magazines'
WHERE NOT EXISTS (
    SELECT 1 FROM resources r WHERE r.file_url = m.file_url
);