-- Version 2: a customer can have several payment plans over time ("new loan for an existing customer").
--
-- The customer row keeps the person: identity, contact details, BVN/NIN fingerprint, the Monnify
-- account number (shared by all their plans), their current agent and their registration status
-- (customers.approval_status: approved once their first plan was approved).
-- Each plan (device, price, deposit, schedule, sale details, guarantor, approval) is a row in `loans`.
-- Existing customers become plan 1 with the same id, so every existing record links up 1:1.

CREATE TABLE loans (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(20) NULL,
  customer_id INT UNSIGNED NOT NULL,
  -- The agent who made this sale (earns commission on its repayments)
  agent_id INT UNSIGNED NOT NULL,
  -- NULL once a rejected application's device went back to stock
  device_id INT UNSIGNED NULL,
  product_id INT UNSIGNED NOT NULL,
  total_cost DECIMAL(14,2) NOT NULL,
  initial_deposit DECIMAL(14,2) NOT NULL,
  installment_amount DECIMAL(14,2) NOT NULL,
  frequency ENUM('daily', 'weekly', 'monthly') NOT NULL,
  plan_periods SMALLINT UNSIGNED NULL,
  start_date DATE NOT NULL,
  sale_reference VARCHAR(30) NULL,
  sale_receipt_number VARCHAR(60) NULL,
  sale_notes VARCHAR(500) NULL,
  guarantor_required TINYINT(1) NOT NULL DEFAULT 0,
  approval_status ENUM('pending', 'approved', 'rejected') NOT NULL DEFAULT 'pending',
  approval_decided_at DATETIME NULL,
  approval_decided_by INT UNSIGNED NULL,
  rejection_reason VARCHAR(500) NULL,
  -- The customer's request this plan came from, if any
  request_id INT UNSIGNED NULL,
  created_by_type ENUM('admin', 'agent', 'system') NOT NULL DEFAULT 'agent',
  created_by_id INT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_loans_code (code),
  UNIQUE KEY uq_loans_device (device_id),
  UNIQUE KEY uq_loans_sale_reference (sale_reference),
  KEY ix_loans_customer (customer_id, id),
  KEY ix_loans_agent (agent_id),
  KEY ix_loans_approval (approval_status, created_at),
  CONSTRAINT fk_loans_customer FOREIGN KEY (customer_id) REFERENCES customers (id),
  CONSTRAINT fk_loans_agent FOREIGN KEY (agent_id) REFERENCES agents (id),
  CONSTRAINT fk_loans_device FOREIGN KEY (device_id) REFERENCES devices (id),
  CONSTRAINT fk_loans_product FOREIGN KEY (product_id) REFERENCES products (id),
  CONSTRAINT fk_loans_decided_by FOREIGN KEY (approval_decided_by) REFERENCES admins (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO loans (id, code, customer_id, agent_id, device_id, product_id, total_cost, initial_deposit, installment_amount, frequency,
                   plan_periods, start_date, sale_reference, sale_receipt_number, sale_notes, guarantor_required, approval_status,
                   approval_decided_at, approval_decided_by, rejection_reason, created_by_type, created_by_id, created_at)
  SELECT id, CONCAT('PLN-', LPAD(id, 5, '0')), id, agent_id, device_id, product_id, total_cost, initial_deposit, installment_amount, frequency,
         plan_periods, start_date, sale_reference, sale_receipt_number, sale_notes, guarantor_required, approval_status,
         approval_decided_at, approval_decided_by, rejection_reason, created_by_type, created_by_id, created_at
  FROM customers;

-- A returning customer asks for another device from their account; their agent completes the sale.
CREATE TABLE plan_requests (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  customer_id INT UNSIGNED NOT NULL,
  product_id INT UNSIGNED NOT NULL,
  frequency ENUM('daily', 'weekly', 'monthly') NOT NULL,
  plan_periods SMALLINT UNSIGNED NOT NULL,
  -- The deposit the customer expects to pay (the agent confirms the real one)
  initial_deposit DECIMAL(14,2) NOT NULL,
  note VARCHAR(500) NULL,
  -- open: waiting for the agent; started: a plan was submitted; declined: by staff; cancelled: by the customer
  status ENUM('open', 'started', 'declined', 'cancelled') NOT NULL DEFAULT 'open',
  loan_id INT UNSIGNED NULL,
  handled_by_type ENUM('admin', 'agent', 'customer') NULL,
  handled_by_id INT UNSIGNED NULL,
  handled_by_name VARCHAR(120) NULL,
  handled_at DATETIME NULL,
  decline_reason VARCHAR(500) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY ix_plan_requests_customer (customer_id, status),
  KEY ix_plan_requests_status (status, created_at),
  CONSTRAINT fk_plan_requests_customer FOREIGN KEY (customer_id) REFERENCES customers (id),
  CONSTRAINT fk_plan_requests_product FOREIGN KEY (product_id) REFERENCES products (id),
  CONSTRAINT fk_plan_requests_loan FOREIGN KEY (loan_id) REFERENCES loans (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE loans ADD CONSTRAINT fk_loans_request FOREIGN KEY (request_id) REFERENCES plan_requests (id);

-- Payments belong to a plan. "credit_transfer" moves an overpayment from a finished plan to the next one.
ALTER TABLE payments
  MODIFY method ENUM('deposit', 'cash', 'bank_transfer', 'monnify_transfer', 'monnify_card', 'reversal', 'credit_transfer') NOT NULL,
  ADD COLUMN loan_id INT UNSIGNED NULL AFTER customer_id;
UPDATE payments SET loan_id = customer_id;
ALTER TABLE payments
  MODIFY loan_id INT UNSIGNED NOT NULL,
  ADD KEY ix_payments_loan (loan_id, paid_on),
  ADD CONSTRAINT fk_payments_loan FOREIGN KEY (loan_id) REFERENCES loans (id);

ALTER TABLE payment_intents ADD COLUMN loan_id INT UNSIGNED NULL AFTER customer_id;
UPDATE payment_intents SET loan_id = customer_id;
ALTER TABLE payment_intents
  MODIFY loan_id INT UNSIGNED NOT NULL,
  ADD CONSTRAINT fk_intents_loan FOREIGN KEY (loan_id) REFERENCES loans (id);

-- Reminders are about one plan's installments.
ALTER TABLE payment_reminders ADD COLUMN loan_id INT UNSIGNED NULL AFTER customer_id, ADD KEY ix_reminders_customer (customer_id);
UPDATE payment_reminders SET loan_id = customer_id;
ALTER TABLE payment_reminders
  MODIFY loan_id INT UNSIGNED NOT NULL,
  DROP INDEX uq_reminders_once,
  ADD UNIQUE KEY uq_reminders_once (loan_id, kind, due_date),
  ADD CONSTRAINT fk_reminders_loan FOREIGN KEY (loan_id) REFERENCES loans (id);

-- Each plan is its own application, with its own history.
ALTER TABLE customer_approval_events ADD COLUMN loan_id INT UNSIGNED NULL AFTER customer_id;
UPDATE customer_approval_events SET loan_id = customer_id;
ALTER TABLE customer_approval_events
  MODIFY loan_id INT UNSIGNED NOT NULL,
  ADD KEY ix_approval_events_loan (loan_id, id),
  ADD CONSTRAINT fk_approval_events_loan FOREIGN KEY (loan_id) REFERENCES loans (id);

-- The customer signs the agreement again for every new plan.
ALTER TABLE customer_agreements ADD COLUMN loan_id INT UNSIGNED NULL AFTER customer_id;
UPDATE customer_agreements SET loan_id = customer_id;
ALTER TABLE customer_agreements
  MODIFY loan_id INT UNSIGNED NOT NULL,
  ADD KEY ix_agreements_loan (loan_id, id),
  ADD CONSTRAINT fk_agreements_loan FOREIGN KEY (loan_id) REFERENCES loans (id);

-- A guarantor guarantees one plan (one guarantor per plan when the sale needs one).
ALTER TABLE guarantors ADD COLUMN loan_id INT UNSIGNED NULL AFTER customer_id, ADD KEY ix_guarantors_customer (customer_id);
UPDATE guarantors SET loan_id = customer_id;
ALTER TABLE guarantors
  MODIFY loan_id INT UNSIGNED NOT NULL,
  DROP INDEX uq_guarantors_customer,
  ADD UNIQUE KEY uq_guarantors_loan (loan_id),
  ADD CONSTRAINT fk_guarantors_loan FOREIGN KEY (loan_id) REFERENCES loans (id);

ALTER TABLE guarantor_agreements ADD COLUMN loan_id INT UNSIGNED NULL AFTER customer_id;
UPDATE guarantor_agreements SET loan_id = customer_id;
ALTER TABLE guarantor_agreements
  MODIFY loan_id INT UNSIGNED NOT NULL,
  ADD KEY ix_guarantor_agreements_loan (loan_id, id),
  ADD CONSTRAINT fk_guarantor_agreements_loan FOREIGN KEY (loan_id) REFERENCES loans (id);

-- Device photos and guarantor documents belong to a plan; the customer's photo, ID and
-- supporting documents belong to the customer and are shown with every plan.
ALTER TABLE stored_files ADD COLUMN loan_id INT UNSIGNED NULL AFTER customer_id, ADD KEY ix_files_loan (loan_id);
UPDATE stored_files SET loan_id = customer_id
  WHERE customer_id IS NOT NULL AND kind IN ('device_photo', 'guarantor_photo', 'guarantor_id_document', 'guarantor_document');
ALTER TABLE stored_files ADD CONSTRAINT fk_files_loan FOREIGN KEY (loan_id) REFERENCES loans (id);

ALTER TABLE stock_movements ADD COLUMN loan_id INT UNSIGNED NULL AFTER customer_id;
UPDATE stock_movements SET loan_id = customer_id WHERE customer_id IS NOT NULL;
ALTER TABLE stock_movements ADD CONSTRAINT fk_movements_loan FOREIGN KEY (loan_id) REFERENCES loans (id);

-- The plan columns now live in `loans`.
ALTER TABLE customers
  DROP FOREIGN KEY fk_customers_device,
  DROP FOREIGN KEY fk_customers_product,
  DROP FOREIGN KEY fk_customers_decided_by;
ALTER TABLE customers
  DROP INDEX uq_customers_device,
  DROP INDEX uq_customers_sale_reference;
ALTER TABLE customers
  DROP COLUMN device_id,
  DROP COLUMN product_id,
  DROP COLUMN total_cost,
  DROP COLUMN initial_deposit,
  DROP COLUMN installment_amount,
  DROP COLUMN frequency,
  DROP COLUMN plan_periods,
  DROP COLUMN start_date,
  DROP COLUMN sale_reference,
  DROP COLUMN sale_receipt_number,
  DROP COLUMN sale_notes,
  DROP COLUMN guarantor_required,
  DROP COLUMN approval_decided_at,
  DROP COLUMN approval_decided_by,
  DROP COLUMN rejection_reason;