-- Access numbers for the AI line, from the existing asterisk.DNIS table (the same numbers
-- the website shows through ez_DNIS_Select). Nothing in asterisk.DNIS is changed.
--
-- afd_AccessNumberOrder   which number is given first: Priority 1 first, then 2, 3 ...
--                         (a number without a row comes after the ranked ones). Only the
--                         order: the numbers themselves stay in asterisk.DNIS, and
--                         DisplayOnline = 0 there still retires a number.
-- afd_TollFreeDisclosure  every time the AI gave a toll-free number, and to whom.
--
-- afd_AccessNumber_List     local numbers (DisplayOnline = 1, never toll-free), best first.
-- afd_AccessNumber_TollFree a toll-free number (888 first), only for an account of this
--                           partition with at least one credited payment; logged.
--                           Status: ok | no_payment | none | not_found.
--
-- Toll-free = 800, 833, 844, 855, 866, 877, 888 (the website hides 855 and 888).
-- Run as a DB admin (root, like the ez_ procedures) after 014. Safe to run again.

CREATE TABLE IF NOT EXISTS `afd_AccessNumberOrder` (
  `PartitionID` int unsigned NOT NULL,
  `DNIS`        varchar(30)  NOT NULL,
  `Priority`    int unsigned NOT NULL DEFAULT 100,
  PRIMARY KEY (`PartitionID`, `DNIS`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `afd_TollFreeDisclosure` (
  `ID`        bigint unsigned NOT NULL AUTO_INCREMENT,
  `AccountID` bigint unsigned NOT NULL,
  `DNIS`      varchar(30) NOT NULL,
  `CallerANI` varchar(20) NOT NULL DEFAULT '',
  `CallID`    varchar(64) NOT NULL DEFAULT '',
  `CreatedAt` datetime    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  KEY `Idx_afd_TollFreeDisclosure_Account` (`AccountID`, `CreatedAt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DELIMITER ;;

DROP PROCEDURE IF EXISTS `afd_AccessNumber_List`;;
CREATE PROCEDURE `afd_AccessNumber_List`(IN p_PartitionID int, IN p_ApplicationID int)
BEGIN
  -- One row per number (asterisk.DNIS can list a number once per carrier).
  SELECT d.DNIS AS DNIS, MIN(d.Location) AS Location, MIN(IFNULL(o.Priority, 1000)) AS Priority
  FROM asterisk.DNIS d
  LEFT JOIN afd_AccessNumberOrder o ON o.PartitionID = d.PartitionID AND o.DNIS = d.DNIS
  WHERE d.PartitionID = p_PartitionID
    AND d.ApplicationID = p_ApplicationID
    AND d.DisplayOnline = 1
    AND LEFT(d.DNIS, 3) NOT IN ('800', '833', '844', '855', '866', '877', '888')
  GROUP BY d.DNIS
  ORDER BY Priority, Location, DNIS;
END ;;

DROP PROCEDURE IF EXISTS `afd_AccessNumber_TollFree`;;
CREATE PROCEDURE `afd_AccessNumber_TollFree`(
  IN p_PartitionID int, IN p_ApplicationID int, IN p_AccountID bigint,
  IN p_CallerANI varchar(20), IN p_CallID varchar(64), IN p_ExcludeDNIS varchar(30))
BEGIN
  DECLARE v_DNIS varchar(30) DEFAULT NULL;

  IF NOT EXISTS (SELECT 1 FROM Account WHERE ID = p_AccountID AND PartitionID = p_PartitionID) THEN
    SELECT 'not_found' AS Status, NULL AS DNIS;
  ELSEIF NOT EXISTS (SELECT 1 FROM TransactionLog WHERE AccountID = p_AccountID AND AmountCredited > 0) THEN
    SELECT 'no_payment' AS Status, NULL AS DNIS;
  ELSE
    -- 888 first, then the order table. Never the AI line itself (p_ExcludeDNIS).
    SELECT d.DNIS INTO v_DNIS
    FROM asterisk.DNIS d
    LEFT JOIN afd_AccessNumberOrder o ON o.PartitionID = d.PartitionID AND o.DNIS = d.DNIS
    WHERE d.PartitionID = p_PartitionID
      AND d.ApplicationID = p_ApplicationID
      AND LEFT(d.DNIS, 3) IN ('800', '833', '844', '855', '866', '877', '888')
      AND d.DNIS <> p_ExcludeDNIS
    ORDER BY (LEFT(d.DNIS, 3) = '888') DESC, IFNULL(o.Priority, 1000), d.DNIS
    LIMIT 1;

    IF v_DNIS IS NULL THEN
      SELECT 'none' AS Status, NULL AS DNIS;
    ELSE
      INSERT INTO afd_TollFreeDisclosure (AccountID, DNIS, CallerANI, CallID)
      VALUES (p_AccountID, v_DNIS, p_CallerANI, p_CallID);
      SELECT 'ok' AS Status, v_DNIS AS DNIS;
    END IF;
  END IF;
END ;;

DELIMITER ;

-- Which number is given first (Priority 1), e.g.:
-- INSERT INTO afd_AccessNumberOrder (PartitionID, DNIS, Priority) VALUES (<partition>, '6786065965', 1)
--   ON DUPLICATE KEY UPDATE Priority = VALUES(Priority);
-- A Canadian number is given to callers from Canadian area codes: rank it the same way.
--
-- With per-procedure grants:
-- GRANT EXECUTE ON PROCEDURE `prepaid`.`afd_AccessNumber_List` TO 'your_user'@'your_host';
-- GRANT EXECUTE ON PROCEDURE `prepaid`.`afd_AccessNumber_TollFree` TO 'your_user'@'your_host';