-- إنشاء قاعدة البيانات (عدّل الاسم إن رغبت)
CREATE DATABASE IF NOT EXISTS `yt2tg_db` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `yt2tg_db`;

-- جدول القنوات
CREATE TABLE IF NOT EXISTS `channels` (
    `id`                  INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `title`               VARCHAR(255) DEFAULT NULL,
    `youtube_channel_url` VARCHAR(500) DEFAULT NULL,
    `youtube_feed_url`    VARCHAR(500) DEFAULT NULL,
    `enabled`             TINYINT(1) NOT NULL DEFAULT 1,
    `created_at`          DATETIME DEFAULT NULL,
    `updated_at`          DATETIME DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- جدول الفيديوهات التي تم نشرها
CREATE TABLE IF NOT EXISTS `posted_videos` (
    `id`         INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `video_id`   VARCHAR(50) NOT NULL,
    `channel_id` INT UNSIGNED DEFAULT NULL,
    `posted_at`  DATETIME DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uniq_video_channel` (`video_id`, `channel_id`),
    CONSTRAINT `fk_posted_channel` FOREIGN KEY (`channel_id`) REFERENCES `channels` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
