`db/001_schema.sql`

257 lines
-- Iwascute.com — Core Schema
-- Dual-rights clearance marketplace for childhood photos

-- Extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- ============================================================
-- USERS
-- ============================================================
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  email TEXT UNIQUE NOT NULL,
  password_hash TEXT, -- NULL for OAuth-only users
  display_name TEXT NOT NULL,
  role TEXT NOT NULL DEFAULT 'licensor' CHECK (role IN ('licensor', 'licensee', 'admin')),
  is_verified BOOLEAN DEFAULT FALSE,
  is_adult_confirmed BOOLEAN DEFAULT FALSE,
  avatar_url TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Admin user
INSERT INTO users (email, password_hash, display_name, role, is_verified, is_adult_confirmed)
VALUES (
  'admin@iwascute.com',
  crypt('DWSecure2024!', gen_salt('bf')),
  'Admin',
  'admin',
  TRUE,
  TRUE
);

-- ============================================================
-- UPLOADS (a submission = batch of photos + rights info)
-- ============================================================
CREATE TABLE uploads (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  status TEXT NOT NULL DEFAULT 'pending'
    CHECK (status IN ('pending', 'in_review', 'approved', 'rejected', 'flagged', 'dmca_removed')),

  -- Copyright info (step 2)
  copyright_relationship TEXT CHECK (copyright_relationship IN ('parent', 'family', 'professional', 'unknown')),
  copyright_photographer TEXT,
  copyright_year INTEGER,
  copyright_permission TEXT CHECK (copyright_permission IN ('yes', 'no', 'deceased')),
  copyright_estate_rights BOOLEAN DEFAULT FALSE,

  -- Likeness release (step 3)
  likeness_is_subject BOOLEAN DEFAULT FALSE,
  likeness_is_adult BOOLEAN DEFAULT FALSE,
  likeness_editorial BOOLEAN DEFAULT FALSE,
  likeness_commercial BOOLEAN DEFAULT FALSE,
  likeness_ai_training BOOLEAN DEFAULT FALSE,
  likeness_signature TEXT,
  likeness_signed_at TIMESTAMPTZ,

  -- Review
  reviewed_by UUID REFERENCES users(id),
  reviewed_at TIMESTAMPTZ,
  review_notes TEXT,
  rejection_reason TEXT,

  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- ============================================================
-- PHOTOS (individual images within an upload)
-- ============================================================
CREATE TABLE photos (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  upload_id UUID NOT NULL REFERENCES uploads(id) ON DELETE CASCADE,
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,

  -- File info
  filename TEXT NOT NULL,
  file_size INTEGER,
  mime_type TEXT,
  storage_path TEXT, -- future: S3/R2 path
  thumbnail_path TEXT,

  -- EXIF metadata
  exif_date_taken TIMESTAMPTZ,
  exif_year INTEGER,
  exif_latitude DOUBLE PRECISION,
  exif_longitude DOUBLE PRECISION,
  exif_camera_make TEXT,
  exif_camera_model TEXT,
  exif_width INTEGER,
  exif_height INTEGER,

  -- Clearance status (inherits from upload, can be overridden per-photo)
  clearance_level TEXT DEFAULT 'none'
    CHECK (clearance_level IN ('none', 'editorial', 'commercial', 'ai_training')),

  -- Licensing
  view_count INTEGER DEFAULT 0,
  license_count INTEGER DEFAULT 0,
  total_earnings NUMERIC(10,2) DEFAULT 0,

  -- Directory grouping
  directory_name TEXT,

  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- ============================================================
-- DMCA CLAIMS
-- ============================================================
CREATE TABLE dmca_claims (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  upload_id UUID REFERENCES uploads(id),
  photo_id UUID REFERENCES photos(id),

  claimant_name TEXT NOT NULL,
  claimant_email TEXT,
  reason TEXT NOT NULL,
  evidence_url TEXT,

  status TEXT NOT NULL DEFAULT 'open'
    CHECK (status IN ('open', 'investigating', 'taken_down', 'counter_noticed', 'resolved', 'dismissed')),

  -- Resolution
  resolved_by UUID REFERENCES users(id),
  resolved_at TIMESTAMPTZ,
  resolution_notes TEXT,

  filed_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- ============================================================
-- AUDIT LOG
-- ============================================================
CREATE TABLE audit_log (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID REFERENCES users(id),
  action TEXT NOT NULL, -- 'upload.approve', 'upload.reject', 'dmca.takedown', etc
  target_type TEXT, -- 'upload', 'photo', 'dmca_claim', 'user'
  target_id UUID,
  details JSONB,
  ip_address INET,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- ============================================================
-- SESSIONS (simple cookie auth)
-- ============================================================
CREATE TABLE sessions (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  token TEXT UNIQUE NOT NULL DEFAULT encode(gen_random_bytes(32), 'hex'),
  expires_at TIMESTAMPTZ NOT NULL DEFAULT NOW() + INTERVAL '7 days',
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- ============================================================
-- INDEXES
-- ============================================================
CREATE INDEX idx_uploads_user ON uploads(user_id);
CREATE INDEX idx_uploads_status ON uploads(status);
CREATE INDEX idx_photos_upload ON photos(upload_id);
CREATE INDEX idx_photos_user ON photos(user_id);
CREATE INDEX idx_dmca_status ON dmca_claims(status);
CREATE INDEX idx_sessions_token ON sessions(token);
CREATE INDEX idx_sessions_expires ON sessions(expires_at);
CREATE INDEX idx_audit_user ON audit_log(user_id);
CREATE INDEX idx_audit_action ON audit_log(action);

-- ============================================================
-- SEED DATA (demo uploads for admin testing)
-- ============================================================
DO $$
DECLARE
  u1 UUID; u2 UUID; u3 UUID; u4 UUID; u5 UUID; u6 UUID;
  up1 UUID; up2 UUID; up3 UUID; up4 UUID; up5 UUID; up6 UUID;
BEGIN
  -- Create demo users
  INSERT INTO users (email, password_hash, display_name, role, is_verified, is_adult_confirmed)
  VALUES ('sarah.m@email.com', crypt('demo123', gen_salt('bf')), 'Sarah Martinez', 'licensor', TRUE, TRUE) RETURNING id INTO u1;
  INSERT INTO users (email, password_hash, display_name, role, is_verified, is_adult_confirmed)
  VALUES ('james.k@email.com', crypt('demo123', gen_salt('bf')), 'James Kim', 'licensor', TRUE, TRUE) RETURNING id INTO u2;
  INSERT INTO users (email, password_hash, display_name, role, is_verified, is_adult_confirmed)
  VALUES ('maria.l@email.com', crypt('demo123', gen_salt('bf')), 'Maria Lopez', 'licensor', TRUE, TRUE) RETURNING id INTO u3;
  INSERT INTO users (email, password_hash, display_name, role, is_verified, is_adult_confirmed)
  VALUES ('david.r@email.com', crypt('demo123', gen_salt('bf')), 'David Reynolds', 'licensor', TRUE, TRUE) RETURNING id INTO u4;
  INSERT INTO users (email, password_hash, display_name, role, is_verified, is_adult_confirmed)
  VALUES ('emma.t@email.com', crypt('demo123', gen_salt('bf')), 'Emma Thompson', 'licensor', TRUE, TRUE) RETURNING id INTO u5;
  INSERT INTO users (email, password_hash, display_name, role, is_verified, is_adult_confirmed)
  VALUES ('alex.w@email.com', crypt('demo123', gen_salt('bf')), 'Alex Wilson', 'licensor', TRUE, TRUE) RETURNING id INTO u6;

  -- Create uploads (pending review)
  INSERT INTO uploads (user_id, status, copyright_relationship, copyright_permission, likeness_is_subject, likeness_is_adult, likeness_editorial, likeness_commercial, likeness_signature, likeness_signed_at)
  VALUES (u1, 'pending', 'parent', 'yes', TRUE, TRUE, TRUE, TRUE, 'Sarah Martinez', NOW() - INTERVAL '2 hours') RETURNING id INTO up1;
  INSERT INTO uploads (user_id, status, copyright_relationship, copyright_photographer, copyright_permission, copyright_estate_rights, likeness_is_subject, likeness_is_adult, likeness_editorial, likeness_signature, likeness_signed_at)
  VALUES (u2, 'pending', 'professional', 'Jane Doe Photography', 'deceased', TRUE, TRUE, TRUE, TRUE, 'James Kim', NOW() - INTERVAL '5 hours') RETURNING id INTO up2;
  INSERT INTO uploads (user_id, status, copyright_relationship, copyright_permission, likeness_is_subject, likeness_is_adult, likeness_editorial, likeness_commercial, likeness_ai_training, likeness_signature, likeness_signed_at)
  VALUES (u3, 'pending', 'family', 'yes', TRUE, TRUE, TRUE, TRUE, TRUE, 'Maria Lopez', NOW() - INTERVAL '8 hours') RETURNING id INTO up3;
  INSERT INTO uploads (user_id, status, copyright_relationship, copyright_permission, likeness_is_subject, likeness_is_adult, likeness_editorial, likeness_signature, likeness_signed_at)
  VALUES (u4, 'flagged', 'unknown', 'no', TRUE, TRUE, TRUE, 'David Reynolds', NOW() - INTERVAL '12 hours') RETURNING id INTO up4;
  INSERT INTO uploads (user_id, status, copyright_relationship, copyright_permission, likeness_is_subject, likeness_is_adult, likeness_editorial, likeness_commercial, likeness_signature, likeness_signed_at)
  VALUES (u5, 'pending', 'parent', 'yes', TRUE, TRUE, TRUE, TRUE, 'Emma Thompson', NOW() - INTERVAL '1 day') RETURNING id INTO up5;
  INSERT INTO uploads (user_id, status, copyright_relationship, copyright_permission, copyright_estate_rights, likeness_is_subject, likeness_is_adult, likeness_editorial, likeness_commercial, likeness_ai_training, likeness_signature, likeness_signed_at)
  VALUES (u6, 'pending', 'parent', 'deceased', TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, 'Alex Wilson', NOW() - INTERVAL '1 day') RETURNING id INTO up6;

  -- Add photos to uploads
  INSERT INTO photos (upload_id, user_id, filename, file_size, mime_type, exif_year) VALUES
    (up1, u1, 'summer_1992.jpg', 2400000, 'image/jpeg', 1992),
    (up1, u1, 'birthday_party.jpg', 1800000, 'image/jpeg', 1993),
    (up1, u1, 'first_steps.jpg', 3100000, 'image/jpeg', 1991),
    (up1, u1, 'beach_day.jpg', 2200000, 'image/jpeg', 1994),
    (up2, u2, 'school_portrait.jpg', 4500000, 'image/jpeg', 1988),
    (up3, u3, 'christmas_1995.jpg', 1900000, 'image/jpeg', 1995),
    (up3, u3, 'halloween.jpg', 2100000, 'image/jpeg', 1996),
    (up3, u3, 'easter_egg_hunt.jpg', 1700000, 'image/jpeg', 1995),
    (up3, u3, 'playground.jpg', 2800000, 'image/jpeg', 1997),
    (up3, u3, 'first_bike.jpg', 3200000, 'image/jpeg', 1996),
    (up3, u3, 'family_vacation.jpg', 2500000, 'image/jpeg', 1998),
    (up3, u3, 'grandma_house.jpg', 1600000, 'image/jpeg', 1994),
    (up3, u3, 'snow_day.jpg', 2900000, 'image/jpeg', 1997),
    (up4, u4, 'old_photo_1.jpg', 1200000, 'image/jpeg', NULL),
    (up4, u4, 'old_photo_2.jpg', 1500000, 'image/jpeg', NULL),
    (up5, u5, 'toddler_pic.jpg', 2600000, 'image/jpeg', 2000),
    (up5, u5, 'kindergarten.jpg', 2100000, 'image/jpeg', 2002),
    (up5, u5, 'dance_recital.jpg', 3400000, 'image/jpeg', 2003),
    (up6, u6, 'baby_photo_1.jpg', 1800000, 'image/jpeg', 1985),
    (up6, u6, 'baby_photo_2.jpg', 2200000, 'image/jpeg', 1985),
    (up6, u6, 'baby_photo_3.jpg', 1900000, 'image/jpeg', 1986),
    (up6, u6, 'toddler_1.jpg', 2700000, 'image/jpeg', 1987),
    (up6, u6, 'toddler_2.jpg', 2400000, 'image/jpeg', 1987),
    (up6, u6, 'preschool.jpg', 3000000, 'image/jpeg', 1988);

  -- DMCA claims
  INSERT INTO dmca_claims (upload_id, claimant_name, claimant_email, reason, status, filed_at) VALUES
    (up1, 'John Smith Photography', 'john@smithphoto.com', 'Copyright infringement — claims to be the original photographer of these images', 'open', NOW() - INTERVAL '3 days'),
    (up3, 'Getty Images Legal', 'legal@gettyimages.com', 'Stock photo mistakenly uploaded as personal childhood photo', 'resolved', NOW() - INTERVAL '7 days'),
    (up5, 'Anonymous', NULL, 'Subject requests removal — disputes likeness consent was given', 'open', NOW() - INTERVAL '2 days');

  -- Some approved uploads (historical)
  FOR i IN 1..20 LOOP
    INSERT INTO uploads (user_id, status, copyright_relationship, copyright_permission, likeness_is_subject, likeness_is_adult, likeness_editorial, likeness_commercial, likeness_signature, likeness_signed_at, reviewed_at)
    VALUES (
      (ARRAY[u1, u2, u3, u5, u6])[1 + (i % 5)],
      'approved',
      (ARRAY['parent', 'family', 'professional'])[1 + (i % 3)],
      'yes', TRUE, TRUE, TRUE, TRUE,
      'Demo User ' || i,
      NOW() - (i || ' days')::INTERVAL,
      NOW() - ((i - 1) || ' days')::INTERVAL
    );
  END LOOP;

END $$;