CREATE DATABASE IF NOT EXISTS `rp_bot` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `rp_bot`;

CREATE TABLE IF NOT EXISTS users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  telegram_id BIGINT UNSIGNED NOT NULL UNIQUE,
  username VARCHAR(255) NULL,
  first_name VARCHAR(255) NULL,
  last_name VARCHAR(255) NULL,
  language ENUM('fa','en','ru') NULL DEFAULT NULL,
  system_role ENUM('user','admin') NOT NULL DEFAULT 'user',
  is_blocked TINYINT(1) NOT NULL DEFAULT 0,
  joined_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_active_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_users_system_role (system_role),
  INDEX idx_users_language (language)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS rp_groups (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL UNIQUE,
  country VARCHAR(120) NULL,
  description TEXT NULL,
  is_hidden TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_groups_hidden (is_hidden)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS rp_roles (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  group_id BIGINT UNSIGNED NULL,
  country VARCHAR(120) NULL,
  category ENUM('top','normal') NOT NULL DEFAULT 'normal',
  status ENUM('available','taken','reserved','hidden') NOT NULL DEFAULT 'available',
  description TEXT NULL,
  image_file_id VARCHAR(255) NULL,
  is_suggested TINYINT(1) NOT NULL DEFAULT 0,
  is_reservable TINYINT(1) NOT NULL DEFAULT 1,
  owner_user_id BIGINT UNSIGNED NULL,
  taken_by_text VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_role_group_name (name, group_id),
  INDEX idx_roles_name (name),
  INDEX idx_roles_category (category),
  INDEX idx_roles_status (status),
  INDEX idx_roles_suggested (is_suggested),
  CONSTRAINT fk_roles_group FOREIGN KEY (group_id) REFERENCES rp_groups(id) ON DELETE SET NULL,
  CONSTRAINT fk_roles_owner FOREIGN KEY (owner_user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS reservations (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  role_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  status ENUM('pending','accepted','rejected','cancelled') NOT NULL DEFAULT 'pending',
  admin_note TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_active_reservation (role_id, user_id, status),
  INDEX idx_res_status (status),
  CONSTRAINT fk_res_role FOREIGN KEY (role_id) REFERENCES rp_roles(id) ON DELETE CASCADE,
  CONSTRAINT fk_res_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS settings (
  key_name VARCHAR(100) PRIMARY KEY,
  value_text MEDIUMTEXT NULL,
  media_file_id VARCHAR(255) NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS broadcasts (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  admin_user_id BIGINT UNSIGNED NULL,
  message_type ENUM('text','photo','video') NOT NULL DEFAULT 'text',
  content_text MEDIUMTEXT NULL,
  media_file_id VARCHAR(255) NULL,
  sent_count INT UNSIGNED NOT NULL DEFAULT 0,
  failed_count INT UNSIGNED NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_broadcast_admin FOREIGN KEY (admin_user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS user_states (
  user_id BIGINT UNSIGNED PRIMARY KEY,
  state VARCHAR(120) NOT NULL,
  data_json JSON NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_state_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS contact_messages (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  message_text MEDIUMTEXT NULL,
  media_file_id VARCHAR(255) NULL,
  status ENUM('new','seen','closed') NOT NULL DEFAULT 'new',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_contact_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_contact_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO settings (key_name, value_text) VALUES
('welcome_fa', 'سلام 👋\nبه بات مدیریت رول‌پلی خوش آمدی.\n\nلطفاً زبان خود را انتخاب کن:'),
('welcome_en', 'Hello 👋\nWelcome to the Roleplay Manager Bot.\n\nPlease choose your language:'),
('welcome_ru', 'Привет 👋\nДобро пожаловать в бот управления ролями.\n\nПожалуйста, выберите язык:'),
('after_language_fa', 'زبان فارسی فعال شد ✅'),
('after_language_en', 'English language activated ✅'),
('after_language_ru', 'Русский язык включен ✅')
ON DUPLICATE KEY UPDATE key_name = VALUES(key_name);

-- Sample data. Delete or edit after install.
INSERT INTO rp_groups (name, country, description) VALUES
('IVE', 'Korea', 'K-pop group'),
('Aespa', 'Korea', 'K-pop group')
ON DUPLICATE KEY UPDATE name = VALUES(name);

INSERT INTO rp_roles (name, group_id, country, category, status, is_suggested, description)
SELECT 'Yujin', id, 'Korea', 'top', 'taken', 1, 'IVE member' FROM rp_groups WHERE name='IVE'
ON DUPLICATE KEY UPDATE name = VALUES(name);
INSERT INTO rp_roles (name, group_id, country, category, status, is_suggested, description)
SELECT 'Rei', id, 'Korea', 'normal', 'available', 0, 'IVE member' FROM rp_groups WHERE name='IVE'
ON DUPLICATE KEY UPDATE name = VALUES(name);
INSERT INTO rp_roles (name, group_id, country, category, status, is_suggested, description)
SELECT 'Karina', id, 'Korea', 'top', 'available', 1, 'Aespa member' FROM rp_groups WHERE name='Aespa'
ON DUPLICATE KEY UPDATE name = VALUES(name);