-- Phase 8: real TikTok Shop data platform
--   * TikTokConnection -> multi-store (one row per user+shop, was one per user)
--   * Product / ProductMetricSnapshot gain official-API fields
--   * Creator, ProductCreator, Store, StoreMetricSnapshot (new)
--
-- Safe to apply directly: tiktok_connections has 0 rows in production as of
-- this migration (verified before writing it), so restructuring its unique
-- constraint needs no backfill.

-- ============================================================
-- tiktok_connections: multi-store support
-- ============================================================

DROP INDEX "tiktok_connections_userId_key";

ALTER TABLE "tiktok_connections"
    ADD COLUMN "shopId" TEXT NOT NULL DEFAULT '',
    ADD COLUMN "shopName" TEXT;

-- Drop the default now that existing (zero) rows are backfilled — new rows
-- must supply a real shopId going forward.
ALTER TABLE "tiktok_connections" ALTER COLUMN "shopId" DROP DEFAULT;

CREATE UNIQUE INDEX "tiktok_connections_userId_shopId_key" ON "tiktok_connections"("userId", "shopId");
CREATE INDEX "tiktok_connections_userId_idx" ON "tiktok_connections"("userId");

-- ============================================================
-- stores (create before products.storeId FK)
-- ============================================================

CREATE TABLE "stores" (
    "id"           TEXT NOT NULL,
    "externalId"   TEXT NOT NULL,
    "sourceId"     TEXT NOT NULL,
    "name"         TEXT NOT NULL,
    "sellerId"     TEXT,
    "region"       TEXT,
    "category"     TEXT,
    "rating"       DECIMAL(3,2),
    "reviewCount"  INTEGER,
    "productCount" INTEGER,
    "storeScore"   INTEGER,
    "growthScore"  INTEGER,
    "firstSeenAt"  TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "lastSeenAt"   TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "createdAt"    TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt"    TIMESTAMP(3) NOT NULL,

    CONSTRAINT "stores_pkey" PRIMARY KEY ("id")
);

CREATE UNIQUE INDEX "stores_sourceId_externalId_key" ON "stores"("sourceId", "externalId");
CREATE INDEX "stores_sourceId_idx" ON "stores"("sourceId");
CREATE INDEX "stores_storeScore_idx" ON "stores"("storeScore");

ALTER TABLE "stores"
    ADD CONSTRAINT "stores_sourceId_fkey"
    FOREIGN KEY ("sourceId") REFERENCES "data_sources"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

CREATE TABLE "store_metric_snapshots" (
    "id"           TEXT NOT NULL,
    "storeId"      TEXT NOT NULL,
    "capturedAt"   TIMESTAMP(3) NOT NULL,
    "revenue"      DECIMAL(14,2),
    "productCount" INTEGER,
    "rating"       DECIMAL(3,2),
    "reviewCount"  INTEGER,
    "growthRate"   DECIMAL(10,4),
    "createdAt"    TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "store_metric_snapshots_pkey" PRIMARY KEY ("id")
);

CREATE UNIQUE INDEX "store_metric_snapshots_storeId_capturedAt_key" ON "store_metric_snapshots"("storeId", "capturedAt");
CREATE INDEX "store_metric_snapshots_storeId_capturedAt_idx" ON "store_metric_snapshots"("storeId", "capturedAt");

ALTER TABLE "store_metric_snapshots"
    ADD CONSTRAINT "store_metric_snapshots_storeId_fkey"
    FOREIGN KEY ("storeId") REFERENCES "stores"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- ============================================================
-- creators
-- ============================================================

CREATE TABLE "creators" (
    "id"                 TEXT NOT NULL,
    "externalId"         TEXT NOT NULL,
    "sourceId"           TEXT NOT NULL,
    "handle"             TEXT,
    "displayName"        TEXT,
    "avatarUrl"          TEXT,
    "region"             TEXT,
    "followerCount"      INTEGER,
    "videoCount"         INTEGER,
    "avgViews"           BIGINT,
    "estimatedInfluence" INTEGER,
    "qualityScore"       INTEGER,
    "growthRate30d"      DECIMAL(10,4),
    "firstSeenAt"        TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "lastSeenAt"         TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "createdAt"          TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt"          TIMESTAMP(3) NOT NULL,

    CONSTRAINT "creators_pkey" PRIMARY KEY ("id")
);

CREATE UNIQUE INDEX "creators_sourceId_externalId_key" ON "creators"("sourceId", "externalId");
CREATE INDEX "creators_sourceId_idx" ON "creators"("sourceId");
CREATE INDEX "creators_estimatedInfluence_idx" ON "creators"("estimatedInfluence");

ALTER TABLE "creators"
    ADD CONSTRAINT "creators_sourceId_fkey"
    FOREIGN KEY ("sourceId") REFERENCES "data_sources"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

CREATE TABLE "product_creators" (
    "id"              TEXT NOT NULL,
    "productId"       TEXT NOT NULL,
    "creatorId"       TEXT NOT NULL,
    "videoCount"      INTEGER,
    "salesAttributed" DECIMAL(12,2),
    "firstPromotedAt" TIMESTAMP(3),
    "lastPromotedAt"  TIMESTAMP(3),
    "createdAt"       TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt"       TIMESTAMP(3) NOT NULL,

    CONSTRAINT "product_creators_pkey" PRIMARY KEY ("id")
);

CREATE UNIQUE INDEX "product_creators_productId_creatorId_key" ON "product_creators"("productId", "creatorId");
CREATE INDEX "product_creators_creatorId_idx" ON "product_creators"("creatorId");

ALTER TABLE "product_creators"
    ADD CONSTRAINT "product_creators_productId_fkey"
    FOREIGN KEY ("productId") REFERENCES "products"("id") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "product_creators"
    ADD CONSTRAINT "product_creators_creatorId_fkey"
    FOREIGN KEY ("creatorId") REFERENCES "creators"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- ============================================================
-- products: official-API fields
-- ============================================================

ALTER TABLE "products"
    ADD COLUMN "sellerId"      TEXT,
    ADD COLUMN "storeId"       TEXT,
    ADD COLUMN "region"        TEXT,
    ADD COLUMN "stockQuantity" INTEGER,
    ADD COLUMN "status"        TEXT,
    ADD COLUMN "isDeleted"     BOOLEAN NOT NULL DEFAULT false,
    ADD COLUMN "deletedAt"     TIMESTAMP(3);

CREATE INDEX "products_storeId_idx" ON "products"("storeId");
CREATE INDEX "products_isDeleted_idx" ON "products"("isDeleted");

ALTER TABLE "products"
    ADD CONSTRAINT "products_storeId_fkey"
    FOREIGN KEY ("storeId") REFERENCES "stores"("id") ON DELETE SET NULL ON UPDATE CASCADE;

-- ============================================================
-- product_metric_snapshots: official-API metrics fields
-- ============================================================

ALTER TABLE "product_metric_snapshots"
    ADD COLUMN "orders"         INTEGER,
    ADD COLUMN "ctr"            DECIMAL(7,4),
    ADD COLUMN "conversionRate" DECIMAL(7,4),
    ADD COLUMN "returnRate"     DECIMAL(7,4),
    ADD COLUMN "inventoryCount" INTEGER,
    ADD COLUMN "affiliateCount" INTEGER;

-- ============================================================
-- product_scores: Opportunity Intelligence indexed columns (Step 5)
-- ============================================================

ALTER TABLE "product_scores"
    ADD COLUMN "growthVelocityScore"     INTEGER,
    ADD COLUMN "demandAccelerationScore" INTEGER,
    ADD COLUMN "riskScore"               INTEGER,
    ADD COLUMN "competitionTrendScore"   INTEGER;

CREATE INDEX "product_scores_growthVelocityScore_idx" ON "product_scores"("growthVelocityScore");
CREATE INDEX "product_scores_riskScore_idx" ON "product_scores"("riskScore");