BEGIN;

ALTER TABLE ordre_drafts
ADD COLUMN IF NOT EXISTS economic_last_checked_at TIMESTAMPTZ,
ADD COLUMN IF NOT EXISTS economic_document_state VARCHAR(30),
ADD COLUMN IF NOT EXISTS economic_payment_status VARCHAR(30),
ADD COLUMN IF NOT EXISTS economic_status_response JSONB,
ADD COLUMN IF NOT EXISTS economic_status_error TEXT;

CREATE TABLE IF NOT EXISTS economic_order_update_requests (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    draft_id INTEGER NOT NULL REFERENCES ordre_drafts(id) ON DELETE CASCADE,
    economic_order_number VARCHAR(80) NOT NULL,
    before_snapshot JSONB NOT NULL,
    proposed_payload JSONB NOT NULL,
    proposed_local_lines JSONB NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'pending' CHECK (status IN ('pending','applied','expired','failed')),
    requested_by_user_id INTEGER REFERENCES users(user_id) ON DELETE SET NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    expires_at TIMESTAMPTZ NOT NULL DEFAULT now() + interval '15 minutes',
    applied_at TIMESTAMPTZ,
    response JSONB,
    error TEXT
);

CREATE INDEX IF NOT EXISTS idx_economic_order_update_pending ON economic_order_update_requests(draft_id,status);
CREATE INDEX IF NOT EXISTS idx_ordre_economic_daily_check ON ordre_drafts(economic_last_checked_at) WHERE economic_order_number IS NOT NULL;

CREATE OR REPLACE FUNCTION protect_exported_ordre_lines() RETURNS trigger AS $$
BEGIN
 IF (NEW.lines_json IS DISTINCT FROM OLD.lines_json OR NEW.customer_id IS DISTINCT FROM OLD.customer_id)
 AND current_setting('bmc.economic_order_update', true) IS DISTINCT FROM OLD.id::text
 AND EXISTS (SELECT 1 FROM economic_document_exports WHERE document_key='order-draft:'||OLD.id::text AND status NOT IN ('rejected','archived')) THEN
  RAISE EXCEPTION 'Exported or uncertain document lines are immutable; use the approved e-conomic draft update flow';
 END IF;
 RETURN NEW;
END; $$ LANGUAGE plpgsql;

INSERT INTO permissions(code,description,category)
VALUES ('economic.orders.update','Forhåndsvis og godkend opdatering af en åben e-conomic-ordrekladde','economic')
ON CONFLICT (code) DO NOTHING;

INSERT INTO group_permissions(group_id,permission_id)
SELECT g.id,p.id FROM groups g CROSS JOIN permissions p
WHERE g.name='Administrators' AND p.code='economic.orders.update'
ON CONFLICT DO NOTHING;

COMMIT;