-- 086: State motor carrier fulfillment status machine.
--
-- State trucking filings (IRP, IFTA, NY HUT, CT HUF, weight-distance, CA MCP,
-- etc.) have a multi-step fulfillment lifecycle that goes beyond payment: we
-- must collect the customer's signed authorization, possibly get them to add us
-- as a delegate inside the state portal (or collect credentials), get approval
-- for variable government fees, and then file and wait on the state.
--
-- This adds a dedicated fulfillment_status column to compliance_orders so ops
-- and the customer portal can see exactly where a state filing is, independent
-- of payment_status. Non-state services simply leave it NULL.
--
-- Status lifecycle (see docs/trucking-state-authorization-plan.md):
--   authorization_required          -> we emailed the customer the signing link
--   authorization_signed            -> customer signed the Limited Authorization
--   awaiting_customer_delegation    -> customer must add us as portal user/delegate
--   awaiting_secure_credentials     -> delegation unsupported; collecting credentials
--   awaiting_government_fee_approval -> variable state fee quoted, awaiting OK to pay
--   awaiting_insurance_filing       -> waiting on insurer to file proof (e.g. intrastate)
--   ready_to_file                   -> all blockers cleared; queued for filing
--   filed_waiting_state             -> submitted; waiting on the state to issue
--   completed                       -> credentials/decals/account delivered

ALTER TABLE compliance_orders
    ADD COLUMN IF NOT EXISTS fulfillment_status TEXT
        CHECK (fulfillment_status IS NULL OR fulfillment_status IN (
            'authorization_required',
            'authorization_signed',
            'awaiting_customer_delegation',
            'awaiting_secure_credentials',
            'awaiting_government_fee_approval',
            'awaiting_insurance_filing',
            'ready_to_file',
            'filed_waiting_state',
            'completed'
        )),
    ADD COLUMN IF NOT EXISTS fulfillment_status_at TIMESTAMPTZ;

CREATE INDEX IF NOT EXISTS idx_compliance_orders_fulfillment
    ON compliance_orders(fulfillment_status)
    WHERE fulfillment_status IS NOT NULL;

COMMENT ON COLUMN compliance_orders.fulfillment_status IS
    'State motor carrier filing lifecycle (NULL for services without a portal/authorization step). See migration 086.';
COMMENT ON COLUMN compliance_orders.fulfillment_status_at IS
    'When fulfillment_status last changed.';