-- ============================================================
-- VidReview Mock Data Seed Script
-- Run with:
--   docker exec -i vidreview-db psql -U vidreview -d vidreview < scripts/seed-mock-data.sql
--
-- Behaviour:
--   * If DB already has users  -> skips entirely (update deploy safe)
--   * If DB is empty           -> seeds full mock dataset
-- ============================================================

-- Guard: skip if users already exist
DO $$
BEGIN
  IF EXISTS (SELECT 1 FROM "User" LIMIT 1) THEN
    RAISE NOTICE E'Database already has users -- skipping seed (update deploy detected).\n';
  ELSE
    RAISE NOTICE 'Seeding mock data...';
  END IF;
END
$$;

-- Password hash for "demo1234" (bcrypt, $2a$10$...)
-- Hash: docker exec vidreview-api node -e "require('bcryptjs').hash('demo1234',10).then(h=>console.log(h))"
\set PASS_HASH '$2a$10$lnSHDKXHRTayh.Z6Sx/q7eHecpMywQqG5nWvTWThj6lw0h.a99gyO'
\set ON_ERROR_STOP on

-- Users
INSERT INTO "User" (id, email, name, password, "globalRole", active, "storageQuota", "storageUsed", "createdAt", "updatedAt")
VALUES (gen_random_uuid()::text, 'admin@vidreview.local', 'Admin', :'PASS_HASH', 'ADMIN', true, 524288000, 0, NOW(), NOW());

INSERT INTO "User" (id, email, name, password, "globalRole", active, "storageQuota", "storageUsed", "createdAt", "updatedAt") VALUES
  (gen_random_uuid()::text, 'alice@vidreview.local', 'Alice Johnson',  :'PASS_HASH', 'MEMBER', true, 524288000, 0, NOW(), NOW()),
  (gen_random_uuid()::text, 'bob@vidreview.local',   'Bob Smith',        :'PASS_HASH', 'MEMBER', true, 524288000, 0, NOW(), NOW()),
  (gen_random_uuid()::text, 'carol@vidreview.local', 'Carol White',     :'PASS_HASH', 'MEMBER', true, 524288000, 0, NOW(), NOW()),
  (gen_random_uuid()::text, 'david@vidreview.local', 'David Lee',       :'PASS_HASH', 'MEMBER', true, 524288000, 0, NOW(), NOW()),
  (gen_random_uuid()::text, 'eva@vidreview.local',   'Eva Martinez',    :'PASS_HASH', 'MEMBER', true, 524288000, 0, NOW(), NOW());

-- Projects (each owned by one member)
INSERT INTO "Project" (id, name, "ownerId", "createdAt", "updatedAt") VALUES
  (gen_random_uuid()::text, 'Brand Campaign Q2',       (SELECT id FROM "User" WHERE email='alice@vidreview.local'), NOW(), NOW()),
  (gen_random_uuid()::text, 'Product Launch Video',     (SELECT id FROM "User" WHERE email='alice@vidreview.local'), NOW(), NOW()),
  (gen_random_uuid()::text, 'Internal Training Clips', (SELECT id FROM "User" WHERE email='bob@vidreview.local'),   NOW(), NOW()),
  (gen_random_uuid()::text, 'Customer Testimonials',   (SELECT id FROM "User" WHERE email='bob@vidreview.local'),   NOW(), NOW()),
  (gen_random_uuid()::text, 'Event Highlights Reel',   (SELECT id FROM "User" WHERE email='carol@vidreview.local'), NOW(), NOW()),
  (gen_random_uuid()::text, 'Social Media Shorts',     (SELECT id FROM "User" WHERE email='david@vidreview.local'), NOW(), NOW()),
  (gen_random_uuid()::text, 'How-To Tutorial Series',  (SELECT id FROM "User" WHERE email='eva@vidreview.local'),  NOW(), NOW()),
  (gen_random_uuid()::text, 'Partner Collaboration',   (SELECT id FROM "User" WHERE email='carol@vidreview.local'), NOW(), NOW());

-- Project Members
-- Brand Campaign Q2  (owner=Alice, ADMIN)
INSERT INTO "ProjectMember" ("id","userId","projectId","role","joinedAt","invitedBy")
  SELECT gen_random_uuid()::text, (SELECT id FROM "User" WHERE email='alice@vidreview.local'), id, 'ADMIN', NOW(), (SELECT id FROM "User" WHERE email='alice@vidreview.local')
  FROM "Project" WHERE name='Brand Campaign Q2';

INSERT INTO "ProjectMember" ("id","userId","projectId","role","joinedAt","invitedBy")
  SELECT gen_random_uuid()::text, (SELECT id FROM "User" WHERE email='bob@vidreview.local'), id, 'EDITOR', NOW(), (SELECT id FROM "User" WHERE email='alice@vidreview.local')
  FROM "Project" WHERE name='Brand Campaign Q2';

INSERT INTO "ProjectMember" ("id","userId","projectId","role","joinedAt","invitedBy")
  SELECT gen_random_uuid()::text, (SELECT id FROM "User" WHERE email='carol@vidreview.local'), id, 'REVIEWER', NOW(), (SELECT id FROM "User" WHERE email='alice@vidreview.local')
  FROM "Project" WHERE name='Brand Campaign Q2';

INSERT INTO "ProjectMember" ("id","userId","projectId","role","joinedAt","invitedBy")
  SELECT gen_random_uuid()::text, (SELECT id FROM "User" WHERE email='david@vidreview.local'), id, 'VIEWER', NOW(), (SELECT id FROM "User" WHERE email='alice@vidreview.local')
  FROM "Project" WHERE name='Brand Campaign Q2';

-- Product Launch Video
INSERT INTO "ProjectMember" ("id","userId","projectId","role","joinedAt","invitedBy")
  SELECT gen_random_uuid()::text, (SELECT id FROM "User" WHERE email='alice@vidreview.local'), id, 'ADMIN', NOW(), (SELECT id FROM "User" WHERE email='alice@vidreview.local')
  FROM "Project" WHERE name='Product Launch Video';

INSERT INTO "ProjectMember" ("id","userId","projectId","role","joinedAt","invitedBy")
  SELECT gen_random_uuid()::text, (SELECT id FROM "User" WHERE email='eva@vidreview.local'), id, 'EDITOR', NOW(), (SELECT id FROM "User" WHERE email='alice@vidreview.local')
  FROM "Project" WHERE name='Product Launch Video';

INSERT INTO "ProjectMember" ("id","userId","projectId","role","joinedAt","invitedBy")
  SELECT gen_random_uuid()::text, (SELECT id FROM "User" WHERE email='carol@vidreview.local'), id, 'REVIEWER', NOW(), (SELECT id FROM "User" WHERE email='alice@vidreview.local')
  FROM "Project" WHERE name='Product Launch Video';

-- Internal Training Clips
INSERT INTO "ProjectMember" ("id","userId","projectId","role","joinedAt","invitedBy")
  SELECT gen_random_uuid()::text, (SELECT id FROM "User" WHERE email='bob@vidreview.local'), id, 'ADMIN', NOW(), (SELECT id FROM "User" WHERE email='bob@vidreview.local')
  FROM "Project" WHERE name='Internal Training Clips';

INSERT INTO "ProjectMember" ("id","userId","projectId","role","joinedAt","invitedBy")
  SELECT gen_random_uuid()::text, (SELECT id FROM "User" WHERE email='alice@vidreview.local'), id, 'EDITOR', NOW(), (SELECT id FROM "User" WHERE email='bob@vidreview.local')
  FROM "Project" WHERE name='Internal Training Clips';

INSERT INTO "ProjectMember" ("id","userId","projectId","role","joinedAt","invitedBy")
  SELECT gen_random_uuid()::text, (SELECT id FROM "User" WHERE email='david@vidreview.local'), id, 'REVIEWER', NOW(), (SELECT id FROM "User" WHERE email='bob@vidreview.local')
  FROM "Project" WHERE name='Internal Training Clips';

-- Customer Testimonials
INSERT INTO "ProjectMember" ("id","userId","projectId","role","joinedAt","invitedBy")
  SELECT gen_random_uuid()::text, (SELECT id FROM "User" WHERE email='bob@vidreview.local'), id, 'ADMIN', NOW(), (SELECT id FROM "User" WHERE email='bob@vidreview.local')
  FROM "Project" WHERE name='Customer Testimonials';

INSERT INTO "ProjectMember" ("id","userId","projectId","role","joinedAt","invitedBy")
  SELECT gen_random_uuid()::text, (SELECT id FROM "User" WHERE email='carol@vidreview.local'), id, 'EDITOR', NOW(), (SELECT id FROM "User" WHERE email='bob@vidreview.local')
  FROM "Project" WHERE name='Customer Testimonials';

INSERT INTO "ProjectMember" ("id","userId","projectId","role","joinedAt","invitedBy")
  SELECT gen_random_uuid()::text, (SELECT id FROM "User" WHERE email='eva@vidreview.local'), id, 'REVIEWER', NOW(), (SELECT id FROM "User" WHERE email='bob@vidreview.local')
  FROM "Project" WHERE name='Customer Testimonials';

-- Event Highlights Reel
INSERT INTO "ProjectMember" ("id","userId","projectId","role","joinedAt","invitedBy")
  SELECT gen_random_uuid()::text, (SELECT id FROM "User" WHERE email='carol@vidreview.local'), id, 'ADMIN', NOW(), (SELECT id FROM "User" WHERE email='carol@vidreview.local')
  FROM "Project" WHERE name='Event Highlights Reel';

INSERT INTO "ProjectMember" ("id","userId","projectId","role","joinedAt","invitedBy")
  SELECT gen_random_uuid()::text, (SELECT id FROM "User" WHERE email='bob@vidreview.local'), id, 'EDITOR', NOW(), (SELECT id FROM "User" WHERE email='carol@vidreview.local')
  FROM "Project" WHERE name='Event Highlights Reel';

INSERT INTO "ProjectMember" ("id","userId","projectId","role","joinedAt","invitedBy")
  SELECT gen_random_uuid()::text, (SELECT id FROM "User" WHERE email='alice@vidreview.local'), id, 'REVIEWER', NOW(), (SELECT id FROM "User" WHERE email='carol@vidreview.local')
  FROM "Project" WHERE name='Event Highlights Reel';

-- Social Media Shorts
INSERT INTO "ProjectMember" ("id","userId","projectId","role","joinedAt","invitedBy")
  SELECT gen_random_uuid()::text, (SELECT id FROM "User" WHERE email='david@vidreview.local'), id, 'ADMIN', NOW(), (SELECT id FROM "User" WHERE email='david@vidreview.local')
  FROM "Project" WHERE name='Social Media Shorts';

INSERT INTO "ProjectMember" ("id","userId","projectId","role","joinedAt","invitedBy")
  SELECT gen_random_uuid()::text, (SELECT id FROM "User" WHERE email='alice@vidreview.local'), id, 'EDITOR', NOW(), (SELECT id FROM "User" WHERE email='david@vidreview.local')
  FROM "Project" WHERE name='Social Media Shorts';

INSERT INTO "ProjectMember" ("id","userId","projectId","role","joinedAt","invitedBy")
  SELECT gen_random_uuid()::text, (SELECT id FROM "User" WHERE email='bob@vidreview.local'), id, 'REVIEWER', NOW(), (SELECT id FROM "User" WHERE email='david@vidreview.local')
  FROM "Project" WHERE name='Social Media Shorts';

-- How-To Tutorial Series
INSERT INTO "ProjectMember" ("id","userId","projectId","role","joinedAt","invitedBy")
  SELECT gen_random_uuid()::text, (SELECT id FROM "User" WHERE email='eva@vidreview.local'), id, 'ADMIN', NOW(), (SELECT id FROM "User" WHERE email='eva@vidreview.local')
  FROM "Project" WHERE name='How-To Tutorial Series';

INSERT INTO "ProjectMember" ("id","userId","projectId","role","joinedAt","invitedBy")
  SELECT gen_random_uuid()::text, (SELECT id FROM "User" WHERE email='carol@vidreview.local'), id, 'EDITOR', NOW(), (SELECT id FROM "User" WHERE email='eva@vidreview.local')
  FROM "Project" WHERE name='How-To Tutorial Series';

INSERT INTO "ProjectMember" ("id","userId","projectId","role","joinedAt","invitedBy")
  SELECT gen_random_uuid()::text, (SELECT id FROM "User" WHERE email='david@vidreview.local'), id, 'REVIEWER', NOW(), (SELECT id FROM "User" WHERE email='eva@vidreview.local')
  FROM "Project" WHERE name='How-To Tutorial Series';

-- Partner Collaboration
INSERT INTO "ProjectMember" ("id","userId","projectId","role","joinedAt","invitedBy")
  SELECT gen_random_uuid()::text, (SELECT id FROM "User" WHERE email='carol@vidreview.local'), id, 'ADMIN', NOW(), (SELECT id FROM "User" WHERE email='carol@vidreview.local')
  FROM "Project" WHERE name='Partner Collaboration';

INSERT INTO "ProjectMember" ("id","userId","projectId","role","joinedAt","invitedBy")
  SELECT gen_random_uuid()::text, (SELECT id FROM "User" WHERE email='david@vidreview.local'), id, 'EDITOR', NOW(), (SELECT id FROM "User" WHERE email='carol@vidreview.local')
  FROM "Project" WHERE name='Partner Collaboration';

INSERT INTO "ProjectMember" ("id","userId","projectId","role","joinedAt","invitedBy")
  SELECT gen_random_uuid()::text, (SELECT id FROM "User" WHERE email='eva@vidreview.local'), id, 'REVIEWER', NOW(), (SELECT id FROM "User" WHERE email='carol@vidreview.local')
  FROM "Project" WHERE name='Partner Collaboration';

-- Output summary
\pset format aligned
\pset border 2

\echo ''
\echo '============================================================'
\echo '  VidReview Mock Data Seeded Successfully'
\echo '============================================================'
\echo ''
\echo '  Password (all accounts): demo1234'
\echo '  Storage quota:          500 MB per user'
\echo ''

\echo '-- Users ----------------------------------------------------'
SELECT
  "globalRole"::text                              AS "Role",
  name                                             AS "Name",
  email                                            AS "Email",
  ("storageQuota"::bigint / 1024 / 1024) || ' MB' AS "Quota"
FROM "User"
ORDER BY
  CASE "globalRole" WHEN 'ADMIN' THEN 0 WHEN 'MEMBER' THEN 1 ELSE 2 END,
  name;

\echo ''
\echo '-- Projects & Members ----------------------------------------'
SELECT
  pr.name                       AS "Project",
  u.name                        AS "Member",
  pm.role::text                 AS "Role",
  CASE WHEN pr."ownerId" = u.id THEN '(owner)' ELSE '' END AS "Note"
FROM "Project" pr
JOIN "ProjectMember" pm ON pm."projectId" = pr.id
JOIN "User" u ON u.id = pm."userId"
ORDER BY pr.name, u.name;

\echo ''
\echo '============================================================'
\echo ''