CREATE TABLE users (
  id TEXT PRIMARY KEY,
  email TEXT UNIQUE,
  name TEXT,
  image_url TEXT,
  role TEXT NOT NULL DEFAULT 'user' CHECK (role IN ('user', 'admin')),
  created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
  updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
);

CREATE TABLE oauth_accounts (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  provider TEXT NOT NULL CHECK (provider IN ('google', 'github', 'apple')),
  provider_account_id TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
  UNIQUE(provider, provider_account_id)
);

CREATE TABLE sessions (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  session_token_hash TEXT NOT NULL UNIQUE,
  expires_at TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
);

CREATE TABLE links (
  id TEXT PRIMARY KEY,
  scope TEXT NOT NULL CHECK (scope IN ('public', 'private')),
  owner_user_id TEXT REFERENCES users(id) ON DELETE CASCADE,
  alias TEXT NOT NULL,
  link_type TEXT NOT NULL CHECK (link_type IN ('redirect', 'custom')),
  target_url TEXT,
  content_markdown TEXT,
  description TEXT,
  status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'archived', 'deleted')),
  click_count INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
  updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
  CHECK (
    (scope = 'public' AND owner_user_id IS NULL)
    OR (scope = 'private' AND owner_user_id IS NOT NULL)
  ),
  CHECK (
    (link_type = 'redirect' AND target_url IS NOT NULL)
    OR (link_type = 'custom' AND content_markdown IS NOT NULL)
  )
);

CREATE UNIQUE INDEX links_public_alias_unique
  ON links(alias)
  WHERE scope = 'public' AND status != 'deleted';

CREATE UNIQUE INDEX links_private_owner_alias_unique
  ON links(owner_user_id, alias)
  WHERE scope = 'private' AND status != 'deleted';

CREATE INDEX links_owner_idx ON links(owner_user_id, updated_at DESC);
CREATE INDEX links_scope_updated_idx ON links(scope, updated_at DESC);

CREATE TABLE tags (
  id TEXT PRIMARY KEY,
  slug TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
);

CREATE TABLE link_tags (
  link_id TEXT NOT NULL REFERENCES links(id) ON DELETE CASCADE,
  tag_id TEXT NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
  PRIMARY KEY (link_id, tag_id)
);

CREATE TABLE promotion_submissions (
  id TEXT PRIMARY KEY,
  private_link_id TEXT NOT NULL REFERENCES links(id) ON DELETE CASCADE,
  submitted_by_user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  proposed_alias TEXT NOT NULL,
  note TEXT,
  status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'approved', 'rejected', 'needs_changes')),
  reviewed_by_user_id TEXT REFERENCES users(id),
  rejection_reason TEXT,
  public_link_id TEXT REFERENCES links(id),
  created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
  reviewed_at TEXT
);

CREATE INDEX promotion_status_idx ON promotion_submissions(status, created_at DESC);

CREATE TABLE click_daily (
  link_id TEXT NOT NULL REFERENCES links(id) ON DELETE CASCADE,
  day TEXT NOT NULL,
  count INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (link_id, day)
);