USE csc_management;

ALTER TABLE applications
ADD COLUMN paid_amount DECIMAL(10,2) NOT NULL DEFAULT 0 AFTER amount,
ADD COLUMN due_amount DECIMAL(10,2) NOT NULL DEFAULT 0 AFTER paid_amount,
ADD COLUMN payment_status ENUM('UNPAID','PARTIAL','PAID') NOT NULL DEFAULT 'UNPAID' AFTER due_amount;

UPDATE applications
SET paid_amount = LEAST(amount, COALESCE((
  SELECT SUM(t.amount) FROM transactions t
  WHERE t.application_id=applications.id AND t.type='income'
),0)),
due_amount = GREATEST(amount - COALESCE((
  SELECT SUM(t.amount) FROM transactions t
  WHERE t.application_id=applications.id AND t.type='income'
),0),0);

UPDATE applications
SET payment_status = CASE
 WHEN due_amount <= 0 THEN 'PAID'
 WHEN paid_amount > 0 THEN 'PARTIAL'
 ELSE 'UNPAID' END;