BEGIN;

CREATE TABLE IF NOT EXISTS completed_sales (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  sale_type text NOT NULL CHECK (sale_type IN ('customer_research', 'dealer_membership')),
  provider text NOT NULL DEFAULT 'stripe',
  provider_event_id text NOT NULL,
  stripe_checkout_session_id text,
  stripe_invoice_id text,
  stripe_payment_intent_id text,
  stripe_customer_id text,
  stripe_subscription_id text,
  customer_request_id uuid REFERENCES customer_requests(id) ON DELETE SET NULL,
  dealership_id uuid REFERENCES dealerships(id) ON DELETE SET NULL,
  purchaser_name text,
  purchaser_email text,
  description text NOT NULL,
  membership_tier text CHECK (membership_tier IS NULL OR membership_tier IN ('bronze', 'silver', 'gold')),
  amount_total_cents integer NOT NULL CHECK (amount_total_cents >= 0),
  currency char(3) NOT NULL DEFAULT 'cad',
  completed_at timestamptz NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  UNIQUE (provider, provider_event_id)
);

CREATE UNIQUE INDEX IF NOT EXISTS completed_sales_checkout_unique
  ON completed_sales (stripe_checkout_session_id)
  WHERE stripe_checkout_session_id IS NOT NULL;

CREATE UNIQUE INDEX IF NOT EXISTS completed_sales_invoice_unique
  ON completed_sales (stripe_invoice_id)
  WHERE stripe_invoice_id IS NOT NULL;

CREATE INDEX IF NOT EXISTS completed_sales_completed_at_idx
  ON completed_sales (completed_at DESC);

CREATE INDEX IF NOT EXISTS completed_sales_type_idx
  ON completed_sales (sale_type, completed_at DESC);

-- Preserve paid customer records created before the sales ledger existed.
INSERT INTO completed_sales (
  sale_type, provider, provider_event_id, stripe_checkout_session_id, customer_request_id,
  purchaser_name, purchaser_email, description, amount_total_cents, currency, completed_at, metadata
)
SELECT 'customer_research', 'legacy_import', 'customer-request:' || request.id::text,
  request.stripe_checkout_session_id, request.id, request.customer_name, request.customer_email,
  'Vehicle price research', 5000, 'cad', request.created_at,
  jsonb_build_object('source', 'pre-ledger customer backfill')
FROM customer_requests request
WHERE request.payment_status = 'paid'
  AND NOT EXISTS (
    SELECT 1 FROM completed_sales sale
    WHERE sale.stripe_checkout_session_id = request.stripe_checkout_session_id
       OR sale.customer_request_id = request.id
  )
ON CONFLICT DO NOTHING;

-- Add the current paid membership period for dealerships enrolled before the ledger existed.
INSERT INTO completed_sales (
  sale_type, provider, provider_event_id, stripe_customer_id, stripe_subscription_id, dealership_id,
  purchaser_name, purchaser_email, description, membership_tier, amount_total_cents, currency,
  completed_at, metadata
)
SELECT 'dealer_membership', 'legacy_import', 'dealer-period:' || subscription.id::text || ':' || extract(epoch from subscription.current_period_start)::bigint::text,
  subscription.stripe_customer_id, subscription.stripe_subscription_id, subscription.dealership_id,
  dealership.display_name, owner.email,
  initcap(subscription.tier) || ' membership', subscription.tier, subscription.monthly_price_cents,
  subscription.currency, subscription.current_period_start,
  jsonb_build_object('source', 'pre-ledger current subscription period backfill')
FROM dealer_subscriptions subscription
JOIN dealerships dealership ON dealership.id = subscription.dealership_id
LEFT JOIN LATERAL (
  SELECT user_account.email FROM dealer_memberships membership
  JOIN dealer_users user_account ON user_account.id = membership.user_id
  WHERE membership.dealership_id = subscription.dealership_id
  ORDER BY CASE membership.role WHEN 'owner' THEN 1 ELSE 2 END LIMIT 1
) owner ON true
WHERE subscription.status = 'active'
  AND NOT EXISTS (
    SELECT 1 FROM completed_sales sale
    WHERE sale.stripe_subscription_id = subscription.stripe_subscription_id
      AND sale.completed_at = subscription.current_period_start
  )
ON CONFLICT DO NOTHING;

COMMIT;