from importlib import import_module
from django.db import migrations


def old_function(name):
    sql = import_module("apps.inventory.migrations.0008_usage_integrity").SQL
    start = sql.index(f"CREATE OR REPLACE FUNCTION {name}")
    return sql[start:sql.index("END $$;", start)+len("END $$;")]


OLD_MOVEMENT = old_function("inventory_validate_movement")
OLD_UNIT = old_function("inventory_check_transit_unit")
MOVEMENT = OLD_MOVEMENT.replace("END $$;", """
 IF m.kind IN ('ADJUST_IN','ADJUST_OUT') AND NOT EXISTS(SELECT 1 FROM inventory_stockadjustment WHERE movement_id=m.id)
 THEN RAISE EXCEPTION 'Adjustment movement requires controlled evidence' USING ERRCODE='23514'; END IF;
END $$;""")
UNIT = OLD_UNIT.replace(" RETURN NULL;", """
 IF u.state='REMOVED' 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 m.id=u.current_movement_id AND m.kind='ADJUST_OUT' AND m.destination_id IS NULL)
 THEN RAISE EXCEPTION 'Removed unit requires adjustment evidence' USING ERRCODE='23514'; END IF;
 RETURN NULL;""")

SQL = r"""
CREATE FUNCTION inventory_adjustment_guard() RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE m inventory_stockmovement;
BEGIN
 IF TG_OP<>'INSERT' THEN RAISE EXCEPTION 'Adjustment history immutable' USING ERRCODE='23514'; END IF;
 SELECT * INTO m FROM inventory_stockmovement WHERE id=NEW.movement_id;
 IF m.company_id<>NEW.company_id OR m.spare_part_id<>NEW.spare_part_id OR m.actor_id<>NEW.actor_id OR m.quantity<>abs(NEW.quantity_delta)
  OR (NEW.quantity_delta>0 AND (m.kind<>'ADJUST_IN' OR m.destination_id IS DISTINCT FROM NEW.location_id))
  OR (NEW.quantity_delta<0 AND (m.kind<>'ADJUST_OUT' OR m.source_id IS DISTINCT FROM NEW.location_id))
  OR (NEW.count_id IS NOT NULL AND NOT EXISTS(SELECT 1 FROM inventory_stockcount WHERE id=NEW.count_id AND company_id=NEW.company_id AND location_id=NEW.location_id AND spare_part_id=NEW.spare_part_id AND status='COUNTING'))
 THEN RAISE EXCEPTION 'Adjustment ownership/evidence inconsistent' USING ERRCODE='23514'; END IF;
 RETURN NEW;
END $$;
CREATE TRIGGER inventory_adjustment_guard BEFORE INSERT OR UPDATE OR DELETE ON inventory_stockadjustment FOR EACH ROW EXECUTE FUNCTION inventory_adjustment_guard();

CREATE FUNCTION inventory_count_guard() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
 IF TG_OP='DELETE' THEN RAISE EXCEPTION 'Count history retained' USING ERRCODE='23514'; END IF;
 IF NEW.company_id IS DISTINCT FROM (SELECT company_id FROM inventory_inventorylocation WHERE id=NEW.location_id)
 THEN RAISE EXCEPTION 'Count ownership mismatch' USING ERRCODE='23514'; END IF;
 IF TG_OP='UPDATE' AND (ROW(NEW.company_id,NEW.location_id,NEW.spare_part_id,NEW.created_by_id)
   IS DISTINCT FROM ROW(OLD.company_id,OLD.location_id,OLD.spare_part_id,OLD.created_by_id)
  OR OLD.status IN ('RECONCILED','CANCELLED')
  OR (OLD.status='COUNTING' AND (NEW.status NOT IN ('COUNTING','RECONCILED','CANCELLED')
    OR ROW(NEW.expected_quantity,NEW.started_at,NEW.started_by_id) IS DISTINCT FROM ROW(OLD.expected_quantity,OLD.started_at,OLD.started_by_id))))
 THEN RAISE EXCEPTION 'Count snapshot/history immutable' USING ERRCODE='23514'; END IF;
 RETURN NEW;
END $$;
CREATE TRIGGER inventory_count_guard BEFORE INSERT OR UPDATE OR DELETE ON inventory_stockcount FOR EACH ROW EXECUTE FUNCTION inventory_count_guard();

CREATE FUNCTION inventory_count_unit_guard() RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE c inventory_stockcount; u inventory_serializedstockunit;
BEGIN
 IF TG_OP='DELETE' THEN RAISE EXCEPTION 'Count unit history retained' USING ERRCODE='23514'; END IF;
 SELECT * INTO c FROM inventory_stockcount WHERE id=NEW.count_id;
 SELECT * INTO u FROM inventory_serializedstockunit WHERE id=NEW.unit_id;
 IF c.status<>'COUNTING' OR c.company_id<>u.company_id OR c.spare_part_id<>u.spare_part_id
  OR (TG_OP='UPDATE' AND ROW(NEW.count_id,NEW.unit_id,NEW.expected) IS DISTINCT FROM ROW(OLD.count_id,OLD.unit_id,OLD.expected))
 THEN RAISE EXCEPTION 'Count unit snapshot/ownership immutable' USING ERRCODE='23514'; END IF;
 RETURN NEW;
END $$;
CREATE TRIGGER inventory_count_unit_guard BEFORE INSERT OR UPDATE OR DELETE ON inventory_stockcountunit FOR EACH ROW EXECUTE FUNCTION inventory_count_unit_guard();

CREATE FUNCTION inventory_count_evidence() RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE c inventory_stockcount; cid uuid; variance bigint; observed bigint; expected bigint; policy text;
BEGIN
 IF TG_TABLE_NAME='inventory_stockcount' THEN cid := NEW.id; ELSE cid := NEW.count_id; END IF;
 IF cid IS NULL THEN RETURN NULL; END IF;
 SELECT * INTO c FROM inventory_stockcount WHERE id=cid;
 SELECT coalesce(sum(quantity_delta),0) INTO variance FROM inventory_stockadjustment WHERE count_id=cid;
 IF (c.status='RECONCILED' AND variance<>c.counted_quantity-c.expected_quantity)
  OR (c.status<>'RECONCILED' AND EXISTS(SELECT 1 FROM inventory_stockadjustment WHERE count_id=cid))
 THEN RAISE EXCEPTION 'Count variance and posted adjustments disagree' USING ERRCODE='23514'; END IF;
 SELECT count(*) FILTER(WHERE counted),count(*) FILTER(WHERE inventory_stockcountunit.expected) INTO observed,expected FROM inventory_stockcountunit WHERE count_id=cid;
 SELECT serialization_policy INTO policy FROM parts_sparepart WHERE id=c.spare_part_id;
 IF (c.expected_quantity IS NOT NULL AND (expected>c.expected_quantity OR (policy='REQUIRED_SERIAL' AND expected<>c.expected_quantity)))
  OR (c.counted_quantity IS NOT NULL AND (observed>c.counted_quantity OR (policy='REQUIRED_SERIAL' AND observed<>c.counted_quantity)))
  OR (policy='NOT_SERIALIZED' AND (observed<>0 OR expected<>0))
 THEN RAISE EXCEPTION 'Count serialized evidence disagrees' USING ERRCODE='23514'; END IF;
 RETURN NULL;
END $$;
CREATE CONSTRAINT TRIGGER inventory_count_coherent AFTER INSERT OR UPDATE ON inventory_stockcount DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION inventory_count_evidence();
CREATE CONSTRAINT TRIGGER inventory_count_unit_evidence AFTER INSERT OR UPDATE ON inventory_stockcountunit DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION inventory_count_evidence();
CREATE CONSTRAINT TRIGGER inventory_adjustment_count_evidence AFTER INSERT ON inventory_stockadjustment DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION inventory_count_evidence();
"""

REVERSE = """
DROP TRIGGER inventory_adjustment_count_evidence ON inventory_stockadjustment;
DROP TRIGGER inventory_count_unit_evidence ON inventory_stockcountunit;
DROP TRIGGER inventory_count_coherent ON inventory_stockcount;
DROP FUNCTION inventory_count_evidence();
DROP TRIGGER inventory_count_unit_guard ON inventory_stockcountunit;
DROP FUNCTION inventory_count_unit_guard();
DROP TRIGGER inventory_count_guard ON inventory_stockcount;
DROP FUNCTION inventory_count_guard();
DROP TRIGGER inventory_adjustment_guard ON inventory_stockadjustment;
DROP FUNCTION inventory_adjustment_guard();
"""


class Migration(migrations.Migration):
    dependencies = [("inventory", "0010_stock_control")]
    operations = [migrations.RunSQL(MOVEMENT+UNIT+SQL, REVERSE+OLD_MOVEMENT+OLD_UNIT)]