CREATE TABLE IF NOT EXISTS users(
  user_id BIGINT PRIMARY KEY,
  username VARCHAR(100) NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS messages(
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT NOT NULL,
  message TEXT NULL,
  is_admin TINYINT(1) NOT NULL DEFAULT 0,
  photo VARCHAR(255) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX(user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS bot_commands(
  id INT AUTO_INCREMENT PRIMARY KEY,
  command VARCHAR(50) NOT NULL UNIQUE,
  text TEXT NULL,
  button_url VARCHAR(255) NULL,
  photo VARCHAR(255) NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS broadcast_jobs(
  id INT AUTO_INCREMENT PRIMARY KEY,
  text TEXT NULL,
  button_url VARCHAR(255) NULL,
  photo VARCHAR(255) NULL,
  total INT NOT NULL DEFAULT 0,
  sent INT NOT NULL DEFAULT 0,
  last_user_id BIGINT NOT NULL DEFAULT 0,
  done TINYINT(1) NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO bot_commands(command, text, button_url, photo, is_active)
VALUES ('/start', 'Halo! 👋\nBot aktif ✅', NULL, NULL, 1);
