-- =============================================================================
-- NeuroBot web panel — shared schema (MariaDB 11.8)
-- Idempotent: safe to run on every bot/panel startup.
-- =============================================================================

SET sql_notes = 0;

-- --- Bot-owned ----------------------------------------------------------------

CREATE TABLE IF NOT EXISTS guilds (
  guild_id    BIGINT       NOT NULL PRIMARY KEY,
  name        VARCHAR(128) NOT NULL,
  joined_at   TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  enabled     TINYINT(1)   NOT NULL DEFAULT 1
) ENGINE=InnoDB CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS guild_settings (
  guild_id          BIGINT       NOT NULL PRIMARY KEY,
  clips_channel_id  BIGINT       NULL,
  inviter_user_id   BIGINT       NULL,
  json_blob         JSON         NULL,
  updated_at        TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS restricted_users (
  guild_id      BIGINT    NOT NULL,
  user_id       BIGINT    NOT NULL,
  restricted_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (guild_id, user_id),
  KEY ix_user (user_id)
) ENGINE=InnoDB CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS chitchat_config (
  guild_id                 BIGINT      NOT NULL PRIMARY KEY,
  enabled                  TINYINT(1)  NOT NULL DEFAULT 0,
  channel_id               BIGINT      NULL,
  cadence_min_hours        INT         NOT NULL DEFAULT 4,
  cadence_max_hours        INT         NOT NULL DEFAULT 6,
  posts_per_window_max     INT         NOT NULL DEFAULT 5,
  window_minutes           INT         NOT NULL DEFAULT 60,
  active_start_hour        SMALLINT    NOT NULL DEFAULT 8,
  active_end_hour          SMALLINT    NOT NULL DEFAULT 20,
  updated_at               TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  updated_by_panel_user_id INT         NULL
) ENGINE=InnoDB CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS chitchat_state (
  guild_id        BIGINT      NOT NULL PRIMARY KEY,
  last_fire_iso   VARCHAR(64) NULL,
  next_fire_iso   VARCHAR(64) NULL,
  last_topic      VARCHAR(64) NULL,
  last_question   TEXT        NULL,
  fire_count      INT         NOT NULL DEFAULT 0,
  last_skip_count INT         NOT NULL DEFAULT 0,
  last_post_url   TEXT        NULL,
  updated_at      TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS bot_config (
  config_key   VARCHAR(64) NOT NULL PRIMARY KEY,
  config_value TEXT        NULL,
  is_secret    TINYINT(1)  NOT NULL DEFAULT 0,
  updated_at   TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB CHARSET=utf8mb4;

-- --- Panel-owned -------------------------------------------------------------

CREATE TABLE IF NOT EXISTS panel_users (
  id                 INT          NOT NULL AUTO_INCREMENT PRIMARY KEY,
  discord_id         BIGINT       NOT NULL UNIQUE,
  -- NULL while the row is pre-staged by an admin or via an invite; the OAuth
  -- callback fills these in on the user's first login.
  discord_username   VARCHAR(64)  NULL,
  discord_global_name VARCHAR(64) NULL,
  discord_avatar_url VARCHAR(255) NULL,
  role               ENUM('admin','user') NOT NULL DEFAULT 'user',
  status             ENUM('active','disabled') NOT NULL DEFAULT 'active',
  created_by         INT          NULL,
  created_at         TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_login_at      TIMESTAMP    NULL,
  KEY ix_panel_users_discord (discord_id)
) ENGINE=InnoDB CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS panel_user_guilds (
  panel_user_id INT    NOT NULL,
  guild_id      BIGINT NOT NULL,
  PRIMARY KEY (panel_user_id, guild_id)
) ENGINE=InnoDB CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS panel_user_command_allowlist (
  panel_user_id INT         NOT NULL,
  command       VARCHAR(32) NOT NULL,        -- 'kick','restrict','unrestrict','chitchat','fuck'
  enabled       TINYINT(1)  NOT NULL DEFAULT 1,
  PRIMARY KEY (panel_user_id, command)
) ENGINE=InnoDB CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS panel_invites (
  id                       INT          NOT NULL AUTO_INCREMENT PRIMARY KEY,
  code                     VARCHAR(64)  NOT NULL UNIQUE,
  role                     ENUM('admin','user') NOT NULL DEFAULT 'user',
  created_by               INT          NOT NULL,
  created_at               TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  expires_at               TIMESTAMP    NULL,
  consumed_at              TIMESTAMP    NULL,
  consumed_by_discord_id   BIGINT       NULL,
  default_command_allowlist JSON        NULL,
  KEY ix_panel_invites_code (code),
  KEY ix_panel_invites_created_by (created_by)
) ENGINE=InnoDB CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS audit_log (
  id              BIGINT      NOT NULL AUTO_INCREMENT PRIMARY KEY,
  panel_user_id   INT         NOT NULL,
  action          VARCHAR(64) NOT NULL,
  target_guild_id BIGINT      NULL,
  target_user_id  BIGINT      NULL,
  details         JSON        NULL,
  ip              VARCHAR(64) NULL,
  occurred_at     TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY ix_audit_at (occurred_at),
  KEY ix_audit_user (panel_user_id)
) ENGINE=InnoDB CHARSET=utf8mb4;

-- =============================================================================
-- Seed defaults for panel_users default allowlist (no app-level seeds needed;
-- the panel reads each user's allowlist row and falls back to defaults in code)
-- =============================================================================