from django.db import migrations


SQL = r"""
CREATE FUNCTION commercial_guard_history() RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE q commercial_servicequotation; case_id uuid;
BEGIN
 IF TG_OP = 'DELETE' THEN RAISE EXCEPTION 'Commercial history cannot be deleted' USING ERRCODE='23514'; END IF;
 IF TG_TABLE_NAME = 'commercial_quotationfamily' THEN
  IF NOT EXISTS (SELECT 1 FROM service_servicecase c WHERE c.id=NEW.service_case_id AND c.service_center_id=NEW.service_center_id) THEN
   RAISE EXCEPTION 'Quotation family ownership mismatch' USING ERRCODE='23514'; END IF;
  IF TG_OP='UPDATE' AND ((OLD.service_case_id,OLD.service_center_id,OLD.number) IS DISTINCT FROM (NEW.service_case_id,NEW.service_center_id,NEW.number)
    OR (OLD.approval_obligation AND NOT NEW.approval_obligation)) THEN
   RAISE EXCEPTION 'Family identity and established obligation are immutable' USING ERRCODE='23514'; END IF;
 ELSIF TG_TABLE_NAME = 'commercial_servicequotation' THEN
  IF NEW.submitted_at IS NOT NULL AND (NEW.submitted_at<NEW.created_at OR NEW.submitted_at>clock_timestamp()) THEN
   RAISE EXCEPTION 'Invalid submission timestamp' USING ERRCODE='23514'; END IF;
  IF TG_OP='UPDATE' THEN
   IF OLD.status='DRAFT' AND NEW.status NOT IN ('DRAFT','SUBMITTED') THEN
    RAISE EXCEPTION 'Draft must be explicitly submitted' USING ERRCODE='23514'; END IF;
   IF (OLD.family_id,OLD.revision,OLD.created_by_id,OLD.created_at) IS DISTINCT FROM (NEW.family_id,NEW.revision,NEW.created_by_id,NEW.created_at) THEN
    RAISE EXCEPTION 'Quotation identity immutable' USING ERRCODE='23514'; END IF;
   IF OLD.status<>'DRAFT' AND (to_jsonb(OLD)-ARRAY['status','is_current','updated_at']) IS DISTINCT FROM (to_jsonb(NEW)-ARRAY['status','is_current','updated_at']) THEN
    RAISE EXCEPTION 'Submitted commercial content immutable' USING ERRCODE='23514'; END IF;
   IF OLD.status<>'DRAFT' AND NEW.status<>OLD.status AND NOT (OLD.status='SUBMITTED' AND NEW.status IN ('APPROVED','REJECTED','SUPERSEDED')) THEN
    RAISE EXCEPTION 'Invalid quotation transition' USING ERRCODE='23514'; END IF;
   IF NOT OLD.is_current AND NEW.is_current THEN RAISE EXCEPTION 'Historical revision cannot reopen' USING ERRCODE='23514'; END IF;
  END IF;
 ELSIF TG_TABLE_NAME = 'commercial_quotationline' THEN
  SELECT * INTO q FROM commercial_servicequotation WHERE id=NEW.quotation_id;
  IF q.status<>'DRAFT' THEN RAISE EXCEPTION 'Submitted lines immutable' USING ERRCODE='23514'; END IF;
  IF TG_OP='UPDATE' AND (OLD.quotation_id<>NEW.quotation_id OR NOT OLD.is_active) THEN
   RAISE EXCEPTION 'Historical line identity immutable' USING ERRCODE='23514'; END IF;
  SELECT service_case_id INTO case_id FROM commercial_quotationfamily WHERE id=q.family_id;
  IF NEW.repair_action_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM service_servicerepairaction a JOIN service_servicerepairexecution e ON e.id=a.repair_execution_id WHERE a.id=NEW.repair_action_id AND e.service_case_id=case_id) THEN
   RAISE EXCEPTION 'Cross-case repair reference' USING ERRCODE='23514'; END IF;
  IF NEW.finding_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM service_servicediagnosticfinding f JOIN service_servicediagnosticassessment a ON a.id=f.assessment_id WHERE f.id=NEW.finding_id AND a.service_case_id=case_id) THEN
   RAISE EXCEPTION 'Cross-case finding reference' USING ERRCODE='23514'; END IF;
  IF NEW.tax<>round(NEW.taxable_amount*NEW.tax_rate/100,2) THEN RAISE EXCEPTION 'Tax arithmetic mismatch' USING ERRCODE='23514'; END IF;
 ELSE
  IF TG_OP='UPDATE' THEN RAISE EXCEPTION 'Commercial evidence immutable' USING ERRCODE='23514'; END IF;
  IF TG_TABLE_NAME='commercial_commercialworkauthorization' THEN
   SELECT * INTO q FROM commercial_servicequotation WHERE id=NEW.quotation_id;
   SELECT service_case_id INTO case_id FROM commercial_quotationfamily WHERE id=q.family_id;
   IF q.status<>'APPROVED' OR NOT q.is_current OR NOT EXISTS (SELECT 1 FROM service_servicerepairexecution WHERE id=NEW.repair_execution_id AND service_case_id=case_id)
    OR (NEW.repair_action_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM service_servicerepairaction WHERE id=NEW.repair_action_id AND repair_execution_id=NEW.repair_execution_id)) THEN
    RAISE EXCEPTION 'Invalid commercial work evidence' USING ERRCODE='23514'; END IF;
  END IF;
 END IF;
 RETURN NEW;
END $$;

CREATE FUNCTION commercial_check_aggregate() RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE q commercial_servicequotation; qid uuid; totals record; decision_status text;
BEGIN
 IF TG_TABLE_NAME='commercial_servicequotation' THEN qid:=NEW.id; ELSE qid:=NEW.quotation_id; END IF;
 SELECT * INTO q FROM commercial_servicequotation WHERE id=qid;
 SELECT coalesce(sum(subtotal),0) subtotal, coalesce(sum(discount),0) discount,
  coalesce(sum(tax),0) tax, coalesce(sum(total),0) total,
  coalesce(sum(total) FILTER(WHERE responsibility='CUSTOMER'),0) customer INTO totals
 FROM commercial_quotationline WHERE quotation_id=qid AND is_active;
 IF (q.subtotal,q.discount_total,q.tax_total,q.grand_total,q.customer_pay_total,q.covered_total)
  IS DISTINCT FROM (totals.subtotal,totals.discount,totals.tax,totals.total,totals.customer,totals.total-totals.customer) THEN
  RAISE EXCEPTION 'Quotation totals must reconcile to lines' USING ERRCODE='23514'; END IF;
 SELECT outcome INTO decision_status FROM commercial_quotationdecision WHERE quotation_id=qid;
 IF EXISTS(SELECT 1 FROM commercial_quotationdecision WHERE quotation_id=qid AND created_at<q.submitted_at) THEN
  RAISE EXCEPTION 'Decision cannot predate submission' USING ERRCODE='23514'; END IF;
 IF (q.status IN ('APPROVED','REJECTED') AND decision_status IS DISTINCT FROM q.status)
  OR (q.status NOT IN ('APPROVED','REJECTED') AND decision_status IS NOT NULL) THEN
  RAISE EXCEPTION 'Decision and quotation state disagree' USING ERRCODE='23514'; END IF;
 IF q.customer_pay_total>0 AND NOT EXISTS(SELECT 1 FROM commercial_quotationfamily WHERE id=q.family_id AND approval_obligation) THEN
  RAISE EXCEPTION 'Customer-pay work establishes approval obligation' USING ERRCODE='23514'; END IF;
 RETURN NULL;
END $$;
"""

TABLES = ("quotationfamily", "servicequotation", "quotationline", "quotationdecision", "commercialworkauthorization")
for table in TABLES:
    SQL += f"CREATE TRIGGER commercial_history BEFORE INSERT OR UPDATE OR DELETE ON commercial_{table} FOR EACH ROW EXECUTE FUNCTION commercial_guard_history();\n"
for table in ("servicequotation", "quotationline", "quotationdecision"):
    SQL += f"CREATE CONSTRAINT TRIGGER commercial_aggregate AFTER INSERT OR UPDATE ON commercial_{table} DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION commercial_check_aggregate();\n"

REVERSE = "\n".join(f"DROP TRIGGER commercial_history ON commercial_{table};" for table in TABLES)
REVERSE += "\n" + "\n".join(f"DROP TRIGGER commercial_aggregate ON commercial_{table};" for table in ("servicequotation", "quotationline", "quotationdecision"))
REVERSE += "\nDROP FUNCTION commercial_check_aggregate(); DROP FUNCTION commercial_guard_history();"


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