from django.db import migrations


FORWARD = r"""
ALTER TABLE inventory_goodsreceipt ADD CONSTRAINT inventory_receipt_destination_owner_fk
 FOREIGN KEY (destination_id, company_id) REFERENCES inventory_inventorylocation(id, company_id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE inventory_stocktransfer ADD CONSTRAINT inventory_transfer_source_owner_fk
 FOREIGN KEY (source_id, company_id) REFERENCES inventory_inventorylocation(id, company_id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE inventory_stocktransfer ADD CONSTRAINT inventory_transfer_destination_owner_fk
 FOREIGN KEY (destination_id, company_id) REFERENCES inventory_inventorylocation(id, company_id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE inventory_stocktransfer ADD CONSTRAINT inventory_transfer_transit_owner_fk
 FOREIGN KEY (transit_location_id, company_id) REFERENCES inventory_inventorylocation(id, company_id) DEFERRABLE INITIALLY DEFERRED;

CREATE FUNCTION inventory_document_guard() RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE parent_status varchar;
BEGIN
 IF TG_OP='DELETE' THEN RAISE EXCEPTION 'Inventory document history is retained' USING ERRCODE='23514'; END IF;
 IF TG_TABLE_NAME='inventory_goodsreceipt' THEN
   IF TG_OP='UPDATE' AND (OLD.status<>'DRAFT' OR
       (OLD.company_id, OLD.number, OLD.destination_id, OLD.created_by_id)
         IS DISTINCT FROM (NEW.company_id, NEW.number, NEW.destination_id, NEW.created_by_id)) THEN
     RAISE EXCEPTION 'Posted/cancelled receipt or identity cannot change' USING ERRCODE='23514';
   END IF;
 ELSIF TG_TABLE_NAME='inventory_stocktransfer' THEN
   IF TG_OP='UPDATE' AND ((OLD.company_id, OLD.number, OLD.source_id, OLD.destination_id, OLD.created_by_id)
       IS DISTINCT FROM (NEW.company_id, NEW.number, NEW.source_id, NEW.destination_id, NEW.created_by_id)
       OR OLD.status IN ('RECEIVED','CANCELLED') OR (OLD.status='DISPATCHED' AND
         (NEW.status<>'RECEIVED' OR (OLD.note, OLD.transit_location_id, OLD.dispatched_at, OLD.dispatched_by_id)
          IS DISTINCT FROM (NEW.note, NEW.transit_location_id, NEW.dispatched_at, NEW.dispatched_by_id)))) THEN
     RAISE EXCEPTION 'Dispatched transfer permits receipt only; terminal history is immutable' USING ERRCODE='23514';
   END IF;
 ELSIF TG_TABLE_NAME='inventory_goodsreceiptline' THEN
   SELECT status INTO parent_status FROM inventory_goodsreceipt WHERE id=NEW.receipt_id;
   IF parent_status<>'DRAFT' OR (TG_OP='UPDATE' AND (OLD.receipt_id, OLD.spare_part_id) IS DISTINCT FROM (NEW.receipt_id, NEW.spare_part_id)) THEN
     RAISE EXCEPTION 'Receipt line is immutable' USING ERRCODE='23514';
   END IF;
 ELSIF TG_TABLE_NAME='inventory_goodsreceiptidentifier' THEN
   SELECT r.status INTO parent_status FROM inventory_goodsreceipt r JOIN inventory_goodsreceiptline l ON l.receipt_id=r.id WHERE l.id=NEW.line_id;
   IF parent_status<>'DRAFT' OR (TG_OP='UPDATE' AND (OLD.line_id, OLD.identifier) IS DISTINCT FROM (NEW.line_id, NEW.identifier)) THEN
     RAISE EXCEPTION 'Receipt identifier is immutable' USING ERRCODE='23514';
   END IF;
 ELSIF TG_TABLE_NAME='inventory_stocktransferunit' THEN
   SELECT t.status INTO parent_status FROM inventory_stocktransfer t JOIN inventory_stocktransferline l ON l.transfer_id=t.id WHERE l.id=NEW.line_id;
   IF parent_status<>'DRAFT' OR (TG_OP='UPDATE' AND (OLD.line_id, OLD.unit_id) IS DISTINCT FROM (NEW.line_id, NEW.unit_id)) THEN
     RAISE EXCEPTION 'Transfer unit selection is immutable' USING ERRCODE='23514';
   END IF;
 ELSE
   SELECT status INTO parent_status FROM inventory_stocktransfer WHERE id=NEW.transfer_id;
   IF parent_status IN ('RECEIVED','CANCELLED') OR (TG_OP='UPDATE' AND
       (OLD.transfer_id, OLD.spare_part_id) IS DISTINCT FROM (NEW.transfer_id, NEW.spare_part_id)) OR
       (parent_status='DISPATCHED' AND (TG_OP='INSERT' OR OLD.receive_movement_id IS NOT NULL OR NEW.receive_movement_id IS NULL
        OR (OLD.quantity, OLD.is_active, OLD.dispatch_movement_id) IS DISTINCT FROM (NEW.quantity, NEW.is_active, NEW.dispatch_movement_id))) THEN
     RAISE EXCEPTION 'Dispatched transfer lines permit receipt evidence only' USING ERRCODE='23514';
   END IF;
 END IF;
 RETURN NEW;
END $$;
CREATE TRIGGER inventory_receipt_guard BEFORE UPDATE OR DELETE ON inventory_goodsreceipt FOR EACH ROW EXECUTE FUNCTION inventory_document_guard();
CREATE TRIGGER inventory_transfer_guard BEFORE UPDATE OR DELETE ON inventory_stocktransfer FOR EACH ROW EXECUTE FUNCTION inventory_document_guard();
CREATE TRIGGER inventory_receipt_line_guard BEFORE INSERT OR UPDATE OR DELETE ON inventory_goodsreceiptline FOR EACH ROW EXECUTE FUNCTION inventory_document_guard();
CREATE TRIGGER inventory_receipt_identifier_guard BEFORE INSERT OR UPDATE OR DELETE ON inventory_goodsreceiptidentifier FOR EACH ROW EXECUTE FUNCTION inventory_document_guard();
CREATE TRIGGER inventory_transfer_line_guard BEFORE INSERT OR UPDATE OR DELETE ON inventory_stocktransferline FOR EACH ROW EXECUTE FUNCTION inventory_document_guard();
CREATE TRIGGER inventory_transfer_unit_guard BEFORE INSERT OR UPDATE OR DELETE ON inventory_stocktransferunit FOR EACH ROW EXECUTE FUNCTION inventory_document_guard();

CREATE FUNCTION inventory_validate_receipt(p_id uuid) RETURNS void LANGUAGE plpgsql AS $$
DECLARE r inventory_goodsreceipt%ROWTYPE;
BEGIN
 SELECT * INTO r FROM inventory_goodsreceipt WHERE id=p_id;
 IF NOT FOUND THEN RETURN; END IF;
 IF r.status='POSTED' THEN
   IF NOT EXISTS (SELECT 1 FROM inventory_goodsreceiptline WHERE receipt_id=r.id AND is_active) OR EXISTS (
     SELECT 1 FROM inventory_goodsreceiptline l LEFT JOIN inventory_stockmovement m ON m.id=l.movement_id
     WHERE l.receipt_id=r.id AND l.is_active AND (m.id IS NULL OR m.company_id<>r.company_id OR m.spare_part_id<>l.spare_part_id
       OR m.kind<>'RECEIPT' OR m.destination_id<>r.destination_id OR m.quantity<>l.quantity)) OR EXISTS (
     SELECT 1 FROM inventory_goodsreceiptidentifier i JOIN inventory_goodsreceiptline l ON l.id=i.line_id
     WHERE l.receipt_id=r.id AND l.is_active AND i.is_active AND (i.unit_id IS NULL OR NOT EXISTS (
       SELECT 1 FROM inventory_stockmovementunit u WHERE u.movement_id=l.movement_id AND u.unit_id=i.unit_id))) THEN
     RAISE EXCEPTION 'Posted receipt lacks matching stock evidence' USING ERRCODE='23514';
   END IF;
 ELSE
   IF EXISTS (SELECT 1 FROM inventory_goodsreceiptline WHERE receipt_id=r.id AND movement_id IS NOT NULL) THEN
     RAISE EXCEPTION 'Unposted receipts cannot contain posted movements' USING ERRCODE='23514';
   END IF;
 END IF;
END $$;

CREATE FUNCTION inventory_validate_transfer(p_id uuid) RETURNS void LANGUAGE plpgsql AS $$
DECLARE t inventory_stocktransfer%ROWTYPE;
BEGIN
 SELECT * INTO t FROM inventory_stocktransfer WHERE id=p_id;
 IF NOT FOUND THEN RETURN; END IF;
 IF t.status IN ('DISPATCHED','RECEIVED') THEN
   IF NOT EXISTS (SELECT 1 FROM inventory_inventorylocation WHERE id=t.transit_location_id AND location_type='TRANSIT' AND company_id=t.company_id)
      OR NOT EXISTS (SELECT 1 FROM inventory_stocktransferline WHERE transfer_id=t.id AND is_active)
      OR EXISTS (SELECT 1 FROM inventory_stocktransferline l LEFT JOIN inventory_stockmovement m ON m.id=l.dispatch_movement_id
        WHERE l.transfer_id=t.id AND l.is_active AND (m.id IS NULL OR m.company_id<>t.company_id OR m.spare_part_id<>l.spare_part_id
          OR m.source_id<>t.source_id OR m.destination_id<>t.transit_location_id OR m.kind<>'MOVE' OR m.quantity<>l.quantity)) THEN
     RAISE EXCEPTION 'Transfer lacks matching dispatch evidence' USING ERRCODE='23514';
   END IF;
 END IF;
 IF t.status='RECEIVED' THEN
   IF EXISTS (SELECT 1 FROM inventory_stocktransferline l LEFT JOIN inventory_stockmovement m ON m.id=l.receive_movement_id
       WHERE l.transfer_id=t.id AND l.is_active AND (m.id IS NULL OR m.company_id<>t.company_id OR m.spare_part_id<>l.spare_part_id
         OR m.source_id<>t.transit_location_id OR m.destination_id<>t.destination_id OR m.kind<>'MOVE' OR m.quantity<>l.quantity)) THEN
     RAISE EXCEPTION 'Transfer lacks matching receipt evidence' USING ERRCODE='23514';
   END IF;
 ELSIF EXISTS (SELECT 1 FROM inventory_stocktransferline WHERE transfer_id=t.id AND receive_movement_id IS NOT NULL) THEN
   RAISE EXCEPTION 'Unreceived transfer cannot contain receipt movements' USING ERRCODE='23514';
 END IF;
 IF t.status IN ('DRAFT','CANCELLED') AND EXISTS (SELECT 1 FROM inventory_stocktransferline WHERE transfer_id=t.id AND dispatch_movement_id IS NOT NULL) THEN
   RAISE EXCEPTION 'Undispatched transfer cannot contain dispatch movements' USING ERRCODE='23514';
 END IF;
END $$;

CREATE FUNCTION inventory_check_document() RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE parent_id uuid;
BEGIN
 IF TG_TABLE_NAME='inventory_goodsreceipt' THEN PERFORM inventory_validate_receipt(NEW.id);
 ELSIF TG_TABLE_NAME='inventory_goodsreceiptline' THEN PERFORM inventory_validate_receipt(NEW.receipt_id);
 ELSIF TG_TABLE_NAME='inventory_goodsreceiptidentifier' THEN
   SELECT receipt_id INTO parent_id FROM inventory_goodsreceiptline WHERE id=NEW.line_id;
   PERFORM inventory_validate_receipt(parent_id);
 ELSIF TG_TABLE_NAME='inventory_stocktransfer' THEN PERFORM inventory_validate_transfer(NEW.id);
 ELSIF TG_TABLE_NAME='inventory_stocktransferline' THEN PERFORM inventory_validate_transfer(NEW.transfer_id);
 ELSE
   SELECT transfer_id INTO parent_id FROM inventory_stocktransferline WHERE id=NEW.line_id;
   PERFORM inventory_validate_transfer(parent_id);
 END IF;
 RETURN NULL;
END $$;
CREATE CONSTRAINT TRIGGER inventory_receipt_coherent AFTER INSERT OR UPDATE ON inventory_goodsreceipt DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION inventory_check_document();
CREATE CONSTRAINT TRIGGER inventory_receipt_line_coherent AFTER INSERT OR UPDATE ON inventory_goodsreceiptline DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION inventory_check_document();
CREATE CONSTRAINT TRIGGER inventory_receipt_identifier_coherent AFTER INSERT OR UPDATE ON inventory_goodsreceiptidentifier DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION inventory_check_document();
CREATE CONSTRAINT TRIGGER inventory_transfer_coherent AFTER INSERT OR UPDATE ON inventory_stocktransfer DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION inventory_check_document();
CREATE CONSTRAINT TRIGGER inventory_transfer_line_coherent AFTER INSERT OR UPDATE ON inventory_stocktransferline DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION inventory_check_document();
CREATE CONSTRAINT TRIGGER inventory_transfer_unit_coherent AFTER INSERT OR UPDATE ON inventory_stocktransferunit DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION inventory_check_document();

CREATE FUNCTION inventory_check_transit_unit() RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE u inventory_serializedstockunit%ROWTYPE; kind varchar;
BEGIN
 SELECT * INTO u FROM inventory_serializedstockunit WHERE id=NEW.id;
 IF u.state IN ('IN_STOCK','IN_TRANSIT') THEN
   SELECT location_type INTO kind FROM inventory_inventorylocation WHERE id=u.current_location_id;
   IF (u.state='IN_TRANSIT') IS DISTINCT FROM (kind='TRANSIT') OR NOT EXISTS (
       SELECT 1 FROM inventory_stockmovementunit l JOIN inventory_stockmovement m ON m.id=l.movement_id
       WHERE l.unit_id=u.id AND l.movement_id=u.current_movement_id AND m.destination_id=u.current_location_id) THEN
     RAISE EXCEPTION 'Serialized transit state disagrees with ledger/location' USING ERRCODE='23514';
   END IF;
 END IF;
 RETURN NULL;
END $$;
CREATE CONSTRAINT TRIGGER inventory_unit_transit_coherent AFTER INSERT OR UPDATE ON inventory_serializedstockunit DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION inventory_check_transit_unit();
"""

REVERSE = r"""
DROP TRIGGER inventory_unit_transit_coherent ON inventory_serializedstockunit;
DROP FUNCTION inventory_check_transit_unit();
DROP TRIGGER inventory_transfer_unit_coherent ON inventory_stocktransferunit;
DROP TRIGGER inventory_transfer_line_coherent ON inventory_stocktransferline;
DROP TRIGGER inventory_transfer_coherent ON inventory_stocktransfer;
DROP TRIGGER inventory_receipt_identifier_coherent ON inventory_goodsreceiptidentifier;
DROP TRIGGER inventory_receipt_line_coherent ON inventory_goodsreceiptline;
DROP TRIGGER inventory_receipt_coherent ON inventory_goodsreceipt;
DROP FUNCTION inventory_check_document();
DROP FUNCTION inventory_validate_transfer(uuid);
DROP FUNCTION inventory_validate_receipt(uuid);
DROP TRIGGER inventory_transfer_unit_guard ON inventory_stocktransferunit;
DROP TRIGGER inventory_transfer_line_guard ON inventory_stocktransferline;
DROP TRIGGER inventory_receipt_identifier_guard ON inventory_goodsreceiptidentifier;
DROP TRIGGER inventory_receipt_line_guard ON inventory_goodsreceiptline;
DROP TRIGGER inventory_transfer_guard ON inventory_stocktransfer;
DROP TRIGGER inventory_receipt_guard ON inventory_goodsreceipt;
DROP FUNCTION inventory_document_guard();
ALTER TABLE inventory_stocktransfer DROP CONSTRAINT inventory_transfer_transit_owner_fk;
ALTER TABLE inventory_stocktransfer DROP CONSTRAINT inventory_transfer_destination_owner_fk;
ALTER TABLE inventory_stocktransfer DROP CONSTRAINT inventory_transfer_source_owner_fk;
ALTER TABLE inventory_goodsreceipt DROP CONSTRAINT inventory_receipt_destination_owner_fk;
"""


class Migration(migrations.Migration):
    dependencies = [("inventory", "0003_receiving_transfer")]
    operations = [migrations.RunSQL(FORWARD, REVERSE)]