from django.db import migrations

SQL = r"""
CREATE FUNCTION commercial_payment_guard() RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE p commercial_servicepayment%ROWTYPE;
BEGIN
 IF TG_OP='DELETE' THEN RAISE EXCEPTION 'Financial evidence cannot be deleted' USING ERRCODE='23514'; END IF;
 IF TG_TABLE_NAME='commercial_servicepayment' THEN
  IF TG_OP='UPDATE' THEN
   IF OLD.status<>'POSTED' OR NEW.status<>'VOIDED' OR
      (to_jsonb(NEW)-'status'-'updated_at') IS DISTINCT FROM (to_jsonb(OLD)-'status'-'updated_at') THEN
    RAISE EXCEPTION 'Payment financial facts are immutable' USING ERRCODE='23514';
   END IF;
  ELSIF NEW.status<>'POSTED' THEN RAISE EXCEPTION 'Payment starts posted' USING ERRCODE='23514';
  END IF;
  IF NOT EXISTS (SELECT 1 FROM commercial_serviceinvoice i WHERE i.id=NEW.invoice_id AND i.status='FINALIZED'
     AND i.currency=NEW.currency AND i.service_center_id=NEW.service_center_id) THEN
   RAISE EXCEPTION 'Payment invoice/currency/center mismatch' USING ERRCODE='23514';
  END IF;
  RETURN NEW;
 END IF;
 IF TG_OP='UPDATE' THEN RAISE EXCEPTION 'Financial evidence is immutable' USING ERRCODE='23514'; END IF;
 IF TG_TABLE_NAME='commercial_servicefinancialrelease' THEN
  IF NOT EXISTS (SELECT 1 FROM commercial_serviceinvoice i WHERE i.id=NEW.invoice_id AND i.status='FINALIZED'
     AND NEW.outstanding_amount<=i.customer_pay_total) THEN
   RAISE EXCEPTION 'Invalid release liability' USING ERRCODE='23514';
  END IF;
  RETURN NEW;
 END IF;
 SELECT * INTO p FROM commercial_servicepayment WHERE id=NEW.payment_id;
 IF p.status IS DISTINCT FROM 'POSTED' OR NEW.amount<>p.amount THEN
  RAISE EXCEPTION 'Financial evidence must match a posted payment' USING ERRCODE='23514';
 END IF;
 IF TG_TABLE_NAME='commercial_paymentallocation' THEN
  IF NEW.invoice_id<>p.invoice_id OR NEW.allocated_by_id<>p.received_by_id THEN
   RAISE EXCEPTION 'Payment allocation mismatch' USING ERRCODE='23514';
  END IF;
 ELSIF TG_TABLE_NAME='commercial_servicepaymentreceipt' THEN
  IF (NEW.service_center_id,NEW.currency,NEW.method,NEW.reference,NEW.issued_by_id,NEW.issued_at)
    IS DISTINCT FROM (p.service_center_id,p.currency,p.method,p.reference,p.received_by_id,p.received_at) THEN
   RAISE EXCEPTION 'Receipt must acknowledge the original payment' USING ERRCODE='23514';
  END IF;
 END IF;
 RETURN NEW;
END $$;
CREATE TRIGGER payment_guard BEFORE INSERT OR UPDATE OR DELETE ON commercial_servicepayment FOR EACH ROW EXECUTE FUNCTION commercial_payment_guard();
CREATE TRIGGER payment_allocation_guard BEFORE INSERT OR UPDATE OR DELETE ON commercial_paymentallocation FOR EACH ROW EXECUTE FUNCTION commercial_payment_guard();
CREATE TRIGGER payment_receipt_guard BEFORE INSERT OR UPDATE OR DELETE ON commercial_servicepaymentreceipt FOR EACH ROW EXECUTE FUNCTION commercial_payment_guard();
CREATE TRIGGER payment_reversal_guard BEFORE INSERT OR UPDATE OR DELETE ON commercial_paymentreversal FOR EACH ROW EXECUTE FUNCTION commercial_payment_guard();
CREATE TRIGGER payment_release_guard BEFORE INSERT OR UPDATE OR DELETE ON commercial_servicefinancialrelease FOR EACH ROW EXECUTE FUNCTION commercial_payment_guard();

CREATE FUNCTION commercial_payment_complete() RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE p commercial_servicepayment%ROWTYPE; pid uuid;
BEGIN
 IF TG_TABLE_NAME='commercial_servicepayment' THEN pid:=NEW.id; ELSE pid:=NEW.payment_id; END IF;
 SELECT * INTO p FROM commercial_servicepayment WHERE id=pid;
 IF NOT EXISTS (SELECT 1 FROM commercial_paymentallocation a WHERE a.payment_id=pid AND a.invoice_id=p.invoice_id AND a.amount=p.amount)
  OR NOT EXISTS (SELECT 1 FROM commercial_servicepaymentreceipt r WHERE r.payment_id=pid AND r.amount=p.amount)
  OR ((p.status='VOIDED') IS DISTINCT FROM EXISTS (SELECT 1 FROM commercial_paymentreversal v WHERE v.payment_id=pid AND v.amount=p.amount)) THEN
  RAISE EXCEPTION 'Payment, allocation, receipt and reversal must commit coherently' USING ERRCODE='23514';
 END IF;
 RETURN NULL;
END $$;
CREATE CONSTRAINT TRIGGER payment_complete AFTER INSERT OR UPDATE ON commercial_servicepayment
 DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION commercial_payment_complete();
CREATE CONSTRAINT TRIGGER payment_reversal_complete AFTER INSERT ON commercial_paymentreversal
 DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION commercial_payment_complete();
"""

REVERSE = """
DROP TRIGGER payment_reversal_complete ON commercial_paymentreversal;
DROP TRIGGER payment_complete ON commercial_servicepayment;
DROP FUNCTION commercial_payment_complete();
DROP TRIGGER payment_release_guard ON commercial_servicefinancialrelease;
DROP TRIGGER payment_reversal_guard ON commercial_paymentreversal;
DROP TRIGGER payment_receipt_guard ON commercial_servicepaymentreceipt;
DROP TRIGGER payment_allocation_guard ON commercial_paymentallocation;
DROP TRIGGER payment_guard ON commercial_servicepayment;
DROP FUNCTION commercial_payment_guard();
"""


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