-- MySQL utf8mb4 emoji destekli tablo yapısı
-- Telegram Post Manager Pro Database
-- Create database first: CREATE DATABASE telegram_post_manager;

START TRANSACTION;

-- Channels table
CREATE TABLE `channels` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `bot_id` int(11) NOT NULL,
    `channel_id` varchar(100) NOT NULL,
    `title` varchar(255) DEFAULT NULL,
    `username` varchar(100) DEFAULT NULL,
    `is_active` boolean DEFAULT 1,
    `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `bot_id` (`bot_id`),
    CONSTRAINT `channels_ibfk_1` FOREIGN KEY (`bot_id`) REFERENCES `bots` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Posts table
CREATE TABLE `posts` (
    `id` varchar(50) NOT NULL,
    `user_id` int(11) DEFAULT NULL,
    `title` varchar(255) DEFAULT NULL,
    `text` text,
    `image_url` varchar(500) DEFAULT NULL,
    `buttons` json DEFAULT NULL,
    `parse_mode` enum('Markdown','HTML') DEFAULT 'Markdown',
    `status` enum('draft','scheduled','sent','failed') DEFAULT 'draft',
    `created_at` int(11) NOT NULL,
    `updated_at` int(11) NOT NULL,
    `sent_at` int(11) DEFAULT NULL,
    `message_id` int(11) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `user_id` (`user_id`),
    KEY `status` (`status`),
    CONSTRAINT `posts_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Scheduled posts table
CREATE TABLE `scheduled_posts` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `post_id` varchar(50) NOT NULL,
    `bot_id` int(11) NOT NULL,
    `channel_id` varchar(100) NOT NULL,
    `send_time` int(11) NOT NULL,
    `status` enum('pending','sent','failed') DEFAULT 'pending',
    `error_message` text DEFAULT NULL,
    `created_at` int(11) NOT NULL,
    `sent_at` int(11) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `post_id` (`post_id`),
    KEY `bot_id` (`bot_id`),
    KEY `send_time` (`send_time`),
    KEY `status` (`status`),
    CONSTRAINT `scheduled_posts_ibfk_1` FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`) ON DELETE CASCADE,
    CONSTRAINT `scheduled_posts_ibfk_2` FOREIGN KEY (`bot_id`) REFERENCES `bots` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Logs table for error tracking
CREATE TABLE `logs` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `type` enum('info','warning','error') DEFAULT 'info',
    `message` text NOT NULL,
    `data` json DEFAULT NULL,
    `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `type` (`type`),
    KEY `created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

COMMIT;