-- ============================================================
-- Extended schema: Menus, Welcome, Support, Broadcast
-- Run AFTER database.sql. Safe to re-run (IF NOT EXISTS).
-- ============================================================

SET NAMES utf8mb4;

-- ---------- Welcome templates ----------
CREATE TABLE IF NOT EXISTS `welcome_templates` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(128) NOT NULL,
  `type` ENUM('text','photo','video','animation','document','audio','voice','sticker') NOT NULL DEFAULT 'text',
  `body` TEXT NULL,                 -- caption / text (HTML)
  `media_file_id` VARCHAR(255) NULL,-- telegram file_id or uploaded path
  `buttons` TEXT NULL,              -- JSON inline keyboard
  `is_active` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- Menus (unlimited, nestable) ----------
CREATE TABLE IF NOT EXISTS `menus` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `parent_id` INT UNSIGNED NULL,     -- NULL = root menu
  `title` VARCHAR(128) NOT NULL,     -- button label
  `type` ENUM('text','photo','video','audio','voice','animation','document','sticker') NOT NULL DEFAULT 'text',
  `body` TEXT NULL,                  -- content / caption (HTML)
  `media_file_id` VARCHAR(255) NULL,
  `url` VARCHAR(512) NULL,           -- external URL button (optional)
  `sort` INT NOT NULL DEFAULT 0,     -- drag & drop ordering
  `is_enabled` TINYINT(1) NOT NULL DEFAULT 1,
  `is_hidden` TINYINT(1) NOT NULL DEFAULT 0,
  `is_premium` TINYINT(1) NOT NULL DEFAULT 0,
  `password` VARCHAR(128) NULL,      -- password-protected menu
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_parent` (`parent_id`),
  KEY `idx_sort` (`sort`),
  KEY `idx_enabled` (`is_enabled`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- Support tickets ----------
CREATE TABLE IF NOT EXISTS `support_tickets` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `telegram_id` BIGINT NOT NULL,
  `status` ENUM('open','closed','archived') NOT NULL DEFAULT 'open',
  `is_pinned` TINYINT(1) NOT NULL DEFAULT 0,
  `unread_admin` INT NOT NULL DEFAULT 0,   -- unread messages for admin
  `unread_user` INT NOT NULL DEFAULT 0,
  `last_message_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user` (`telegram_id`),
  KEY `idx_status` (`status`),
  KEY `idx_last` (`last_message_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- Support messages ----------
CREATE TABLE IF NOT EXISTS `support_messages` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `ticket_id` INT UNSIGNED NOT NULL,
  `sender` ENUM('user','admin') NOT NULL,
  `admin_id` INT UNSIGNED NULL,
  `type` VARCHAR(24) NOT NULL DEFAULT 'text',
  `body` TEXT NULL,
  `media_file_id` VARCHAR(255) NULL,
  `tg_message_id` BIGINT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_ticket` (`ticket_id`),
  KEY `idx_time` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- Broadcasts ----------
CREATE TABLE IF NOT EXISTS `broadcasts` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `type` VARCHAR(24) NOT NULL DEFAULT 'text',
  `body` TEXT NULL,
  `media_file_id` VARCHAR(255) NULL,
  `buttons` TEXT NULL,                -- JSON inline keyboard
  `target` VARCHAR(32) NOT NULL DEFAULT 'all', -- all|premium|active|inactive|new|blocked...
  `status` ENUM('draft','queued','running','done','failed') NOT NULL DEFAULT 'draft',
  `total` INT NOT NULL DEFAULT 0,
  `sent` INT NOT NULL DEFAULT 0,
  `failed` INT NOT NULL DEFAULT 0,
  `blocked` INT NOT NULL DEFAULT 0,
  `scheduled_at` DATETIME NULL,
  `created_by` INT UNSIGNED NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `finished_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_status` (`status`),
  KEY `idx_sched` (`scheduled_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- Broadcast queue (per-user delivery rows) ----------
CREATE TABLE IF NOT EXISTS `broadcast_queue` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `broadcast_id` INT UNSIGNED NOT NULL,
  `telegram_id` BIGINT NOT NULL,
  `state` ENUM('pending','sent','failed','blocked') NOT NULL DEFAULT 'pending',
  `attempts` TINYINT NOT NULL DEFAULT 0,
  `error` VARCHAR(255) NULL,
  PRIMARY KEY (`id`),
  KEY `idx_bc_state` (`broadcast_id`, `state`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- Admin sessions (CSRF + login) ----------
CREATE TABLE IF NOT EXISTS `admin_sessions` (
  `id` VARCHAR(64) NOT NULL,
  `admin_id` INT UNSIGNED NOT NULL,
  `csrf` VARCHAR(64) NOT NULL,
  `ip` VARCHAR(45) NULL,
  `expires_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_admin` (`admin_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `settings` (`k`,`v`) VALUES
  ('support_enabled','1'),
  ('broadcast_batch','25'),
  ('menu_enabled','1')
ON DUPLICATE KEY UPDATE `k`=`k`;