49 lines
2.3 KiB
MySQL
49 lines
2.3 KiB
MySQL
|
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;
|