Views¶

CREATE OR REPLACE VIEW public.pharmacy_subscription_status AS
SELECT
  s.id AS pharmacy_id,
  s.store_name,
  s.approval_status,
  ps.id AS subscription_id,
  ps.status AS subscription_status,
  ps.subscription_type,
  ps.subscription_start_date,
  ps.subscription_end_date,
  CASE
    WHEN ps.status = 'active' AND ps.subscription_end_date >= NOW() THEN true
    ELSE false
  END AS is_visible
FROM sellers s
LEFT JOIN LATERAL (
  SELECT * FROM pharmacy_subscriptions
  WHERE pharmacy_id = s.id
  ORDER BY created_at DESC
  LIMIT 1
) ps ON true
WHERE s.approval_status = 'approved';