from django.db import migrations


SQL = r"""
CREATE FUNCTION inventory_request_guard() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
 IF TG_OP = 'DELETE' THEN RAISE EXCEPTION 'Request history is retained' USING ERRCODE='23514'; END IF;
 IF NEW.company_id IS DISTINCT FROM (SELECT company_id FROM service_servicecase WHERE id=NEW.service_case_id)
 THEN RAISE EXCEPTION 'Request case company mismatch' USING ERRCODE='23514'; END IF;
 IF TG_OP = 'UPDATE' THEN
  IF ROW(NEW.company_id,NEW.service_case_id,NEW.requester_id,NEW.requested_at,NEW.note)
    IS DISTINCT FROM ROW(OLD.company_id,OLD.service_case_id,OLD.requester_id,OLD.requested_at,OLD.note)
   OR OLD.status IN ('CANCELLED','REJECTED','FULFILLED')
   OR (OLD.status='APPROVED' AND NEW.status NOT IN ('APPROVED','CANCELLED','FULFILLED'))
   OR (OLD.status='REQUESTED' AND NEW.status NOT IN ('REQUESTED','APPROVED','CANCELLED','REJECTED'))
  THEN RAISE EXCEPTION 'Request history is immutable' USING ERRCODE='23514'; END IF;
 END IF;
 RETURN NEW;
END $$;
CREATE TRIGGER inventory_request_guard BEFORE INSERT OR UPDATE OR DELETE ON inventory_partsrequest
 FOR EACH ROW EXECUTE FUNCTION inventory_request_guard();

CREATE FUNCTION inventory_reservation_guard() RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE req inventory_partsrequest; ln inventory_partsrequestline;
BEGIN
 IF TG_OP='DELETE' THEN RAISE EXCEPTION 'Reservation history is retained' USING ERRCODE='23514'; END IF;
 SELECT * INTO ln FROM inventory_partsrequestline WHERE id=NEW.line_id;
 SELECT * INTO req FROM inventory_partsrequest WHERE id=ln.request_id;
 IF NEW.company_id IS DISTINCT FROM req.company_id OR NEW.service_case_id IS DISTINCT FROM req.service_case_id
  OR NEW.spare_part_id IS DISTINCT FROM ln.spare_part_id
  OR NEW.company_id IS DISTINCT FROM (SELECT company_id FROM inventory_inventorylocation WHERE id=NEW.location_id)
 THEN RAISE EXCEPTION 'Reservation ownership mismatch' USING ERRCODE='23514'; END IF;
 IF TG_OP='UPDATE' AND (OLD.status <> 'ACTIVE' OR NEW.status NOT IN ('RELEASED','ISSUED')
  OR ROW(NEW.company_id,NEW.service_case_id,NEW.line_id,NEW.spare_part_id,NEW.location_id,NEW.quantity,NEW.reserved_by_id,NEW.reserved_at)
  IS DISTINCT FROM ROW(OLD.company_id,OLD.service_case_id,OLD.line_id,OLD.spare_part_id,OLD.location_id,OLD.quantity,OLD.reserved_by_id,OLD.reserved_at))
 THEN RAISE EXCEPTION 'Reservation identity/history immutable' USING ERRCODE='23514'; END IF;
 RETURN NEW;
END $$;
CREATE TRIGGER inventory_reservation_guard BEFORE INSERT OR UPDATE OR DELETE ON inventory_stockreservation
 FOR EACH ROW EXECUTE FUNCTION inventory_reservation_guard();

CREATE FUNCTION inventory_reservation_unit_guard() RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE res inventory_stockreservation; unt inventory_serializedstockunit;
BEGIN
 IF TG_OP='DELETE' THEN RAISE EXCEPTION 'Unit reservation history retained' USING ERRCODE='23514'; END IF;
 SELECT * INTO res FROM inventory_stockreservation WHERE id=NEW.reservation_id;
 SELECT * INTO unt FROM inventory_serializedstockunit WHERE id=NEW.unit_id;
 IF res.company_id IS DISTINCT FROM unt.company_id OR res.spare_part_id IS DISTINCT FROM unt.spare_part_id
 THEN RAISE EXCEPTION 'Reserved unit ownership mismatch' USING ERRCODE='23514'; END IF;
 IF TG_OP='UPDATE' AND (NEW.reservation_id<>OLD.reservation_id OR NEW.unit_id<>OLD.unit_id OR NOT OLD.is_active OR NEW.is_active)
 THEN RAISE EXCEPTION 'Unit reservation history immutable' USING ERRCODE='23514'; END IF;
 RETURN NEW;
END $$;
CREATE TRIGGER inventory_reservation_unit_guard BEFORE INSERT OR UPDATE OR DELETE ON inventory_reservationunit
 FOR EACH ROW EXECUTE FUNCTION inventory_reservation_unit_guard();

CREATE TRIGGER inventory_request_line_immutable BEFORE UPDATE OR DELETE ON inventory_partsrequestline
 FOR EACH ROW EXECUTE FUNCTION inventory_reject_history_mutation();
CREATE TRIGGER inventory_request_event_immutable BEFORE UPDATE OR DELETE ON inventory_partsrequestevent
 FOR EACH ROW EXECUTE FUNCTION inventory_reject_history_mutation();

CREATE FUNCTION inventory_reservation_evidence() RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE rid uuid; res inventory_stockreservation; policy text; linked bigint;
BEGIN
 IF TG_TABLE_NAME='inventory_stockreservation' THEN rid := NEW.id;
 ELSE rid := NEW.reservation_id; END IF;
 SELECT * INTO res FROM inventory_stockreservation WHERE id=rid;
 SELECT serialization_policy INTO policy FROM parts_sparepart WHERE id=res.spare_part_id;
 SELECT count(*) INTO linked FROM inventory_reservationunit WHERE reservation_id=rid;
 IF linked > res.quantity OR (policy='REQUIRED_SERIAL' AND linked<>res.quantity) OR (policy='NOT_SERIALIZED' AND linked<>0)
  OR EXISTS(SELECT 1 FROM inventory_reservationunit WHERE reservation_id=rid AND is_active<>(res.status='ACTIVE'))
 THEN RAISE EXCEPTION 'Reservation serialized evidence inconsistent' USING ERRCODE='23514'; END IF;
 IF res.status='ACTIVE' AND EXISTS(SELECT 1 FROM inventory_reservationunit l JOIN inventory_serializedstockunit u ON u.id=l.unit_id
  WHERE l.reservation_id=rid AND (u.current_location_id IS DISTINCT FROM res.location_id OR u.state<>'IN_STOCK'))
 THEN RAISE EXCEPTION 'Reserved unit no longer in source stock' USING ERRCODE='23514'; END IF;
 RETURN NULL;
END $$;
CREATE CONSTRAINT TRIGGER inventory_reservation_evidence AFTER INSERT OR UPDATE ON inventory_stockreservation
 DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION inventory_reservation_evidence();
CREATE CONSTRAINT TRIGGER inventory_reservation_link_evidence AFTER INSERT OR UPDATE ON inventory_reservationunit
 DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION inventory_reservation_evidence();
"""

REVERSE = """
DROP TRIGGER inventory_reservation_link_evidence ON inventory_reservationunit;
DROP TRIGGER inventory_reservation_evidence ON inventory_stockreservation;
DROP FUNCTION inventory_reservation_evidence();
DROP TRIGGER inventory_request_event_immutable ON inventory_partsrequestevent;
DROP TRIGGER inventory_request_line_immutable ON inventory_partsrequestline;
DROP TRIGGER inventory_reservation_unit_guard ON inventory_reservationunit;
DROP FUNCTION inventory_reservation_unit_guard();
DROP TRIGGER inventory_reservation_guard ON inventory_stockreservation;
DROP FUNCTION inventory_reservation_guard();
DROP TRIGGER inventory_request_guard ON inventory_partsrequest;
DROP FUNCTION inventory_request_guard();
"""


class Migration(migrations.Migration):
    dependencies = [("inventory", "0005_job_parts_reservations")]
    operations = [migrations.RunSQL(SQL, REVERSE)]