-- ادامه migration — فقط بخش‌هایی که هنوز اعمال نشده‌اند
-- اگر خطایی نداد، یعنی همه چیز از قبل موجود بوده (بی‌خیال شوید)

SET NAMES utf8mb4;

-- get_novel_post_text
SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'admin'
      AND COLUMN_NAME = 'get_novel_post_text'
);
SET @sql = IF(
    @col_exists = 0,
    'ALTER TABLE `admin` ADD COLUMN `get_novel_post_text` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL',
    'SELECT ''get_novel_post_text already exists'' AS info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- مقدار دکمه دریافت رمان
UPDATE `admin`
SET `get_novel_button_text` = '📖 دریافت رمان'
WHERE `id` = 1 AND (`get_novel_button_text` IS NULL OR `get_novel_button_text` = '');

CREATE TABLE IF NOT EXISTS `novels` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) DEFAULT NULL,
  `post_chatid` bigint(20) NOT NULL,
  `post_message_id` bigint(20) NOT NULL,
  `channel_message_id` bigint(20) DEFAULT NULL,
  `status` enum('draft','published','deleted') NOT NULL DEFAULT 'draft',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_novels_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `novel_contents` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `novel_id` int(11) NOT NULL,
  `from_chatid` bigint(20) NOT NULL,
  `message_id` bigint(20) NOT NULL,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_novel_contents_novel` (`novel_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `channel_messages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `channel_id` varchar(64) NOT NULL,
  `message_id` bigint(20) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_channel_messages_lookup` (`channel_id`, `id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `novel_downloads` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `novel_id` int(11) NOT NULL,
  `chatid` bigint(20) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_novel_user` (`novel_id`, `chatid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ثبت ریکشن کاربران روی پست‌های کانال
CREATE TABLE IF NOT EXISTS `user_channel_reactions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `chatid` bigint(20) NOT NULL,
  `channel_id` varchar(64) NOT NULL,
  `message_id` bigint(20) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_user_post_reaction` (`chatid`, `channel_id`, `message_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ادمین ربات
UPDATE `admin` SET `admin_list` = '5803067539,8276840769' WHERE `id` = 1;

CREATE TABLE IF NOT EXISTS `user_submissions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `chatid` bigint(20) NOT NULL,
  `status` enum('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `approved_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_submissions_chatid` (`chatid`),
  KEY `idx_submissions_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `submission_contents` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `submission_id` int(11) NOT NULL,
  `from_chatid` bigint(20) NOT NULL,
  `message_id` bigint(20) NOT NULL,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_submission_contents` (`submission_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `pending_message_deletes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `chatid` bigint(20) NOT NULL,
  `message_id` bigint(20) NOT NULL,
  `delete_at` int(11) NOT NULL,
  `submission_id` int(11) DEFAULT NULL,
  `batch_id` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_pending_delete_at` (`delete_at`),
  KEY `idx_pending_delete_batch` (`batch_id`, `chatid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'pending_message_deletes'
      AND COLUMN_NAME = 'submission_id'
);
SET @sql = IF(
    @col_exists = 0,
    'ALTER TABLE `pending_message_deletes` ADD COLUMN `submission_id` int(11) DEFAULT NULL AFTER `delete_at`',
    'SELECT ''submission_id already exists'' AS info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'pending_message_deletes'
      AND COLUMN_NAME = 'batch_id'
);
SET @sql = IF(
    @col_exists = 0,
    'ALTER TABLE `pending_message_deletes` ADD COLUMN `batch_id` varchar(64) DEFAULT NULL AFTER `submission_id`',
    'SELECT ''batch_id already exists'' AS info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;