namespace GB5Shared.Query.VoucherNumber
{
    /// <summary>
    /// SQL constants for voucher/task number generation against MBIZTRANSACTIONTYPE
    /// and MBIZTRANSACTIONKEYS tables.
    /// Mirrors the logic of GB4's VnoGenerationDLL.GetNextVnoWithSameSession.
    /// </summary>
    public static class VoucherNumberQB
    {
        /// <summary>
        /// Loads number-generation configuration for a given BizTransactionType.
        /// Required index: MBIZTRANSACTIONTYPE.BIZTRANSACTIONTYPEID (PK — already exists).
        /// </summary>
        public const string GET_BIZTRANSACTIONTYPE_CONFIG = @"
            SELECT
                NOGENERATIONTYPE        AS NOGenerationType,
                NOGENERATIONPERIODTYPE  AS NOGenerationPeriodType,
                NUMBERSIZE              AS NumberSize,
                ISNULL(PREFIX,  '')     AS Prefix,
                ISNULL(SUFFIX,  '')     AS Suffix,
                ISNULL(SYSTEMPREFIX,'') AS SystemPrefix,
                ISNULL(SYSTEMSUFFIX,'') AS SystemSuffix
            FROM MBIZTRANSACTIONTYPE
            WHERE BIZTRANSACTIONTYPEID = @BizTransactionTypeId";

        /// <summary>
        /// Atomically increments LASTNO by 1 and returns the new value via OUTPUT.
        /// Using UPDATE ... OUTPUT avoids a separate SELECT, eliminating the read-then-write
        /// race condition that can occur when two concurrent requests both read the same LASTNO.
        /// SQL Server serialises concurrent UPDATEs on the same row automatically.
        ///
        /// Returns 0 rows when no matching period slot exists yet (first number for the period);
        /// the caller must INSERT a new row and return LASTNO = 1.
        ///
        /// Period sentinel values (set by VoucherNumberService.BuildPeriodValues):
        ///   Yearly     → BIZTRANSACTIONYEAR = -2, BIZTRANSACTIONMONTH = 0, BIZTRANSACTIONDAY = 0
        ///   Monthly    → BIZTRANSACTIONYEAR = actual year, BIZTRANSACTIONMONTH = actual month, BIZTRANSACTIONDAY = 0
        ///   Daily      → BIZTRANSACTIONYEAR = actual year, BIZTRANSACTIONMONTH = actual month, BIZTRANSACTIONDAY = actual day
        ///   Continuous → BIZTRANSACTIONYEAR = -1, BIZTRANSACTIONMONTH = 0, BIZTRANSACTIONDAY = 0
        ///
        /// Required index: MBIZTRANSACTIONKEYS (BIZTRANSACTIONTYPEID, BIZTRANSACTIONYEAR, BIZTRANSACTIONMONTH, BIZTRANSACTIONDAY).
        /// </summary>
        public const string INCREMENT_LASTNO = @"
            UPDATE MBIZTRANSACTIONKEYS
               SET LASTNO = LASTNO + 1
            OUTPUT INSERTED.LASTNO
            WHERE BIZTRANSACTIONTYPEID  = @BizTransactionTypeId
              AND BIZTRANSACTIONYEAR    = @Year
              AND BIZTRANSACTIONMONTH   = @Month
              AND BIZTRANSACTIONDAY     = @Day";

        /// <summary>
        /// Inserts a new BizTransactionKeys row when none exists for the period slot.
        /// LASTNO starts at 1.  The row ID is pre-allocated via AutoNumber("BIZTRANSACTIONKEYS").
        /// Must execute within the same transaction as the entity save.
        /// </summary>
        public const string INSERT_BIZTRANSACTIONKEYS = @"
            INSERT INTO MBIZTRANSACTIONKEYS
                (BIZTRANSACTIONKEYSID, BIZTRANSACTIONTYPEID,
                 BIZTRANSACTIONYEAR, BIZTRANSACTIONMONTH, BIZTRANSACTIONDAY,
                 PREFIX, SUFFIX, SYSTEMPREFIX,
                 LASTNO)
            VALUES
                (@BizTransactionKeysId, @BizTransactionTypeId,
                 @Year, @Month, @Day,
                 @Prefix, @Suffix, @SystemPrefix,
                 1)";
    }
}