backend/database/seeds/003_comments_and_expert_reviews.sql

-- Sample comments and expert reviews for product 4
-- Resolves business_id and approver from the product / business owner automatically.
-- Requires migrations 012_comments.sql and 013_expert_reviews.sql

WITH product_ctx AS (
  SELECT
    p.id AS product_id,
    p.business_id,
    owner.user_id AS owner_user_id
  FROM products p
  JOIN business_users owner
    ON owner.business_id = p.business_id
   AND owner.is_owner = TRUE
  WHERE p.id = 4
),
comment_rows AS (
  SELECT *
  FROM (VALUES
    (
      1::bigint,
      'Mina Salehi'::varchar,
      'mina@customer.demo'::varchar,
      'Camera quality is outstanding, especially in low light. Very happy with the upgrade.'::text,
      TRUE,
      NOW() - INTERVAL '2 days',
      NOW() - INTERVAL '3 days'
    ),
    (
      2,
      'Arash Mohammadi',
      'arash@example.com',
      'Smooth performance and the display looks fantastic. Battery could last a bit longer though.',
      TRUE,
      NOW() - INTERVAL '1 day',
      NOW() - INTERVAL '2 days'
    ),
    (
      3,
      'Leila Karimi',
      'leila@example.com',
      'Premium build and fast delivery from Sanihome. Setup was seamless.',
      TRUE,
      NOW() - INTERVAL '5 hours',
      NOW() - INTERVAL '1 day'
    ),
    (
      4,
      'Hossein Rahimi',
      'hossein@example.com',
      'Just placed my order — excited to try the new Pro model.',
      FALSE,
      NULL::timestamptz,
      NOW() - INTERVAL '3 hours'
    ),
    (
      5,
      'Nazanin Azizi',
      NULL,
      'Does this model support dual SIM for Iran?',
      FALSE,
      NULL::timestamptz,
      NOW() - INTERVAL '1 hour'
    )
  ) AS rows(
    id,
    author_name,
    author_email,
    text,
    is_approved,
    approved_at,
    created_at
  )
)
INSERT INTO comments (
  id, business_id, entity_type, entity_id, author_name, author_email, text,
  is_approved, approved_at, approved_by, created_at
)
SELECT
  r.id,
  ctx.business_id,
  'product'::media_entity_type,
  ctx.product_id,
  r.author_name,
  r.author_email,
  r.text,
  r.is_approved,
  CASE WHEN r.is_approved THEN r.approved_at ELSE NULL END,
  CASE WHEN r.is_approved THEN ctx.owner_user_id ELSE NULL END,
  r.created_at
FROM comment_rows r
CROSS JOIN product_ctx ctx
ON CONFLICT (id) DO NOTHING;

WITH product_ctx AS (
  SELECT
    p.id AS product_id,
    p.business_id,
    owner.user_id AS owner_user_id
  FROM products p
  JOIN business_users owner
    ON owner.business_id = p.business_id
   AND owner.is_owner = TRUE
  WHERE p.id = 4
),
review_rows AS (
  SELECT *
  FROM (VALUES
    (
      1::bigint,
      'MobileTech Review'::varchar,
      'reviews@mobiletech.demo'::varchar,
      9::smallint,
      ARRAY['Excellent camera system', 'Top-tier performance', 'Premium display', 'Strong build quality']::text[],
      ARRAY['High price point', 'No charger in box']::text[],
      'The iPhone 17 Pro remains a benchmark flagship. Photo and video capabilities are class-leading, and day-to-day performance is flawless for power users.'::text,
      TRUE,
      NOW() - INTERVAL '4 days',
      NOW() - INTERVAL '5 days'
    ),
    (
      2,
      'Gadget Iran',
      'editor@gadgetiran.demo',
      8,
      ARRAY['Bright ProMotion display', 'Reliable iOS updates', 'Great video stabilization'],
      ARRAY['Heavy for one-handed use', 'Storage upgrades are expensive'],
      'A compelling Pro model for creators and professionals. The camera and display are the main reasons to choose it over the standard line.',
      TRUE,
      NOW() - INTERVAL '2 days',
      NOW() - INTERVAL '3 days'
    ),
    (
      3,
      'PhoneLab',
      'lab@phonelab.demo',
      7,
      ARRAY['Fast A-series chip', 'Solid battery for its class', 'Excellent ecosystem integration'],
      ARRAY['Incremental design changes', 'Pro price without major leaps for casual users'],
      'A polished flagship that makes sense for Apple loyalists and mobile photographers, though casual upgraders may find better value elsewhere.',
      FALSE,
      NULL::timestamptz,
      NOW() - INTERVAL '6 hours'
    )
  ) AS rows(
    id,
    author_name,
    author_email,
    rate,
    positive_points,
    negative_points,
    text,
    is_approved,
    approved_at,
    created_at
  )
)
INSERT INTO expert_reviews (
  id, business_id, product_id, author_name, author_email, rate,
  positive_points, negative_points, text,
  is_approved, approved_at, approved_by, created_at
)
SELECT
  r.id,
  ctx.business_id,
  ctx.product_id,
  r.author_name,
  r.author_email,
  r.rate,
  r.positive_points,
  r.negative_points,
  r.text,
  r.is_approved,
  CASE WHEN r.is_approved THEN r.approved_at ELSE NULL END,
  CASE WHEN r.is_approved THEN ctx.owner_user_id ELSE NULL END,
  r.created_at
FROM review_rows r
CROSS JOIN product_ctx ctx
ON CONFLICT (id) DO NOTHING;

SELECT setval(pg_get_serial_sequence('comments', 'id'),        COALESCE((SELECT MAX(id) FROM comments), 1));
SELECT setval(pg_get_serial_sequence('expert_reviews', 'id'), COALESCE((SELECT MAX(id) FROM expert_reviews), 1));