"""Extend typed ledger endpoints for actual consumption; retain receipt/move rules."""
from importlib import import_module
from django.db import migrations


SQL = r"""
CREATE OR REPLACE FUNCTION inventory_validate_movement(p_id uuid) RETURNS void LANGUAGE plpgsql AS $$
DECLARE m inventory_stockmovement; 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 <> ((m.source_id IS NOT NULL)::int + (m.destination_id IS NOT NULL)::int)
  OR (m.destination_id IS NOT NULL AND 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 'Movement ledger evidence inconsistent' 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 'Movement serialization inconsistent' USING ERRCODE='23514'; END IF;
 IF m.kind='CONSUME' AND NOT EXISTS(SELECT 1 FROM inventory_partsdisposition WHERE movement_id=m.id AND kind='CONSUMED')
 THEN RAISE EXCEPTION 'Consumption requires job evidence' USING ERRCODE='23514'; END IF;
 IF EXISTS(SELECT 1 FROM inventory_inventorylocation WHERE id IN (m.source_id,m.destination_id) AND location_type='CUSTODY')
 AND NOT EXISTS(SELECT 1 FROM inventory_partsissue WHERE movement_id=m.id)
 AND NOT EXISTS(SELECT 1 FROM inventory_partsdisposition WHERE movement_id=m.id)
 THEN RAISE EXCEPTION 'Custody movement requires job evidence' USING ERRCODE='23514'; END IF;
END $$;

CREATE OR REPLACE FUNCTION inventory_check_transit_unit() RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE u inventory_serializedstockunit; loc_kind text;
BEGIN
 SELECT * INTO u FROM inventory_serializedstockunit WHERE id=NEW.id;
 IF u.state IN ('IN_STOCK','IN_TRANSIT','IN_CUSTODY') THEN
  SELECT location_type INTO loc_kind FROM inventory_inventorylocation WHERE id=u.current_location_id;
  IF u.state IS DISTINCT FROM (CASE loc_kind WHEN 'TRANSIT' THEN 'IN_TRANSIT' WHEN 'CUSTODY' THEN 'IN_CUSTODY' ELSE 'IN_STOCK' END)
   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 m.id=u.current_movement_id AND m.destination_id=u.current_location_id)
  THEN RAISE EXCEPTION 'Unit location and ledger disagree' USING ERRCODE='23514'; END IF;
 ELSIF u.state='CONSUMED' 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='CONSUME' AND m.destination_id IS NULL)
 THEN RAISE EXCEPTION 'Consumed unit requires consumption evidence' USING ERRCODE='23514'; END IF;
 RETURN NULL;
END $$;

CREATE FUNCTION inventory_usage_evidence() RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE iss inventory_partsissue; res inventory_stockreservation; mov inventory_stockmovement; disp inventory_partsdisposition;
BEGIN
 IF TG_TABLE_NAME='inventory_partsissue' THEN iss := NEW;
 ELSE SELECT * INTO iss FROM inventory_partsissue WHERE id=NEW.issue_id; END IF;
 SELECT * INTO res FROM inventory_stockreservation WHERE id=iss.reservation_id;
 SELECT * INTO mov FROM inventory_stockmovement WHERE id=iss.movement_id;
 IF res.status<>'ISSUED' OR iss.service_case_id<>res.service_case_id OR mov.company_id<>res.company_id
  OR mov.spare_part_id<>res.spare_part_id OR mov.quantity<>res.quantity OR mov.source_id IS DISTINCT FROM res.location_id
  OR mov.destination_id IS DISTINCT FROM iss.custody_location_id OR mov.kind<>'MOVE' OR mov.actor_id<>iss.issuer_id
  OR NOT EXISTS(SELECT 1 FROM service_serviceengineerassignment WHERE id=iss.engineer_assignment_id AND service_case_id=iss.service_case_id AND engineer_id=iss.recipient_id)
  OR NOT EXISTS(SELECT 1 FROM inventory_inventorylocation WHERE id=iss.custody_location_id AND location_type='CUSTODY')
 THEN RAISE EXCEPTION 'Issue evidence inconsistent' USING ERRCODE='23514'; END IF;
 IF (SELECT coalesce(sum(quantity),0) FROM inventory_partsdisposition WHERE issue_id=iss.id)>res.quantity
 THEN RAISE EXCEPTION 'Issued quantity resolved more than once' USING ERRCODE='23514'; END IF;
 IF TG_TABLE_NAME='inventory_partsdisposition' THEN
  disp := NEW;
  SELECT * INTO mov FROM inventory_stockmovement WHERE id=disp.movement_id;
  IF mov.company_id<>res.company_id OR mov.spare_part_id<>res.spare_part_id OR mov.quantity<>disp.quantity
   OR mov.source_id IS DISTINCT FROM iss.custody_location_id OR mov.actor_id<>disp.actor_id
   OR (disp.kind='CONSUMED' AND (mov.kind<>'CONSUME' OR NOT EXISTS(
    SELECT 1 FROM service_servicerepairaction a JOIN service_servicerepairexecution e ON e.id=a.repair_execution_id
    WHERE a.id=disp.repair_action_id AND e.service_case_id=iss.service_case_id)))
   OR (disp.kind='RETURNED' AND (mov.kind<>'MOVE' OR NOT EXISTS(SELECT 1 FROM inventory_inventorylocation WHERE id=mov.destination_id AND location_type IN ('WAREHOUSE','STORE'))))
  THEN RAISE EXCEPTION 'Disposition evidence inconsistent' USING ERRCODE='23514'; END IF;
 END IF;
 RETURN NULL;
END $$;
CREATE TRIGGER inventory_issue_immutable BEFORE UPDATE OR DELETE ON inventory_partsissue FOR EACH ROW EXECUTE FUNCTION inventory_reject_history_mutation();
CREATE TRIGGER inventory_disposition_immutable BEFORE UPDATE OR DELETE ON inventory_partsdisposition FOR EACH ROW EXECUTE FUNCTION inventory_reject_history_mutation();
CREATE CONSTRAINT TRIGGER inventory_issue_evidence AFTER INSERT ON inventory_partsissue DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION inventory_usage_evidence();
CREATE CONSTRAINT TRIGGER inventory_disposition_evidence AFTER INSERT ON inventory_partsdisposition DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION inventory_usage_evidence();
"""


def previous_function(module, name):
    sql = import_module(f"apps.inventory.migrations.{module}").FORWARD
    start = sql.index(f"CREATE FUNCTION {name}")
    end = sql.index("END $$;", start) + len("END $$;")
    return sql[start:end].replace("CREATE FUNCTION", "CREATE OR REPLACE FUNCTION", 1)


REVERSE = """
DROP TRIGGER inventory_disposition_evidence ON inventory_partsdisposition;
DROP TRIGGER inventory_issue_evidence ON inventory_partsissue;
DROP TRIGGER inventory_disposition_immutable ON inventory_partsdisposition;
DROP TRIGGER inventory_issue_immutable ON inventory_partsissue;
DROP FUNCTION inventory_usage_evidence();
""" + previous_function("0002_ledger_integrity", "inventory_validate_movement") + previous_function("0004_document_integrity", "inventory_check_transit_unit")


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