from django.db import migrations


SQL = r"""
CREATE FUNCTION commercial_invoice_history_guard() RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE parent_id uuid; parent_status text; case_id uuid; quote_id uuid;
BEGIN
  IF TG_OP = 'DELETE' THEN
    RAISE EXCEPTION 'Invoice history cannot be deleted' USING ERRCODE = '23514';
  END IF;
  IF TG_TABLE_NAME = 'commercial_serviceinvoice' THEN
    IF TG_OP = 'UPDATE' THEN
      IF OLD.status = 'FINALIZED' OR NEW.id <> OLD.id OR NEW.service_case_id <> OLD.service_case_id
        OR NEW.service_center_id <> OLD.service_center_id OR NEW.number <> OLD.number
        OR NEW.created_by_id <> OLD.created_by_id OR NEW.created_at <> OLD.created_at THEN
        RAISE EXCEPTION 'Immutable invoice identity/history' USING ERRCODE = '23514';
      END IF;
    ELSIF NEW.status <> 'DRAFT' THEN
      RAISE EXCEPTION 'Invoices begin as drafts' USING ERRCODE = '23514';
    END IF;
    IF NOT EXISTS (SELECT 1 FROM service_servicecase c JOIN commercial_quotationfamily f ON f.service_case_id=c.id
        JOIN commercial_servicequotation q ON q.family_id=f.id
        WHERE c.id=NEW.service_case_id AND c.service_center_id=NEW.service_center_id AND q.id=NEW.quotation_id
        AND q.currency=NEW.currency AND q.status='APPROVED' AND q.is_current) THEN
      RAISE EXCEPTION 'Invoice must reference its current approved commercial source' USING ERRCODE = '23514';
    END IF;
    RETURN NEW;
  END IF;
  IF TG_TABLE_NAME = 'commercial_invoiceline' THEN
    parent_id := NEW.invoice_id;
    IF TG_OP = 'UPDATE' AND ((to_jsonb(NEW)-'is_active'-'updated_at') IS DISTINCT FROM
      (to_jsonb(OLD)-'is_active'-'updated_at') OR NOT OLD.is_active OR NEW.is_active) THEN
      RAISE EXCEPTION 'Retain invoice line evidence; only retirement is supported' USING ERRCODE = '23514';
    END IF;
  ELSE
    IF TG_OP = 'UPDATE' THEN
      RAISE EXCEPTION 'Immutable invoice allocation evidence' USING ERRCODE = '23514';
    END IF;
    SELECT invoice_id INTO parent_id FROM commercial_invoiceline WHERE id=NEW.line_id;
  END IF;
  SELECT status,service_case_id,quotation_id INTO parent_status,case_id,quote_id
    FROM commercial_serviceinvoice WHERE id=parent_id FOR UPDATE;
  IF parent_status IS DISTINCT FROM 'DRAFT' THEN
    RAISE EXCEPTION 'Finalized invoice children are immutable' USING ERRCODE = '23514';
  END IF;
  IF TG_TABLE_NAME = 'commercial_invoiceline' AND TG_OP = 'INSERT' THEN
    IF NOT EXISTS (SELECT 1 FROM commercial_quotationline q WHERE q.id=NEW.quotation_line_id
      AND q.quotation_id=quote_id AND q.is_active AND q.kind=NEW.kind AND q.responsibility=NEW.responsibility
      AND q.unit_price=NEW.unit_price AND q.tax_rate=NEW.tax_rate AND q.tax_label=NEW.tax_label
      AND NEW.quantity<=q.quantity AND NEW.total<=q.total) THEN
      RAISE EXCEPTION 'Invoice line must preserve approved commercial terms' USING ERRCODE = '23514';
    END IF;
    IF NEW.tax <> round(NEW.taxable_amount*NEW.tax_rate/100,2) THEN
      RAISE EXCEPTION 'Invoice tax arithmetic mismatch' USING ERRCODE = '23514';
    END IF;
  ELSIF TG_TABLE_NAME = 'commercial_invoiceallocation' THEN
    IF NOT EXISTS (SELECT 1 FROM commercial_invoiceline l
      JOIN commercial_quotationline q ON q.id=l.quotation_line_id
      JOIN inventory_partsdisposition d ON d.id=NEW.consumption_id
      JOIN inventory_partsissue i ON i.id=d.issue_id
      JOIN inventory_stockreservation r ON r.id=i.reservation_id
      WHERE l.id=NEW.line_id AND l.is_active AND l.kind='PART' AND d.kind='CONSUMED'
      AND i.service_case_id=case_id AND r.spare_part_id=q.spare_part_id
      AND (q.repair_action_id IS NULL OR q.repair_action_id=d.repair_action_id)) THEN
      RAISE EXCEPTION 'Invoice allocation source mismatch' USING ERRCODE = '23514';
    END IF;
  END IF;
  RETURN NEW;
END $$;
CREATE TRIGGER invoice_history BEFORE INSERT OR UPDATE OR DELETE ON commercial_serviceinvoice
 FOR EACH ROW EXECUTE FUNCTION commercial_invoice_history_guard();
CREATE TRIGGER invoice_line_history BEFORE INSERT OR UPDATE OR DELETE ON commercial_invoiceline
 FOR EACH ROW EXECUTE FUNCTION commercial_invoice_history_guard();
CREATE TRIGGER invoice_allocation_history BEFORE INSERT OR UPDATE OR DELETE ON commercial_invoiceallocation
 FOR EACH ROW EXECUTE FUNCTION commercial_invoice_history_guard();

CREATE FUNCTION commercial_invoice_totals_guard() RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE row_id uuid; inv commercial_serviceinvoice%ROWTYPE; sums record;
BEGIN
  IF TG_TABLE_NAME='commercial_serviceinvoice' THEN row_id:=NEW.id;
  ELSIF TG_TABLE_NAME='commercial_invoiceline' THEN row_id:=NEW.invoice_id;
  ELSE SELECT invoice_id INTO row_id FROM commercial_invoiceline WHERE id=NEW.line_id;
  END IF;
  SELECT * INTO inv FROM commercial_serviceinvoice WHERE id=row_id;
  SELECT coalesce(sum(subtotal),0) s, coalesce(sum(discount),0) d, coalesce(sum(tax),0) t,
    coalesce(sum(total),0) g, coalesce(sum(customer_pay),0) c,
    coalesce(sum(warranty_covered),0) w, coalesce(sum(company_covered),0) b
    INTO sums FROM commercial_invoiceline WHERE invoice_id=row_id AND is_active;
  IF (inv.subtotal,inv.discount_total,inv.tax_total,inv.grand_total,inv.customer_pay_total,inv.warranty_covered_total,inv.company_covered_total)
    IS DISTINCT FROM (sums.s,sums.d,sums.t,sums.g,sums.c,sums.w,sums.b) THEN
    RAISE EXCEPTION 'Invoice totals must equal active line totals' USING ERRCODE = '23514';
  END IF;
  IF EXISTS (SELECT 1 FROM commercial_invoiceline l WHERE l.invoice_id=row_id AND l.is_active AND l.kind='PART'
    AND l.quantity<>(SELECT coalesce(sum(a.quantity),0) FROM commercial_invoiceallocation a WHERE a.line_id=l.id)) THEN
    RAISE EXCEPTION 'Part line must reconcile to allocation evidence' USING ERRCODE = '23514';
  END IF;
  RETURN NULL;
END $$;
CREATE CONSTRAINT TRIGGER invoice_totals AFTER INSERT OR UPDATE ON commercial_serviceinvoice
 DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION commercial_invoice_totals_guard();
CREATE CONSTRAINT TRIGGER invoice_line_totals AFTER INSERT OR UPDATE ON commercial_invoiceline
 DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION commercial_invoice_totals_guard();
CREATE CONSTRAINT TRIGGER invoice_allocation_totals AFTER INSERT ON commercial_invoiceallocation
 DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION commercial_invoice_totals_guard();
"""

REVERSE = """
DROP TRIGGER invoice_allocation_totals ON commercial_invoiceallocation;
DROP TRIGGER invoice_line_totals ON commercial_invoiceline;
DROP TRIGGER invoice_totals ON commercial_serviceinvoice;
DROP FUNCTION commercial_invoice_totals_guard();
DROP TRIGGER invoice_allocation_history ON commercial_invoiceallocation;
DROP TRIGGER invoice_line_history ON commercial_invoiceline;
DROP TRIGGER invoice_history ON commercial_serviceinvoice;
DROP FUNCTION commercial_invoice_history_guard();
"""


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