namespace AdminDAL.Query.TemplateData
{
    // Backs the GB5-native rebuild of the legacy GB4 AdminBLL.TemplateData — see
    // /Users/venkatv/.claude/plans/we-have-done-a-adaptive-stroustrup.md
    // ("Generic data modeling for admin-designed forms"). The legacy GB4 source
    // (~/Downloads/GB4Solution/BLL/AdminBLL/TemplateData/TemplateDataBLL.cs) is reference-only —
    // in particular its DELETE_DYNAMICDATA/INSERT_DYNAMICDATA raw string-concatenated SQL is a
    // real SQL-injection hole that is NOT reproduced here (see TemplateDataDAL.cs, which builds
    // the D_<Code> DML via validated identifiers + real Dapper parameters instead).
    //
    // Column names here match the REAL TTEMPLATEDATA table (confirmed against a live GB5 dev
    // database at 217.216.78.142) — short names (FORMNUMBER, FORMDATE, FORMREFERENCENUMBER,
    // FORMREFERENCEDATE, TEMPLATEDATA), not the longer entity-prefixed names
    // TemplateDataDTO's C# properties use; Dapper binds `@PropertyName` to the DTO property
    // regardless of underlying column name, so only this SQL text needed correcting. TENANTID
    // is a real column (FK-constrained) — populated from LoginDTO.ClientId in TemplateDataBLL.
    //
    // TEMPLATEDATAID is a real IDENTITY column (confirmed live, same as MFORMTEMPLATE's own
    // FORMTEMPLATEID — see FormTemplateQB's header) — SAVE_TEMPLATEDATA therefore omits it from
    // the INSERT and returns the new id via OUTPUT/RETURNING, not an AutoNumber allocation.
    public static class TemplateDataQB
    {
        public const string GET_TEMPLATEDATA = @"SELECT
                            T.TEMPLATEDATAID AS TemplateDataId,
                            T.OUID AS OUId,
                            T.PERIODID AS PeriodId,
                            T.BIZTRANSACTIONTYPEID AS BIZTransactionTypeId,
                            T.FORMNUMBER AS TemplateDataFormNumber,
                            T.FORMDATE AS TemplateDataFormDate,
                            T.FORMREFERENCENUMBER AS TemplateDataFormReferenceNumber,
                            T.FORMREFERENCEDATE AS TemplateDataFormReferenceDate,
                            T.FORMTEMPLATEID AS FormTemplateId,
                            F.FORMTEMPLATECODE AS FormTemplateCode,
                            F.FORMTEMPLATENAME AS FormTemplateName,
                            F.FORMTEMPLATEDATA AS FormTemplateFormTemplateData,
                            T.OBJECTTYPEID AS ObjectTypeId,
                            T.OBJECTID AS ObjectId,
                            T.TEMPLATEDATA AS TemplateDataData,
                            T.ACTIONDETAILID AS ActionDetailId,
                            T.TASKID AS TaskId,
                            T.PARTYID AS PartyId,
                            T.PARTYBRANCHID AS PartyBranchId,
                            T.HEADERPARAMETERSETID AS HeaderParameterSetId,
                            T.FOOTERPARAMETERSETID AS FooterParameterSetId,
                            T.STATUS AS TemplateDataStatus,
                            T.VERSION AS TemplateDataVersion,
                            T.CREATEDBYID AS TemplateDataCreatedById,
                            T.CREATEDON AS TemplateDataCreatedOn,
                            T.MODIFIEDBYID AS TemplateDataModifiedById,
                            T.MODIFIEDON AS TemplateDataModifiedOn,
                            T.TENANTID AS TenantId
                            FROM TTEMPLATEDATA T
                            LEFT JOIN MFORMTEMPLATE F ON T.FORMTEMPLATEID = F.FORMTEMPLATEID
                            WHERE T.TEMPLATEDATAID = @templatedataid;
                            ";

        public const string GET_TEMPLATEDATA_SELECTLIST = @"SELECT
                            TEMPLATEDATAID AS Id,
                            FORMNUMBER AS Number,
                            FORMDATE AS Date
                            FROM TTEMPLATEDATA
                            WHERE STATUS = 1 AND FORMTEMPLATEID = @formtemplateid
                            ";

        public const string SAVE_TEMPLATEDATA = @"INSERT
                            INTO TTEMPLATEDATA (
                                 OUID,
                                 PERIODID,
                                 BIZTRANSACTIONTYPEID,
                                 FORMNUMBER,
                                 FORMDATE,
                                 FORMREFERENCENUMBER,
                                 FORMREFERENCEDATE,
                                 FORMTEMPLATEID,
                                 OBJECTTYPEID,
                                 OBJECTID,
                                 TEMPLATEDATA,
                                 ACTIONDETAILID,
                                 TASKID,
                                 PARTYID,
                                 PARTYBRANCHID,
                                 HEADERPARAMETERSETID,
                                 FOOTERPARAMETERSETID,
                                 STATUS,
                                 VERSION,
                                 CREATEDBYID,
                                 CREATEDON,
                                 MODIFIEDBYID,
                                 MODIFIEDON,
                                 TENANTID
                            ) OUTPUT Inserted.TEMPLATEDATAID
                            VALUES (
                                 @OUId,
                                 @PeriodId,
                                 @BIZTransactionTypeId,
                                 @TemplateDataFormNumber,
                                 @TemplateDataFormDate,
                                 @TemplateDataFormReferenceNumber,
                                 @TemplateDataFormReferenceDate,
                                 @FormTemplateId,
                                 @ObjectTypeId,
                                 @ObjectId,
                                 @TemplateDataData,
                                 @ActionDetailId,
                                 @TaskId,
                                 @PartyId,
                                 @PartyBranchId,
                                 @HeaderParameterSetId,
                                 @FooterParameterSetId,
                                 @TemplateDataStatus,
                                 @TemplateDataVersion,
                                 @TemplateDataCreatedById,
                                 @TemplateDataCreatedOn,
                                 @TemplateDataModifiedById,
                                 @TemplateDataModifiedOn,
                                 @TenantId
                            );
                            ";

        public const string SAVE_TEMPLATEDATA_POSTGRESQL = @"INSERT
                            INTO TTEMPLATEDATA (
                                 OUID,
                                 PERIODID,
                                 BIZTRANSACTIONTYPEID,
                                 FORMNUMBER,
                                 FORMDATE,
                                 FORMREFERENCENUMBER,
                                 FORMREFERENCEDATE,
                                 FORMTEMPLATEID,
                                 OBJECTTYPEID,
                                 OBJECTID,
                                 TEMPLATEDATA,
                                 ACTIONDETAILID,
                                 TASKID,
                                 PARTYID,
                                 PARTYBRANCHID,
                                 HEADERPARAMETERSETID,
                                 FOOTERPARAMETERSETID,
                                 STATUS,
                                 VERSION,
                                 CREATEDBYID,
                                 CREATEDON,
                                 MODIFIEDBYID,
                                 MODIFIEDON,
                                 TENANTID
                            ) VALUES (
                                 @OUId,
                                 @PeriodId,
                                 @BIZTransactionTypeId,
                                 @TemplateDataFormNumber,
                                 @TemplateDataFormDate,
                                 @TemplateDataFormReferenceNumber,
                                 @TemplateDataFormReferenceDate,
                                 @FormTemplateId,
                                 @ObjectTypeId,
                                 @ObjectId,
                                 @TemplateDataData,
                                 @ActionDetailId,
                                 @TaskId,
                                 @PartyId,
                                 @PartyBranchId,
                                 @HeaderParameterSetId,
                                 @FooterParameterSetId,
                                 @TemplateDataStatus,
                                 @TemplateDataVersion,
                                 @TemplateDataCreatedById,
                                 @TemplateDataCreatedOn,
                                 @TemplateDataModifiedById,
                                 @TemplateDataModifiedOn,
                                 @TenantId
                            ) RETURNING TEMPLATEDATAID;
                            ";

        public const string UPDATE_TEMPLATEDATA = @"UPDATE
                            TTEMPLATEDATA SET
                            OUID = @OUId,
                            PERIODID = @PeriodId,
                            BIZTRANSACTIONTYPEID = @BIZTransactionTypeId,
                            FORMREFERENCENUMBER = @TemplateDataFormReferenceNumber,
                            FORMREFERENCEDATE = @TemplateDataFormReferenceDate,
                            OBJECTTYPEID = @ObjectTypeId,
                            OBJECTID = @ObjectId,
                            TEMPLATEDATA = @TemplateDataData,
                            ACTIONDETAILID = @ActionDetailId,
                            TASKID = @TaskId,
                            PARTYID = @PartyId,
                            PARTYBRANCHID = @PartyBranchId,
                            HEADERPARAMETERSETID = @HeaderParameterSetId,
                            FOOTERPARAMETERSETID = @FooterParameterSetId,
                            STATUS = @TemplateDataStatus,
                            MODIFIEDBYID = @TemplateDataModifiedById,
                            MODIFIEDON = @TemplateDataModifiedOn
                            WHERE TEMPLATEDATAID = @TemplateDataId;
                            ";

        public const string DELETE_TEMPLATEDATA = @"UPDATE TTEMPLATEDATA
                            SET STATUS = 2
                            WHERE TEMPLATEDATAID = @templatedataid;
                            ";
    }
}