BEGIN;

ALTER TABLE opportunity_assignments ADD COLUMN IF NOT EXISTS dealer_response_deadline timestamptz;

CREATE OR REPLACE FUNCTION next_weekday_10pm_toronto(p_from timestamptz)
RETURNS timestamptz
LANGUAGE plpgsql
STABLE
AS $$
DECLARE
  candidate date := (p_from AT TIME ZONE 'America/Toronto')::date + 1;
BEGIN
  WHILE extract(isodow from candidate) IN (6, 7) LOOP
    candidate := candidate + 1;
  END LOOP;
  RETURN (candidate + time '22:00') AT TIME ZONE 'America/Toronto';
END;
$$;

UPDATE opportunity_assignments
SET dealer_response_deadline = next_weekday_10pm_toronto(assigned_at)
WHERE dealer_response_deadline IS NULL;

ALTER TABLE opportunity_assignments ALTER COLUMN dealer_response_deadline SET NOT NULL;
CREATE INDEX IF NOT EXISTS opportunity_assignments_deadline_idx
  ON opportunity_assignments (dealer_response_deadline, status)
  WHERE status IN ('invited', 'viewed');

CREATE OR REPLACE FUNCTION expire_dealer_assignments()
RETURNS integer
LANGUAGE plpgsql
AS $$
DECLARE expired_count integer;
BEGIN
  UPDATE opportunity_assignments assignment
  SET status = 'expired'
  WHERE assignment.status IN ('invited', 'viewed')
    AND assignment.dealer_response_deadline < now()
    AND NOT EXISTS (
      SELECT 1 FROM dealer_offers offer
      WHERE offer.opportunity_assignment_id = assignment.id AND offer.status = 'submitted'
    );
  GET DIAGNOSTICS expired_count = ROW_COUNT;
  RETURN expired_count;
END;
$$;

CREATE OR REPLACE FUNCTION release_opportunity_quota(p_opportunity_id uuid)
RETURNS integer
LANGUAGE plpgsql
AS $$
DECLARE released_count integer;
BEGIN
  WITH released AS (
    UPDATE opportunity_assignments
    SET counted_against_quota = false,
        quota_released_at = now(),
        status = CASE WHEN status IN ('invited', 'viewed') THEN 'expired' ELSE status END
    WHERE opportunity_id = p_opportunity_id
      AND counted_against_quota = true
      AND quota_released_at IS NULL
      AND status <> 'expired'
    RETURNING subscription_id
  ), totals AS (
    SELECT subscription_id, count(*)::integer AS released
    FROM released GROUP BY subscription_id
  )
  UPDATE dealer_subscriptions subscription
  SET opportunities_used = greatest(0, subscription.opportunities_used - totals.released), updated_at = now()
  FROM totals WHERE subscription.id = totals.subscription_id;
  GET DIAGNOSTICS released_count = ROW_COUNT;
  RETURN released_count;
END;
$$;

CREATE OR REPLACE FUNCTION assign_dealer_opportunity(
  p_opportunity_id uuid,
  p_dealership_id uuid,
  p_assignment_source text
) RETURNS opportunity_assignments
LANGUAGE plpgsql
AS $$
DECLARE
  subscription dealer_subscriptions%ROWTYPE;
  existing_assignment opportunity_assignments%ROWTYPE;
  new_assignment opportunity_assignments%ROWTYPE;
  tier_limit integer;
BEGIN
  PERFORM expire_dealer_assignments();
  IF NOT EXISTS (SELECT 1 FROM opportunities WHERE id = p_opportunity_id AND status = 'open' AND response_deadline >= now()) THEN
    RAISE EXCEPTION 'Opportunity is closed or no longer accepting prices';
  END IF;
  SELECT * INTO existing_assignment FROM opportunity_assignments
  WHERE opportunity_id = p_opportunity_id AND dealership_id = p_dealership_id;
  IF FOUND THEN RETURN existing_assignment; END IF;
  SELECT * INTO subscription FROM dealer_subscriptions
  WHERE dealership_id = p_dealership_id AND status IN ('active', 'trialing')
    AND now() >= current_period_start AND now() < current_period_end FOR UPDATE;
  IF NOT FOUND THEN RAISE EXCEPTION 'No active subscription for dealership %', p_dealership_id; END IF;
  SELECT * INTO existing_assignment FROM opportunity_assignments
  WHERE opportunity_id = p_opportunity_id AND dealership_id = p_dealership_id;
  IF FOUND THEN RETURN existing_assignment; END IF;
  tier_limit := CASE subscription.tier WHEN 'bronze' THEN 5 WHEN 'silver' THEN 10 ELSE NULL END;
  IF tier_limit IS NOT NULL AND subscription.opportunities_used >= tier_limit THEN
    RAISE EXCEPTION 'Opportunity allowance exhausted for dealership %', p_dealership_id;
  END IF;
  IF p_assignment_source = 'gold_automatic' AND subscription.tier <> 'gold' THEN
    RAISE EXCEPTION 'Automatic Gold matching requires a Gold subscription';
  END IF;
  INSERT INTO opportunity_assignments (
    opportunity_id, dealership_id, subscription_id, assignment_source,
    counted_against_quota, billing_period_start, dealer_response_deadline
  ) VALUES (
    p_opportunity_id, p_dealership_id, subscription.id, p_assignment_source,
    subscription.tier <> 'gold', subscription.current_period_start, next_weekday_10pm_toronto(now())
  ) RETURNING * INTO new_assignment;
  IF subscription.tier <> 'gold' THEN
    UPDATE dealer_subscriptions SET opportunities_used = opportunities_used + 1, updated_at = now()
    WHERE id = subscription.id;
  END IF;
  RETURN new_assignment;
END;
$$;

DROP VIEW IF EXISTS dealer_opportunity_view;
CREATE VIEW dealer_opportunity_view AS
SELECT
  oa.id AS assignment_id,
  oa.dealership_id,
  oa.status AS assignment_status,
  oa.assigned_at,
  oa.dealer_response_deadline AS response_deadline,
  o.id AS opportunity_id,
  o.condition,
  o.model_year,
  o.make,
  o.model,
  o.trim,
  o.exterior_color,
  o.exterior_color_flexible,
  o.interior_color,
  o.must_have_options,
  o.maximum_mileage,
  o.buyer_market,
  o.response_deadline AS customer_response_deadline,
  o.status AS opportunity_status,
  o.min_model_year,
  o.max_model_year,
  o.minimum_mileage,
  o.minimum_budget_cents,
  o.maximum_budget_cents
FROM opportunity_assignments oa
JOIN opportunities o ON o.id = oa.opportunity_id;

COMMIT;