BEGIN;

ALTER TABLE dealerships ADD COLUMN IF NOT EXISTS quote_outside_inventory boolean NOT NULL DEFAULT false;
ALTER TABLE opportunity_assignments ADD COLUMN IF NOT EXISTS quota_released_at timestamptz;

CREATE TABLE IF NOT EXISTS dealership_manufacturers (
  dealership_id uuid NOT NULL REFERENCES dealerships(id) ON DELETE CASCADE,
  manufacturer text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (dealership_id, manufacturer)
);
CREATE INDEX IF NOT EXISTS dealership_manufacturers_make_idx ON dealership_manufacturers (lower(manufacturer));

ALTER TABLE opportunity_assignments DROP CONSTRAINT IF EXISTS opportunity_assignments_assignment_source_check;
ALTER TABLE opportunity_assignments ADD CONSTRAINT opportunity_assignments_assignment_source_check
  CHECK (assignment_source IN ('admin', 'dealer_selected', 'gold_automatic', 'manufacturer_automatic'));

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
    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
  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
  ) VALUES (
    p_opportunity_id, p_dealership_id, subscription.id, p_assignment_source,
    subscription.tier <> 'gold', subscription.current_period_start
  ) 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;
$$;

UPDATE opportunities opportunity SET status = 'closed', updated_at = now()
FROM customer_requests request
WHERE request.id = opportunity.customer_request_id AND request.status = 'delivered' AND opportunity.status = 'open';

DO $$
DECLARE opportunity_record record;
BEGIN
  FOR opportunity_record IN
    SELECT opportunity.id FROM opportunities opportunity
    JOIN customer_requests request ON request.id = opportunity.customer_request_id
    WHERE request.status = 'delivered'
  LOOP
    PERFORM release_opportunity_quota(opportunity_record.id);
  END LOOP;
END $$;

COMMIT;