"""PostgreSQL ownership FKs and immutable, coherent posted inventory evidence."""
from django.db import migrations


FORWARD = r"""
ALTER TABLE inventory_stockmovement ADD CONSTRAINT inventory_source_owner_fk
 FOREIGN KEY (source_id, company_id) REFERENCES inventory_inventorylocation(id, company_id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE inventory_stockmovement ADD CONSTRAINT inventory_destination_owner_fk
 FOREIGN KEY (destination_id, company_id) REFERENCES inventory_inventorylocation(id, company_id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE inventory_stockledgerentry ADD CONSTRAINT inventory_entry_owner_fk
 FOREIGN KEY (location_id, company_id) REFERENCES inventory_inventorylocation(id, company_id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE inventory_stockledgerentry ADD CONSTRAINT inventory_entry_movement_owner_fk
 FOREIGN KEY (movement_id, company_id, spare_part_id) REFERENCES inventory_stockmovement(id, company_id, spare_part_id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE inventory_serializedstockunit ADD CONSTRAINT inventory_unit_location_owner_fk
 FOREIGN KEY (current_location_id, company_id) REFERENCES inventory_inventorylocation(id, company_id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE inventory_serializedstockunit ADD CONSTRAINT inventory_unit_movement_owner_fk
 FOREIGN KEY (current_movement_id, company_id, spare_part_id) REFERENCES inventory_stockmovement(id, company_id, spare_part_id) DEFERRABLE INITIALLY DEFERRED;

CREATE FUNCTION inventory_reject_history_mutation() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
 RAISE EXCEPTION 'Posted inventory history is immutable' USING ERRCODE = '23514';
END $$;
CREATE TRIGGER inventory_movement_immutable BEFORE UPDATE OR DELETE ON inventory_stockmovement
 FOR EACH ROW EXECUTE FUNCTION inventory_reject_history_mutation();
CREATE TRIGGER inventory_entry_immutable BEFORE UPDATE OR DELETE ON inventory_stockledgerentry
 FOR EACH ROW EXECUTE FUNCTION inventory_reject_history_mutation();
CREATE TRIGGER inventory_unit_link_immutable BEFORE UPDATE OR DELETE ON inventory_stockmovementunit
 FOR EACH ROW EXECUTE FUNCTION inventory_reject_history_mutation();
CREATE TRIGGER inventory_unit_retained BEFORE DELETE ON inventory_serializedstockunit
 FOR EACH ROW EXECUTE FUNCTION inventory_reject_history_mutation();

CREATE FUNCTION inventory_protect_identity() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
 IF TG_TABLE_NAME = 'inventory_inventorylocation' THEN
   IF TG_OP = 'UPDATE' AND (NEW.company_id, NEW.service_center_id, NEW.code, NEW.location_type)
       IS DISTINCT FROM (OLD.company_id, OLD.service_center_id, OLD.code, OLD.location_type) THEN
     RAISE EXCEPTION 'Inventory location identity is immutable' USING ERRCODE = '23514';
   END IF;
   IF NEW.service_center_id IS NOT NULL AND NOT EXISTS (
       SELECT 1 FROM organization_servicecenter WHERE id=NEW.service_center_id AND company_id=NEW.company_id) THEN
     RAISE EXCEPTION 'Location center belongs to another company' USING ERRCODE = '23514';
   END IF;
 ELSE
   IF (NEW.company_id, NEW.spare_part_id, NEW.identifier, NEW.registered_by_id)
       IS DISTINCT FROM (OLD.company_id, OLD.spare_part_id, OLD.identifier, OLD.registered_by_id) THEN
     RAISE EXCEPTION 'Serialized identity is permanent' USING ERRCODE = '23514';
   END IF;
 END IF;
 RETURN NEW;
END $$;
CREATE TRIGGER inventory_location_identity BEFORE INSERT OR UPDATE ON inventory_inventorylocation
 FOR EACH ROW EXECUTE FUNCTION inventory_protect_identity();
CREATE TRIGGER inventory_unit_identity BEFORE UPDATE ON inventory_serializedstockunit
 FOR EACH ROW EXECUTE FUNCTION inventory_protect_identity();

CREATE FUNCTION inventory_validate_movement(p_id uuid) RETURNS void LANGUAGE plpgsql AS $$
DECLARE m inventory_stockmovement%ROWTYPE; n bigint; policy varchar;
BEGIN
 SELECT * INTO m FROM inventory_stockmovement WHERE id=p_id;
 IF NOT FOUND THEN RETURN; END IF;
 SELECT COUNT(*) INTO n FROM inventory_stockledgerentry WHERE movement_id=m.id;
 IF n <> (CASE WHEN m.source_id IS NULL THEN 1 ELSE 2 END)
    OR NOT EXISTS (SELECT 1 FROM inventory_stockledgerentry WHERE movement_id=m.id
       AND location_id=m.destination_id AND quantity_delta=m.quantity)
    OR (m.source_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM inventory_stockledgerentry WHERE movement_id=m.id
       AND location_id=m.source_id AND quantity_delta=-m.quantity)) THEN
   RAISE EXCEPTION 'Posted movement and ledger entries disagree' USING ERRCODE = '23514';
 END IF;
 SELECT COUNT(*) INTO n FROM inventory_stockmovementunit WHERE movement_id=m.id;
 SELECT serialization_policy INTO policy FROM parts_sparepart WHERE id=m.spare_part_id;
 IF n > m.quantity OR (policy='REQUIRED_SERIAL' AND n<>m.quantity) OR (policy='NOT_SERIALIZED' AND n<>0)
    OR EXISTS (SELECT 1 FROM inventory_stockmovementunit l JOIN inventory_serializedstockunit u ON u.id=l.unit_id
       WHERE l.movement_id=m.id AND (u.company_id<>m.company_id OR u.spare_part_id<>m.spare_part_id)) THEN
   RAISE EXCEPTION 'Posted movement and serialized units disagree' USING ERRCODE = '23514';
 END IF;
END $$;

CREATE FUNCTION inventory_check_posted_evidence() RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE u inventory_serializedstockunit%ROWTYPE;
BEGIN
 IF TG_TABLE_NAME='inventory_stockmovement' THEN
   PERFORM inventory_validate_movement(NEW.id);
 ELSIF TG_TABLE_NAME='inventory_serializedstockunit' THEN
   SELECT * INTO u FROM inventory_serializedstockunit WHERE id=NEW.id;
   IF u.state='IN_STOCK' AND 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 current state has no matching movement' USING ERRCODE = '23514';
   END IF;
 ELSE
   PERFORM inventory_validate_movement(NEW.movement_id);
 END IF;
 RETURN NULL;
END $$;
CREATE CONSTRAINT TRIGGER inventory_movement_coherent AFTER INSERT ON inventory_stockmovement
 DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION inventory_check_posted_evidence();
CREATE CONSTRAINT TRIGGER inventory_entry_coherent AFTER INSERT ON inventory_stockledgerentry
 DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION inventory_check_posted_evidence();
CREATE CONSTRAINT TRIGGER inventory_unit_link_coherent AFTER INSERT ON inventory_stockmovementunit
 DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION inventory_check_posted_evidence();
CREATE CONSTRAINT TRIGGER inventory_unit_state_coherent AFTER INSERT OR UPDATE ON inventory_serializedstockunit
 DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION inventory_check_posted_evidence();
"""

REVERSE = r"""
DROP TRIGGER inventory_unit_state_coherent ON inventory_serializedstockunit;
DROP TRIGGER inventory_unit_link_coherent ON inventory_stockmovementunit;
DROP TRIGGER inventory_entry_coherent ON inventory_stockledgerentry;
DROP TRIGGER inventory_movement_coherent ON inventory_stockmovement;
DROP FUNCTION inventory_check_posted_evidence();
DROP FUNCTION inventory_validate_movement(uuid);
DROP TRIGGER inventory_unit_identity ON inventory_serializedstockunit;
DROP TRIGGER inventory_location_identity ON inventory_inventorylocation;
DROP FUNCTION inventory_protect_identity();
DROP TRIGGER inventory_unit_retained ON inventory_serializedstockunit;
DROP TRIGGER inventory_unit_link_immutable ON inventory_stockmovementunit;
DROP TRIGGER inventory_entry_immutable ON inventory_stockledgerentry;
DROP TRIGGER inventory_movement_immutable ON inventory_stockmovement;
DROP FUNCTION inventory_reject_history_mutation();
ALTER TABLE inventory_serializedstockunit DROP CONSTRAINT inventory_unit_movement_owner_fk;
ALTER TABLE inventory_serializedstockunit DROP CONSTRAINT inventory_unit_location_owner_fk;
ALTER TABLE inventory_stockledgerentry DROP CONSTRAINT inventory_entry_movement_owner_fk;
ALTER TABLE inventory_stockledgerentry DROP CONSTRAINT inventory_entry_owner_fk;
ALTER TABLE inventory_stockmovement DROP CONSTRAINT inventory_destination_owner_fk;
ALTER TABLE inventory_stockmovement DROP CONSTRAINT inventory_source_owner_fk;
"""


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