BEGIN;

CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE dealerships (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  legal_name text NOT NULL,
  display_name text NOT NULL,
  slug text NOT NULL UNIQUE,
  omvic_registration_number text,
  website_url text,
  phone text,
  email text,
  address_line_1 text NOT NULL,
  address_line_2 text,
  city text NOT NULL,
  province_code text NOT NULL DEFAULT 'ON',
  postal_code text NOT NULL,
  country_code char(2) NOT NULL DEFAULT 'CA',
  logo_object_key text,
  profile_status text NOT NULL DEFAULT 'pending' CHECK (profile_status IN ('pending', 'active', 'suspended', 'archived')),
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE dealer_users (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  email text NOT NULL,
  full_name text NOT NULL,
  phone text,
  auth_status text NOT NULL DEFAULT 'invited' CHECK (auth_status IN ('invited', 'active', 'suspended')),
  last_signed_in_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX dealer_users_email_unique ON dealer_users (lower(email));

CREATE TABLE dealer_memberships (
  dealership_id uuid NOT NULL REFERENCES dealerships(id) ON DELETE CASCADE,
  user_id uuid NOT NULL REFERENCES dealer_users(id) ON DELETE CASCADE,
  role text NOT NULL DEFAULT 'member' CHECK (role IN ('owner', 'manager', 'member')),
  created_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (dealership_id, user_id)
);

CREATE TABLE dealer_sessions (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES dealer_users(id) ON DELETE CASCADE,
  token_hash text NOT NULL UNIQUE,
  expires_at timestamptz NOT NULL,
  revoked_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX dealer_sessions_user_active_idx ON dealer_sessions (user_id, expires_at) WHERE revoked_at IS NULL;

CREATE TABLE dealer_login_tokens (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid NOT NULL REFERENCES dealer_users(id) ON DELETE CASCADE,
  token_hash text NOT NULL UNIQUE,
  expires_at timestamptz NOT NULL,
  consumed_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE dealer_subscriptions (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  dealership_id uuid NOT NULL REFERENCES dealerships(id) ON DELETE CASCADE,
  tier text NOT NULL CHECK (tier IN ('bronze', 'silver', 'gold')),
  status text NOT NULL CHECK (status IN ('trialing', 'active', 'past_due', 'paused', 'cancelled', 'incomplete')),
  currency char(3) NOT NULL DEFAULT 'cad' CHECK (currency = 'cad'),
  monthly_price_cents integer NOT NULL CHECK (monthly_price_cents IN (9999, 19999, 29999)),
  stripe_customer_id text UNIQUE,
  stripe_subscription_id text UNIQUE,
  stripe_price_id text,
  current_period_start timestamptz NOT NULL,
  current_period_end timestamptz NOT NULL,
  opportunities_used integer NOT NULL DEFAULT 0 CHECK (opportunities_used >= 0),
  cancel_at_period_end boolean NOT NULL DEFAULT false,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  CHECK (current_period_end > current_period_start),
  CHECK (
    (tier = 'bronze' AND monthly_price_cents = 9999) OR
    (tier = 'silver' AND monthly_price_cents = 19999) OR
    (tier = 'gold' AND monthly_price_cents = 29999)
  )
);
CREATE UNIQUE INDEX dealer_subscriptions_one_current_idx
  ON dealer_subscriptions (dealership_id)
  WHERE status IN ('trialing', 'active', 'past_due', 'paused', 'incomplete');

CREATE TABLE customer_requests (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  public_reference text NOT NULL UNIQUE,
  customer_name text NOT NULL,
  customer_email text NOT NULL,
  customer_phone text,
  buyer_city text,
  buyer_region text,
  postal_code text,
  condition text NOT NULL,
  model_year smallint,
  make text NOT NULL,
  model text NOT NULL,
  trim text,
  exterior_color text,
  interior_color text,
  must_have_options text,
  maximum_mileage integer,
  target_budget_cents integer,
  flexibility text,
  customer_notes text,
  payment_status text NOT NULL DEFAULT 'pending' CHECK (payment_status IN ('pending', 'paid', 'refunded', 'failed')),
  stripe_checkout_session_id text UNIQUE,
  status text NOT NULL DEFAULT 'new' CHECK (status IN ('new', 'matching', 'collecting_offers', 'review_ready', 'approved', 'delivered', 'cancelled')),
  due_at timestamptz,
  delivered_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX customer_requests_queue_idx ON customer_requests (status, due_at, created_at);
CREATE INDEX customer_requests_email_idx ON customer_requests (lower(customer_email));

CREATE TABLE opportunities (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  customer_request_id uuid NOT NULL UNIQUE REFERENCES customer_requests(id) ON DELETE CASCADE,
  condition text NOT NULL,
  model_year smallint,
  make text NOT NULL,
  model text NOT NULL,
  trim text,
  exterior_color text,
  interior_color text,
  must_have_options text,
  maximum_mileage integer,
  buyer_market text,
  response_deadline timestamptz NOT NULL,
  status text NOT NULL DEFAULT 'open' CHECK (status IN ('draft', 'open', 'closed', 'cancelled')),
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX opportunities_match_idx ON opportunities (status, make, model, trim, model_year, response_deadline);

CREATE TABLE opportunity_assignments (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  opportunity_id uuid NOT NULL REFERENCES opportunities(id) ON DELETE CASCADE,
  dealership_id uuid NOT NULL REFERENCES dealerships(id) ON DELETE CASCADE,
  subscription_id uuid NOT NULL REFERENCES dealer_subscriptions(id),
  assignment_source text NOT NULL CHECK (assignment_source IN ('admin', 'dealer_selected', 'gold_automatic')),
  status text NOT NULL DEFAULT 'invited' CHECK (status IN ('invited', 'viewed', 'responded', 'declined', 'expired')),
  counted_against_quota boolean NOT NULL DEFAULT true,
  billing_period_start timestamptz NOT NULL,
  assigned_at timestamptz NOT NULL DEFAULT now(),
  first_viewed_at timestamptz,
  responded_at timestamptz,
  UNIQUE (opportunity_id, dealership_id)
);
CREATE INDEX opportunity_assignments_dealer_idx ON opportunity_assignments (dealership_id, status, assigned_at DESC);

CREATE TABLE inventory_imports (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  dealership_id uuid NOT NULL REFERENCES dealerships(id) ON DELETE CASCADE,
  uploaded_by uuid REFERENCES dealer_users(id) ON DELETE SET NULL,
  source_filename text NOT NULL,
  source_object_key text,
  status text NOT NULL DEFAULT 'processing' CHECK (status IN ('processing', 'completed', 'failed')),
  row_count integer NOT NULL DEFAULT 0,
  accepted_count integer NOT NULL DEFAULT 0,
  rejected_count integer NOT NULL DEFAULT 0,
  validation_errors jsonb NOT NULL DEFAULT '[]'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  completed_at timestamptz
);

CREATE TABLE inventory_prices (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  dealership_id uuid NOT NULL REFERENCES dealerships(id) ON DELETE CASCADE,
  inventory_import_id uuid REFERENCES inventory_imports(id) ON DELETE SET NULL,
  stock_number text,
  vin text,
  condition text NOT NULL,
  model_year smallint,
  make text NOT NULL,
  model text NOT NULL,
  trim text,
  exterior_color text,
  interior_color text,
  options_text text,
  vehicle_price_cents integer NOT NULL CHECK (vehicle_price_cents >= 0),
  mandatory_fees_cents integer NOT NULL DEFAULT 0 CHECK (mandatory_fees_cents >= 0),
  unconditional_discount_cents integer NOT NULL DEFAULT 0 CHECK (unconditional_discount_cents >= 0),
  comparable_price_cents integer GENERATED ALWAYS AS (vehicle_price_cents + mandatory_fees_cents - unconditional_discount_cents) STORED,
  quantity_available integer NOT NULL DEFAULT 1 CHECK (quantity_available >= 0),
  valid_from timestamptz NOT NULL DEFAULT now(),
  valid_until timestamptz NOT NULL,
  active boolean NOT NULL DEFAULT true,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  CHECK (vehicle_price_cents + mandatory_fees_cents >= unconditional_discount_cents),
  CHECK (valid_until > valid_from)
);
CREATE INDEX inventory_prices_match_idx
  ON inventory_prices (dealership_id, active, make, model, trim, model_year, exterior_color, valid_until);
CREATE UNIQUE INDEX inventory_prices_dealer_stock_unique
  ON inventory_prices (dealership_id, stock_number)
  WHERE stock_number IS NOT NULL;

CREATE TABLE dealer_offers (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  opportunity_assignment_id uuid NOT NULL REFERENCES opportunity_assignments(id) ON DELETE CASCADE,
  inventory_price_id uuid REFERENCES inventory_prices(id) ON DELETE SET NULL,
  submitted_by uuid REFERENCES dealer_users(id) ON DELETE SET NULL,
  status text NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'submitted', 'withdrawn', 'expired', 'disqualified')),
  stock_number text,
  vin text,
  vehicle_price_cents integer NOT NULL CHECK (vehicle_price_cents >= 0),
  mandatory_fees_cents integer NOT NULL DEFAULT 0 CHECK (mandatory_fees_cents >= 0),
  unconditional_discount_cents integer NOT NULL DEFAULT 0 CHECK (unconditional_discount_cents >= 0),
  comparable_price_cents integer GENERATED ALWAYS AS (vehicle_price_cents + mandatory_fees_cents - unconditional_discount_cents) STORED,
  taxes_included boolean NOT NULL DEFAULT false,
  availability_confirmed boolean NOT NULL DEFAULT false,
  conditions_text text,
  valid_until timestamptz NOT NULL,
  submitted_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  CHECK (vehicle_price_cents + mandatory_fees_cents >= unconditional_discount_cents)
);
CREATE INDEX dealer_offers_rank_idx ON dealer_offers (opportunity_assignment_id, status, comparable_price_cents, submitted_at);

CREATE TABLE report_versions (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  customer_request_id uuid NOT NULL REFERENCES customer_requests(id) ON DELETE CASCADE,
  version_number integer NOT NULL CHECK (version_number > 0),
  status text NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'review_ready', 'approved', 'rejected', 'sent', 'superseded')),
  personalized_recommendation text,
  rendered_pdf_object_key text,
  content_snapshot jsonb NOT NULL DEFAULT '{}'::jsonb,
  prepared_by text,
  submitted_for_review_at timestamptz,
  approved_by text,
  approved_at timestamptz,
  rejected_by text,
  rejected_at timestamptz,
  rejection_reason text,
  sent_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (customer_request_id, version_number),
  CHECK ((status NOT IN ('approved', 'sent')) OR (approved_by IS NOT NULL AND approved_at IS NOT NULL)),
  CHECK ((status <> 'sent') OR sent_at IS NOT NULL)
);

CREATE TABLE report_selected_offers (
  report_version_id uuid NOT NULL REFERENCES report_versions(id) ON DELETE CASCADE,
  dealer_offer_id uuid NOT NULL REFERENCES dealer_offers(id),
  rank smallint NOT NULL CHECK (rank BETWEEN 1 AND 3),
  comparable_price_cents_snapshot integer NOT NULL CHECK (comparable_price_cents_snapshot >= 0),
  PRIMARY KEY (report_version_id, rank),
  UNIQUE (report_version_id, dealer_offer_id)
);

CREATE TABLE email_deliveries (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  report_version_id uuid REFERENCES report_versions(id) ON DELETE SET NULL,
  recipient_email text NOT NULL,
  provider text NOT NULL,
  provider_message_id text,
  idempotency_key text NOT NULL UNIQUE,
  status text NOT NULL DEFAULT 'queued' CHECK (status IN ('queued', 'accepted', 'delivered', 'bounced', 'complained', 'failed')),
  last_error text,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE webhook_events (
  provider text NOT NULL,
  provider_event_id text NOT NULL,
  event_type text NOT NULL,
  payload jsonb NOT NULL,
  processing_status text NOT NULL DEFAULT 'pending' CHECK (processing_status IN ('pending', 'processing', 'processed', 'failed')),
  attempts integer NOT NULL DEFAULT 0,
  received_at timestamptz NOT NULL DEFAULT now(),
  processed_at timestamptz,
  last_error text,
  PRIMARY KEY (provider, provider_event_id)
);

CREATE TABLE background_jobs (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  job_type text NOT NULL,
  payload jsonb NOT NULL,
  idempotency_key text NOT NULL UNIQUE,
  status text NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'processing', 'completed', 'failed', 'dead_letter')),
  priority smallint NOT NULL DEFAULT 100,
  attempts integer NOT NULL DEFAULT 0,
  max_attempts integer NOT NULL DEFAULT 8,
  available_at timestamptz NOT NULL DEFAULT now(),
  locked_at timestamptz,
  locked_by text,
  last_error text,
  created_at timestamptz NOT NULL DEFAULT now(),
  completed_at timestamptz
);
CREATE INDEX background_jobs_claim_idx ON background_jobs (priority, available_at, created_at) WHERE status = 'pending';

CREATE TABLE audit_events (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  actor_type text NOT NULL CHECK (actor_type IN ('admin', 'dealer_user', 'system')),
  actor_id text,
  dealership_id uuid REFERENCES dealerships(id) ON DELETE SET NULL,
  action text NOT NULL,
  entity_type text NOT NULL,
  entity_id text NOT NULL,
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  ip_address inet,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX audit_events_entity_idx ON audit_events (entity_type, entity_id, created_at DESC);
CREATE INDEX audit_events_dealership_idx ON audit_events (dealership_id, created_at DESC);

CREATE VIEW dealer_opportunity_view AS
SELECT
  oa.id AS assignment_id,
  oa.dealership_id,
  oa.status AS assignment_status,
  oa.assigned_at,
  o.id AS opportunity_id,
  o.condition,
  o.model_year,
  o.make,
  o.model,
  o.trim,
  o.exterior_color,
  o.interior_color,
  o.must_have_options,
  o.maximum_mileage,
  o.buyer_market,
  o.response_deadline,
  o.status AS opportunity_status
FROM opportunity_assignments oa
JOIN opportunities o ON o.id = oa.opportunity_id;

CREATE VIEW ranked_submitted_offers AS
WITH dealer_best AS (
  SELECT
    offer.*,
    assignment.opportunity_id,
    assignment.dealership_id,
    row_number() OVER (
      PARTITION BY assignment.opportunity_id, assignment.dealership_id
      ORDER BY offer.comparable_price_cents ASC, offer.submitted_at ASC, offer.id ASC
    ) AS dealer_offer_rank
  FROM dealer_offers offer
  JOIN opportunity_assignments assignment ON assignment.id = offer.opportunity_assignment_id
  WHERE offer.status = 'submitted'
    AND offer.availability_confirmed = true
    AND offer.valid_until >= now()
), ranked AS (
  SELECT
    dealer_best.*,
    row_number() OVER (
      PARTITION BY dealer_best.opportunity_id
      ORDER BY dealer_best.comparable_price_cents ASC, dealer_best.submitted_at ASC, dealer_best.id ASC
    ) AS price_rank
  FROM dealer_best
  WHERE dealer_offer_rank = 1
)
SELECT * FROM ranked WHERE price_rank <= 3;

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
  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 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;
$$;

CREATE OR REPLACE FUNCTION prevent_unapproved_report_send()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  IF NEW.status = 'sent' AND (OLD.status <> 'approved' OR OLD.approved_at IS NULL OR OLD.approved_by IS NULL) THEN
    RAISE EXCEPTION 'A report must be approved before it can be sent';
  END IF;
  RETURN NEW;
END;
$$;

CREATE TRIGGER report_send_requires_approval
BEFORE UPDATE OF status ON report_versions
FOR EACH ROW EXECUTE FUNCTION prevent_unapproved_report_send();

COMMIT;