27 lines
3.5 KiB
PL/PgSQL
27 lines
3.5 KiB
PL/PgSQL
CREATE TABLE cq_versions(
|
|
id bigserial PRIMARY KEY,tenant_id bigint NOT NULL,company_id bigint NOT NULL,unit_id bigint NOT NULL,
|
|
quote_key uuid NOT NULL,version int NOT NULL CHECK(version>0),previous_id bigint UNIQUE REFERENCES cq_versions(id),
|
|
action text NOT NULL CHECK(action IN('prepare','review','customer','convert')),state text NOT NULL CHECK(state IN('draft','approved','rejected','customer_accepted','customer_declined','converted')),
|
|
client_id bigint NOT NULL REFERENCES a_clients(id),source_order_id bigint REFERENCES wo_orders(id),prepared_by bigint NOT NULL REFERENCES users(id),actor_id bigint NOT NULL REFERENCES users(id),
|
|
data jsonb NOT NULL,dependencies jsonb NOT NULL,reason text NOT NULL CHECK(length(reason)>=10),created_at timestamptz NOT NULL DEFAULT now(),
|
|
UNIQUE(tenant_id,company_id,unit_id,quote_key,version),FOREIGN KEY(tenant_id,company_id,unit_id) REFERENCES units(tenant_id,company_id,id)
|
|
);
|
|
CREATE TABLE cq_commands(id bigserial PRIMARY KEY,tenant_id bigint NOT NULL,company_id bigint NOT NULL,unit_id bigint NOT NULL,actor_id bigint NOT NULL REFERENCES users(id),command_key uuid NOT NULL,request_hash char(64) NOT NULL,result jsonb NOT NULL,created_at timestamptz NOT NULL DEFAULT now(),UNIQUE(tenant_id,company_id,unit_id,actor_id,command_key));
|
|
CREATE TABLE cq_conversions(id bigserial PRIMARY KEY,tenant_id bigint NOT NULL,company_id bigint NOT NULL,unit_id bigint NOT NULL,quote_key uuid NOT NULL,quote_version_id bigint NOT NULL UNIQUE REFERENCES cq_versions(id),service_draft_id bigint NOT NULL UNIQUE REFERENCES a_service_drafts(id),billing_draft_id bigint NOT NULL UNIQUE REFERENCES a_billing_drafts(id),source_order_id bigint UNIQUE REFERENCES wo_orders(id),actor_id bigint NOT NULL REFERENCES users(id),created_at timestamptz NOT NULL DEFAULT now(),UNIQUE(tenant_id,company_id,unit_id,quote_key));
|
|
CREATE FUNCTION sgf_cq_guard() RETURNS trigger LANGUAGE plpgsql AS $$
|
|
DECLARE p cq_versions; right_name text;
|
|
BEGIN
|
|
PERFORM sgf_wo_environment();PERFORM 1 FROM companies WHERE id=NEW.company_id FOR UPDATE;
|
|
right_name:=CASE NEW.action WHEN 'review' THEN 'commercial_quotes.review' WHEN 'convert' THEN 'commercial_quotes.convert' ELSE 'commercial_quotes.prepare' END;
|
|
IF NOT EXISTS(SELECT 1 FROM permission_grants WHERE user_id=NEW.actor_id AND tenant_id=NEW.tenant_id AND company_id=NEW.company_id AND unit_id=NEW.unit_id AND permission=right_name)
|
|
OR NOT EXISTS(SELECT 1 FROM a_clients WHERE id=NEW.client_id AND tenant_id=NEW.tenant_id AND company_id=NEW.company_id)
|
|
THEN RAISE EXCEPTION 'SGF_CQ_AUTHORITY_OR_SCOPE'; END IF;
|
|
SELECT * INTO p FROM cq_versions WHERE (tenant_id,company_id,unit_id,quote_key)=(NEW.tenant_id,NEW.company_id,NEW.unit_id,NEW.quote_key) ORDER BY version DESC LIMIT 1;
|
|
IF (p.id IS NULL AND (NEW.version<>1 OR NEW.previous_id IS NOT NULL OR NEW.action<>'prepare')) OR (p.id IS NOT NULL AND (NEW.previous_id IS DISTINCT FROM p.id OR NEW.version<>p.version+1 OR p.state='converted')) THEN RAISE EXCEPTION 'SGF_CQ_STALE_OR_CONVERTED'; END IF;
|
|
IF NEW.action='review' AND (p.state<>'draft' OR NEW.actor_id=NEW.prepared_by) THEN RAISE EXCEPTION 'SGF_CQ_SELF_OR_STALE_REVIEW'; END IF;
|
|
IF NEW.action='customer' AND p.state<>'approved' THEN RAISE EXCEPTION 'SGF_CQ_CUSTOMER_BEFORE_REVIEW'; END IF;
|
|
IF NEW.action='convert' AND (p.state<>'customer_accepted' OR (NEW.data->>'valid_until')::date<(now() AT TIME ZONE 'Africa/Maputo')::date) THEN RAISE EXCEPTION 'SGF_CQ_NOT_CONVERTIBLE'; END IF;
|
|
RETURN NEW;
|
|
END; $$;
|
|
CREATE TRIGGER sgf_cq_guard BEFORE INSERT ON cq_versions FOR EACH ROW EXECUTE FUNCTION sgf_cq_guard();
|