namespace AdminDAL.Query.FormTemplate
{
    // Backs the GB5-native rebuild of the legacy GB4 AdminBLL.FormTemplate — 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/FormTemplate/FormTemplate.cs) is reference-only.
    //
    // Column names here match the REAL MFORMTEMPLATE table (confirmed against a live GB5 dev
    // database at 217.216.78.142, GB5DEMO/BASICDEV/MASTERDEV/TRANSDEV — all identical, and
    // matching the legacy UNISOFTGB4 schema too) — short names (REMARKS, FORMTYPE,
    // DATATABLETYPE, SORTORDER, STATUS, SOURCETYPE, FORMTEMPLATEDATA), not the longer
    // entity-prefixed names FormTemplateDTO's C# properties use. Dapper binds `@PropertyName`
    // to the DTO's actual property name regardless of the underlying column name, so the DTO
    // itself did not need to be renamed — only this SQL text did. FORMTEMPLATECODE and
    // FORMTEMPLATENAME both already have real unique constraints on this table; there is no
    // PARENTMODULEID/PARENTMENUID column (menu auto-provisioning is a deliberately open,
    // not-yet-decided feature, not persisted here).
    //
    // FORMTEMPLATEID is a real IDENTITY column (confirmed live: an explicit-value INSERT fails
    // with "Cannot insert explicit value for identity column ... IDENTITY_INSERT is set to OFF")
    // — NOT an application-generated AutoNumber id, despite that being this table's family's
    // usual convention elsewhere in this codebase. SAVE_FORMTEMPLATE therefore omits the column
    // entirely and returns the new id via OUTPUT/RETURNING, exactly like MSUBSCRIPTION's
    // SAVE_SUBSCRIPTION/SAVE_SUBSCRIPTION_POSTGRESQL pair — see FormTemplateDAL.SaveFormTemplate.
    public static class FormTemplateQB
    {
        public const string GET_FORMTEMPLATE = @"SELECT
                            F.FORMTEMPLATEID AS FormTemplateId,
                            F.FORMTEMPLATECODE AS FormTemplateCode,
                            F.FORMTEMPLATENAME AS FormTemplateName,
                            F.REMARKS AS FormTemplateRemarks,
                            F.FORMTYPE AS FormTemplateFormType,
                            F.MODULEID AS ModuleId,
                            M.MODULECODE AS ModuleCode,
                            M.MODULENAME AS ModuleName,
                            F.FORMNATUREID AS FormNatureId,
                            F.FORMTEMPLATETYPEID AS FormTemplateTypeId,
                            F.FORMTEMPLATEPROGRAMID AS FormTemplateProgarmId,
                            F.DATATABLETYPE AS FormTemplateDataTableType,
                            F.ENTITYID AS EntityId,
                            F.FORMTEMPLATEDATA AS FormTemplateFormTemplateData,
                            F.HEADERPARAMETERSETID AS HeaderParameterSetId,
                            F.FOOTERPARAMETERSETID AS FooterParameterSetId,
                            F.SOURCETYPE AS FormTemplateSourceType,
                            F.SORTORDER AS FormTemplateSortOrder,
                            F.STATUS AS FormTemplateStatus,
                            F.VERSION AS FormTemplateVersion,
                            F.CREATEDBYID AS FormTemplateCreatedById,
                            F.CREATEDON AS FormTemplateCreatedOn,
                            F.MODIFIEDBYID AS FormTemplateModifiedById,
                            F.MODIFIEDON AS FormTemplateModifiedOn,
                            F.TENANTID AS TenantId
                            FROM MFORMTEMPLATE F
                            LEFT JOIN MMODULE M ON F.MODULEID = M.MODULEID
                            WHERE F.FORMTEMPLATEID = @formtemplateid;
                            ";

        public const string GET_FORMTEMPLATE_SELECTLIST = @"SELECT
                            FORMTEMPLATEID AS Id,
                            FORMTEMPLATECODE AS Code,
                            FORMTEMPLATENAME AS Name
                            FROM MFORMTEMPLATE
                            WHERE STATUS = 1
                            ";

        public const string SAVE_FORMTEMPLATE = @"INSERT
                            INTO MFORMTEMPLATE (
                                 FORMTEMPLATECODE,
                                 FORMTEMPLATENAME,
                                 REMARKS,
                                 FORMTYPE,
                                 MODULEID,
                                 FORMNATUREID,
                                 FORMTEMPLATETYPEID,
                                 FORMTEMPLATEPROGRAMID,
                                 DATATABLETYPE,
                                 ENTITYID,
                                 FORMTEMPLATEDATA,
                                 HEADERPARAMETERSETID,
                                 FOOTERPARAMETERSETID,
                                 SOURCETYPE,
                                 SORTORDER,
                                 STATUS,
                                 VERSION,
                                 CREATEDBYID,
                                 CREATEDON,
                                 MODIFIEDBYID,
                                 MODIFIEDON,
                                 TENANTID
                            ) OUTPUT Inserted.FORMTEMPLATEID
                            VALUES (
                                 @FormTemplateCode,
                                 @FormTemplateName,
                                 @FormTemplateRemarks,
                                 @FormTemplateFormType,
                                 @ModuleId,
                                 @FormNatureId,
                                 @FormTemplateTypeId,
                                 @FormTemplateProgarmId,
                                 @FormTemplateDataTableType,
                                 @EntityId,
                                 @FormTemplateFormTemplateData,
                                 @HeaderParameterSetId,
                                 @FooterParameterSetId,
                                 @FormTemplateSourceType,
                                 @FormTemplateSortOrder,
                                 @FormTemplateStatus,
                                 @FormTemplateVersion,
                                 @FormTemplateCreatedById,
                                 @FormTemplateCreatedOn,
                                 @FormTemplateModifiedById,
                                 @FormTemplateModifiedOn,
                                 @TenantId
                            );
                            ";

        public const string SAVE_FORMTEMPLATE_POSTGRESQL = @"INSERT
                            INTO MFORMTEMPLATE (
                                 FORMTEMPLATECODE,
                                 FORMTEMPLATENAME,
                                 REMARKS,
                                 FORMTYPE,
                                 MODULEID,
                                 FORMNATUREID,
                                 FORMTEMPLATETYPEID,
                                 FORMTEMPLATEPROGRAMID,
                                 DATATABLETYPE,
                                 ENTITYID,
                                 FORMTEMPLATEDATA,
                                 HEADERPARAMETERSETID,
                                 FOOTERPARAMETERSETID,
                                 SOURCETYPE,
                                 SORTORDER,
                                 STATUS,
                                 VERSION,
                                 CREATEDBYID,
                                 CREATEDON,
                                 MODIFIEDBYID,
                                 MODIFIEDON,
                                 TENANTID
                            ) VALUES (
                                 @FormTemplateCode,
                                 @FormTemplateName,
                                 @FormTemplateRemarks,
                                 @FormTemplateFormType,
                                 @ModuleId,
                                 @FormNatureId,
                                 @FormTemplateTypeId,
                                 @FormTemplateProgarmId,
                                 @FormTemplateDataTableType,
                                 @EntityId,
                                 @FormTemplateFormTemplateData,
                                 @HeaderParameterSetId,
                                 @FooterParameterSetId,
                                 @FormTemplateSourceType,
                                 @FormTemplateSortOrder,
                                 @FormTemplateStatus,
                                 @FormTemplateVersion,
                                 @FormTemplateCreatedById,
                                 @FormTemplateCreatedOn,
                                 @FormTemplateModifiedById,
                                 @FormTemplateModifiedOn,
                                 @TenantId
                            ) RETURNING FORMTEMPLATEID;
                            ";

        public const string UPDATE_FORMTEMPLATE = @"UPDATE
                            MFORMTEMPLATE SET
                            FORMTEMPLATENAME = @FormTemplateName,
                            REMARKS = @FormTemplateRemarks,
                            FORMTYPE = @FormTemplateFormType,
                            MODULEID = @ModuleId,
                            FORMNATUREID = @FormNatureId,
                            FORMTEMPLATETYPEID = @FormTemplateTypeId,
                            FORMTEMPLATEPROGRAMID = @FormTemplateProgarmId,
                            DATATABLETYPE = @FormTemplateDataTableType,
                            ENTITYID = @EntityId,
                            FORMTEMPLATEDATA = @FormTemplateFormTemplateData,
                            HEADERPARAMETERSETID = @HeaderParameterSetId,
                            FOOTERPARAMETERSETID = @FooterParameterSetId,
                            SORTORDER = @FormTemplateSortOrder,
                            STATUS = @FormTemplateStatus,
                            MODIFIEDBYID = @FormTemplateModifiedById,
                            MODIFIEDON = @FormTemplateModifiedOn
                            WHERE FORMTEMPLATEID = @FormTemplateId;
                            ";

        public const string DELETE_FORMTEMPLATE = @"UPDATE MFORMTEMPLATE
                            SET STATUS = 2
                            WHERE FORMTEMPLATEID = @formtemplateid;
                            ";

        // Existence check used before generating the D_<Code> dynamic table, so a duplicate
        // FormTemplateCode is rejected as a real validation error rather than silently
        // reusing (or colliding with) another form's table. FORMTEMPLATECODE already has a real
        // unique constraint on the table, so this is a defense-in-depth check, not the only one.
        public const string GET_FORMTEMPLATE_BY_CODE = @"SELECT
                            FORMTEMPLATEID AS FormTemplateId,
                            FORMTEMPLATECODE AS FormTemplateCode,
                            DATATABLETYPE AS FormTemplateDataTableType,
                            FORMTEMPLATEDATA AS FormTemplateFormTemplateData
                            FROM MFORMTEMPLATE
                            WHERE FORMTEMPLATECODE = @formtemplatecode;
                            ";
    }
}