-- =====================================================================
-- Update 006 (2026-10-02): billing address entered on the payment page.
-- Additive only: one new table and two new procedures. Safe to re-run.
-- The address of an APPROVED, not-flagged payment also becomes the account
-- address (AddressLine1/2, City, State, Zip), as ezcalls.com's add-funds page
-- does. The page shows the account address first, so the customer only
-- changes it on purpose. Name, Phone, CountryID and Description are never
-- touched (CountryID is keyed by dialing code, not ISO).
-- The payment page keeps working if this has not been run yet (the save is
-- skipped and logged as billing_address_not_saved).
-- =====================================================================

USE `prepaid`;

CREATE TABLE IF NOT EXISTS `afd_BillingAddress` (
  `TransactionLogID` bigint unsigned NOT NULL,
  `Street`           varchar(100) NOT NULL DEFAULT '',
  `City`             varchar(60)  NOT NULL DEFAULT '',
  `State`            varchar(40)  NOT NULL DEFAULT '',
  `PostalCode`       varchar(12)  NOT NULL DEFAULT '',
  `Country`          char(2)      NOT NULL DEFAULT '',
  `CreatedAt`        datetime     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`TransactionLogID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DELIMITER ;;

DROP PROCEDURE IF EXISTS `afd_BillingAddress_Save`;;
CREATE PROCEDURE `afd_BillingAddress_Save`(
  IN p_TransactionLogID bigint, IN p_Street varchar(100), IN p_City varchar(60),
  IN p_State varchar(40), IN p_PostalCode varchar(12), IN p_Country char(2))
BEGIN
  INSERT INTO afd_BillingAddress (TransactionLogID, Street, City, State, PostalCode, Country)
  VALUES (p_TransactionLogID, p_Street, p_City, p_State, p_PostalCode, p_Country)
  ON DUPLICATE KEY UPDATE Street = VALUES(Street), City = VALUES(City), State = VALUES(State),
                          PostalCode = VALUES(PostalCode), Country = VALUES(Country);
END ;;

DROP PROCEDURE IF EXISTS `afd_Account_SaveAddress`;;
CREATE PROCEDURE `afd_Account_SaveAddress`(
  IN p_AccountID int, IN p_Street varchar(100), IN p_Street2 varchar(100),
  IN p_City varchar(45), IN p_State varchar(20), IN p_Zip varchar(10))
BEGIN
  UPDATE Account
     SET AddressLine1 = p_Street, AddressLine2 = p_Street2, City = p_City, State = p_State, Zip = p_Zip
   WHERE ID = p_AccountID;
  SELECT ROW_COUNT() AS Updated;
END ;;

DELIMITER ;

-- Only if your DB user's rights are granted procedure by procedure:
-- GRANT EXECUTE ON PROCEDURE `prepaid`.`afd_BillingAddress_Save` TO 'your_user'@'your_host';
-- GRANT EXECUTE ON PROCEDURE `prepaid`.`afd_Account_SaveAddress` TO 'your_user'@'your_host';

-- Staff: a payment with its address and risk signals
-- SELECT r.*, b.Street, b.City, b.State, b.PostalCode, b.Country
-- FROM afd_RiskSignal r LEFT JOIN afd_BillingAddress b ON b.TransactionLogID = r.TransactionLogID
-- ORDER BY r.CreatedAt DESC LIMIT 20;