-- Auto-run once by MySQL's docker-entrypoint-initdb.d on first container
-- start (an empty data volume). To re-apply after schema changes, run it
-- manually instead: see server/README.md.

CREATE TABLE IF NOT EXISTS users (
  id CHAR(36) PRIMARY KEY,              -- keeps the existing UUID shape so old session JWTs' userId claims still resolve
  provider VARCHAR(16) NOT NULL,        -- 'google' | 'apple'
  provider_user_id VARCHAR(255) NOT NULL,
  email VARCHAR(255) NULL,
  name VARCHAR(255) NULL,
  avatar_url TEXT NULL,
  free_downloads_used INT NOT NULL DEFAULT 0,
  purchased_credits INT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_provider_identity (provider, provider_user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS purchases (
  id CHAR(36) PRIMARY KEY,
  user_id CHAR(36) NOT NULL,
  platform VARCHAR(16) NOT NULL,        -- 'ios' | 'android'
  product_id VARCHAR(255) NOT NULL,
  transaction_id VARCHAR(255) NOT NULL, -- platform's own transaction id
  credits_granted INT NOT NULL,
  status TINYINT UNSIGNED NOT NULL DEFAULT 1, -- see PurchaseStatus enum in purchaseStore.js (1=verified, 2=verified_dev_mode)
  raw_receipt TEXT NULL,                -- kept for audit/debugging
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_transaction (platform, transaction_id), -- prevents double-crediting a replayed receipt
  CONSTRAINT fk_purchases_user FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- The buy-credits product catalog. The server is the source of truth for
-- *which* IAP products exist and how many credits each grants — the client
-- fetches this list rather than hardcoding it, so adding/retiring a tier is
-- a DB edit, not an app update. Actual displayed price still comes from
-- StoreKit/Play Billing at purchase time, not from this table.
CREATE TABLE IF NOT EXISTS credit_products (
  product_id VARCHAR(128) PRIMARY KEY,  -- must match the SKU created in App Store Connect / Play Console
  platform VARCHAR(16) NOT NULL,        -- 'ios' | 'android'
  credits_granted INT NOT NULL,
  display_order INT NOT NULL DEFAULT 0,
  active TINYINT UNSIGNED NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- iOS credit amounts are 29/99/299, not 1/10/100 — Apple's minimum
-- price point for the India storefront is ₹29, so ₹1 was never
-- purchasable. Kept the 1-credit-per-rupee ratio exactly, just anchored
-- to real tiers (see docs/PLAN_MONETIZATION.md's running log).
INSERT INTO credit_products (product_id, platform, credits_granted, display_order) VALUES
  ('com.trentiums.ytdownloadplayer.credits_29', 'ios', 29, 1),
  ('com.trentiums.ytdownloadplayer.credits_99', 'ios', 99, 2),
  ('com.trentiums.ytdownloadplayer.credits_299', 'ios', 299, 3),
  -- Android package is now com.trentiums.ytdownloadplayer, same as iOS's
  -- bundle id — Play Console product ids are scoped to the app's own
  -- package though, so no reverse-DNS prefix is needed; plain names are
  -- the normal convention regardless.
  ('credits_1', 'android', 1, 1),
  ('credits_10', 'android', 10, 2),
  ('credits_100', 'android', 100, 3)
ON DUPLICATE KEY UPDATE product_id = product_id;

-- One row per completed download, for the account-wide "History" screen
-- (Settings > History). Unlike every other table here, the PK is a plain
-- auto-increment BIGINT rather than a UUID — cursor pagination (`WHERE id <
-- ?  ORDER BY id DESC`) needs a monotonic key, and nothing external ever
-- needs to predict/construct one of these ids the way session JWTs encode
-- a user id, so there's no reason to keep the UUID convention here.
CREATE TABLE IF NOT EXISTS download_history (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  user_id CHAR(36) NOT NULL,
  video_id VARCHAR(32) NULL,       -- YouTube video id; null if it couldn't be parsed from the URL
  title VARCHAR(512) NULL,
  channel VARCHAR(255) NULL,
  thumbnail TEXT NULL,
  webpage_url TEXT NULL,
  format_id VARCHAR(64) NOT NULL,  -- e.g. 'audio-320', 'video-1080p'
  type VARCHAR(8) NOT NULL,        -- 'video' | 'audio'
  duration_seconds INT NULL,
  file_size BIGINT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_history_user FOREIGN KEY (user_id) REFERENCES users(id),
  INDEX idx_history_user_id (user_id, id DESC)  -- the exact access pattern GET /api/history uses
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
