CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE IF NOT EXISTS users (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  email text NOT NULL UNIQUE,
  username text NOT NULL UNIQUE,
  display_name text NOT NULL,
  password_hash text NOT NULL,
  locale text NOT NULL DEFAULT 'ar',
  timezone text NOT NULL DEFAULT 'Asia/Riyadh',
  role text NOT NULL DEFAULT 'user' CHECK (role IN ('user','admin')),
  status text NOT NULL DEFAULT 'active' CHECK (status IN ('active','suspended','banned')),
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS user_sessions (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  token_hash text NOT NULL UNIQUE,
  ip_address text,
  user_agent text,
  expires_at timestamptz NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  last_seen_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_user_sessions_user ON user_sessions(user_id);
CREATE INDEX IF NOT EXISTS idx_user_sessions_expiry ON user_sessions(expires_at);

CREATE TABLE IF NOT EXISTS oauth_states (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  platform text NOT NULL CHECK (platform IN ('twitch','kick','youtube')),
  state_hash text NOT NULL UNIQUE,
  expires_at timestamptz NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS platform_accounts (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  platform text NOT NULL CHECK (platform IN ('twitch','kick','youtube')),
  platform_user_id text NOT NULL,
  username text,
  display_name text,
  avatar_url text,
  channel_url text,
  scopes text[] NOT NULL DEFAULT '{}',
  connected boolean NOT NULL DEFAULT true,
  connected_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE(user_id, platform),
  UNIQUE(platform, platform_user_id)
);

CREATE TABLE IF NOT EXISTS oauth_tokens (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  platform_account_id uuid NOT NULL UNIQUE REFERENCES platform_accounts(id) ON DELETE CASCADE,
  access_token_enc text NOT NULL,
  refresh_token_enc text,
  token_type text,
  expires_at timestamptz,
  updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS bot_settings (
  user_id uuid PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
  enabled boolean NOT NULL DEFAULT false,
  twitch_enabled boolean NOT NULL DEFAULT true,
  kick_enabled boolean NOT NULL DEFAULT true,
  youtube_enabled boolean NOT NULL DEFAULT true,
  default_cooldown_seconds integer NOT NULL DEFAULT 5 CHECK (default_cooldown_seconds BETWEEN 0 AND 86400),
  updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS commands (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  name text NOT NULL,
  response text NOT NULL,
  enabled boolean NOT NULL DEFAULT true,
  platforms text[] NOT NULL DEFAULT ARRAY['twitch','kick','youtube'],
  user_level text NOT NULL DEFAULT 'everyone' CHECK (user_level IN ('everyone','follower','subscriber','moderator','broadcaster')),
  cooldown_seconds integer NOT NULL DEFAULT 5 CHECK (cooldown_seconds BETWEEN 0 AND 86400),
  aliases text[] NOT NULL DEFAULT '{}',
  uses_count bigint NOT NULL DEFAULT 0,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE(user_id, name)
);
CREATE INDEX IF NOT EXISTS idx_commands_user ON commands(user_id);

CREATE TABLE IF NOT EXISTS auto_replies (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  trigger_text text NOT NULL,
  match_type text NOT NULL DEFAULT 'contains' CHECK (match_type IN ('exact','contains','starts_with','ends_with')),
  response text NOT NULL,
  probability smallint NOT NULL DEFAULT 100 CHECK (probability BETWEEN 1 AND 100),
  cooldown_seconds integer NOT NULL DEFAULT 30,
  platforms text[] NOT NULL DEFAULT ARRAY['twitch','kick','youtube'],
  enabled boolean NOT NULL DEFAULT true,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS timers (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  message text NOT NULL,
  interval_minutes integer NOT NULL CHECK (interval_minutes BETWEEN 1 AND 1440),
  min_chat_messages integer NOT NULL DEFAULT 5,
  platforms text[] NOT NULL DEFAULT ARRAY['twitch','kick','youtube'],
  enabled boolean NOT NULL DEFAULT true,
  last_sent_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS moderation_rules (
  user_id uuid PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
  spam_enabled boolean NOT NULL DEFAULT true,
  links_enabled boolean NOT NULL DEFAULT true,
  caps_enabled boolean NOT NULL DEFAULT true,
  symbols_enabled boolean NOT NULL DEFAULT true,
  repeated_enabled boolean NOT NULL DEFAULT true,
  updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS blocked_words (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  word text NOT NULL,
  action text NOT NULL DEFAULT 'delete' CHECK (action IN ('delete','warn','timeout')),
  platforms text[] NOT NULL DEFAULT ARRAY['twitch','kick','youtube'],
  enabled boolean NOT NULL DEFAULT true,
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE(user_id, word)
);

CREATE TABLE IF NOT EXISTS events (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  platform text NOT NULL CHECK (platform IN ('twitch','kick','youtube','system','dev')),
  type text NOT NULL,
  actor_platform_id text,
  actor_username text,
  actor_display_name text,
  amount numeric(14,2),
  currency text,
  payload jsonb NOT NULL DEFAULT '{}'::jsonb,
  occurred_at timestamptz NOT NULL DEFAULT now(),
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_events_user_time ON events(user_id, occurred_at DESC);
CREATE INDEX IF NOT EXISTS idx_events_user_type ON events(user_id, type);

CREATE TABLE IF NOT EXISTS alert_settings (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  event_type text NOT NULL,
  enabled boolean NOT NULL DEFAULT true,
  message_template text NOT NULL DEFAULT '{user}',
  media_url text,
  sound_url text,
  duration_ms integer NOT NULL DEFAULT 5000 CHECK (duration_ms BETWEEN 1000 AND 60000),
  volume smallint NOT NULL DEFAULT 80 CHECK (volume BETWEEN 0 AND 100),
  animation text NOT NULL DEFAULT 'pop',
  text_color text NOT NULL DEFAULT '#ffffff',
  font_size integer NOT NULL DEFAULT 42 CHECK (font_size BETWEEN 12 AND 160),
  updated_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE(user_id, event_type)
);

CREATE TABLE IF NOT EXISTS widget_tokens (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  widget_type text NOT NULL CHECK (widget_type IN ('alerts','chat','latest_follower','latest_subscriber','viewer_count','stream_status')),
  token_hash text NOT NULL UNIQUE,
  created_at timestamptz NOT NULL DEFAULT now(),
  rotated_at timestamptz,
  UNIQUE(user_id, widget_type)
);

CREATE TABLE IF NOT EXISTS audit_logs (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid REFERENCES users(id) ON DELETE SET NULL,
  action text NOT NULL,
  ip_address text,
  user_agent text,
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_audit_logs_user_time ON audit_logs(user_id, created_at DESC);