namespace FrameworkDAL.Query.MetaReport
{
    /// <summary>
    /// SQL constants for MMETAREPORT / MMETAREPORTVARIANT / MMETAREPORTASPECT /
    /// MMETAREPORTASPECTOPTION / MMETAREPORTOPTIONVARIANT. Every query here uses only
    /// ANSI-standard constructs, matching MenuGroupQB.cs's convention — a single constant is
    /// valid on both SQL Server and PostgreSQL, no _POSTGRE-suffixed duplicates needed.
    ///
    /// Required indexes (mirrored in the schema migration):
    ///   MMETAREPORTVARIANT       (METAREPORTID)
    ///   MMETAREPORTASPECT        (METAREPORTID)
    ///   MMETAREPORTASPECTOPTION  (ASPECTID)
    ///   MMETAREPORTOPTIONVARIANT (VARIANTID)
    /// </summary>
    public static class MetaReportQB
    {
        // ── Reads ────────────────────────────────────────────────────────────────────

        public const string GET_METAREPORT = @"
SELECT
    METAREPORTID AS MetaReportId, METAREPORTNAME AS MetaReportName, DESCRIPTION AS Description,
    STATUS AS Status, VERSION AS Version,
    CREATEDBYID AS CreatedById, CREATEDON AS CreatedOn, MODIFIEDBYID AS ModifiedById, MODIFIEDON AS ModifiedOn
FROM MMETAREPORT
WHERE METAREPORTID = @MetaReportId";

        public const string GET_METAREPORT_LIST = @"
SELECT METAREPORTID AS MetaReportId, METAREPORTNAME AS MetaReportName, DESCRIPTION AS Description, STATUS AS Status
FROM MMETAREPORT
WHERE STATUS <> 2
ORDER BY METAREPORTNAME";

        public const string GET_VARIANTS_BY_METAREPORTID = @"
SELECT
    VARIANTID AS VariantId, METAREPORTID AS MetaReportId, VARIANTNAME AS VariantName,
    MENUID AS MenuId, REPORTID AS ReportId, SORTORDER AS SortOrder,
    CREATEDBYID AS CreatedById, CREATEDON AS CreatedOn, MODIFIEDBYID AS ModifiedById, MODIFIEDON AS ModifiedOn
FROM MMETAREPORTVARIANT
WHERE METAREPORTID = @MetaReportId
ORDER BY SORTORDER";

        public const string GET_ASPECTS_BY_METAREPORTID = @"
SELECT
    ASPECTID AS AspectId, METAREPORTID AS MetaReportId, ASPECTNAME AS AspectName, ASPECTTYPE AS AspectType,
    ISMANDATORY AS IsMandatory, DISPLAYORDER AS DisplayOrder, TABGROUP AS TabGroup, TOOLTIP AS Tooltip,
    CREATEDBYID AS CreatedById, CREATEDON AS CreatedOn, MODIFIEDBYID AS ModifiedById, MODIFIEDON AS ModifiedOn
FROM MMETAREPORTASPECT
WHERE METAREPORTID = @MetaReportId
ORDER BY DISPLAYORDER";

        public const string GET_OPTIONS_BY_ASPECTIDS = @"
SELECT
    OPTIONID AS OptionId, ASPECTID AS AspectId, LABEL AS Label, VALUE AS Value, VALUETYPE AS ValueType,
    SORTORDER AS SortOrder,
    CREATEDBYID AS CreatedById, CREATEDON AS CreatedOn, MODIFIEDBYID AS ModifiedById, MODIFIEDON AS ModifiedOn
FROM MMETAREPORTASPECTOPTION
WHERE ASPECTID IN @AspectIds
ORDER BY SORTORDER";

        public const string GET_APPLICABLEVARIANTS_BY_OPTIONIDS = @"
SELECT OPTIONID AS OptionId, VARIANTID AS VariantId
FROM MMETAREPORTOPTIONVARIANT
WHERE OPTIONID IN @OptionIds";

        // ── MMETAREPORT upsert / delete ──────────────────────────────────────────────

        public const string INSERT_METAREPORT = @"
INSERT INTO MMETAREPORT
    (METAREPORTID, METAREPORTNAME, DESCRIPTION, STATUS, VERSION, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON)
VALUES
    (@MetaReportId, @MetaReportName, @Description, @Status, @Version, @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn)";

        public const string UPDATE_METAREPORT = @"
UPDATE MMETAREPORT
SET METAREPORTNAME = @MetaReportName, DESCRIPTION = @Description, STATUS = @Status,
    VERSION = VERSION + 1, MODIFIEDBYID = @ModifiedById, MODIFIEDON = @ModifiedOn
WHERE METAREPORTID = @MetaReportId";

        public const string DELETE_METAREPORT = "DELETE FROM MMETAREPORT WHERE METAREPORTID = @MetaReportId";

        // ── MMETAREPORTVARIANT diff-save statements ──────────────────────────────────

        public const string DELETE_VARIANTS_NOT_IN = @"
DELETE FROM MMETAREPORTVARIANT
WHERE METAREPORTID = @MetaReportId AND VARIANTID NOT IN @SurvivingIds";

        public const string DELETE_VARIANTS_ALL = "DELETE FROM MMETAREPORTVARIANT WHERE METAREPORTID = @MetaReportId";

        public const string INSERT_VARIANT = @"
INSERT INTO MMETAREPORTVARIANT
    (VARIANTID, METAREPORTID, VARIANTNAME, MENUID, REPORTID, SORTORDER, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON)
VALUES
    (@VariantId, @MetaReportId, @VariantName, @MenuId, @ReportId, @SortOrder, @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn)";

        public const string UPDATE_VARIANT = @"
UPDATE MMETAREPORTVARIANT
SET VARIANTNAME = @VariantName, MENUID = @MenuId, REPORTID = @ReportId, SORTORDER = @SortOrder,
    MODIFIEDBYID = @ModifiedById, MODIFIEDON = @ModifiedOn
WHERE VARIANTID = @VariantId";

        // ── MMETAREPORTASPECT diff-save statements ───────────────────────────────────

        public const string DELETE_ASPECTS_NOT_IN = @"
DELETE FROM MMETAREPORTASPECT
WHERE METAREPORTID = @MetaReportId AND ASPECTID NOT IN @SurvivingIds";

        public const string DELETE_ASPECTS_ALL = "DELETE FROM MMETAREPORTASPECT WHERE METAREPORTID = @MetaReportId";

        public const string INSERT_ASPECT = @"
INSERT INTO MMETAREPORTASPECT
    (ASPECTID, METAREPORTID, ASPECTNAME, ASPECTTYPE, ISMANDATORY, DISPLAYORDER, TABGROUP, TOOLTIP,
     CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON)
VALUES
    (@AspectId, @MetaReportId, @AspectName, @AspectType, @IsMandatory, @DisplayOrder, @TabGroup, @Tooltip,
     @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn)";

        public const string UPDATE_ASPECT = @"
UPDATE MMETAREPORTASPECT
SET ASPECTNAME = @AspectName, ASPECTTYPE = @AspectType, ISMANDATORY = @IsMandatory,
    DISPLAYORDER = @DisplayOrder, TABGROUP = @TabGroup, TOOLTIP = @Tooltip,
    MODIFIEDBYID = @ModifiedById, MODIFIEDON = @ModifiedOn
WHERE ASPECTID = @AspectId";

        // ── MMETAREPORTASPECTOPTION diff-save statements ─────────────────────────────
        // Scoped by the aspects belonging to one MetaReport (not a single AspectId) since
        // SaveMetaReportDefinition reconciles every aspect's options in one pass.

        public const string DELETE_OPTIONS_NOT_IN = @"
DELETE FROM MMETAREPORTASPECTOPTION
WHERE ASPECTID IN (SELECT ASPECTID FROM MMETAREPORTASPECT WHERE METAREPORTID = @MetaReportId)
  AND OPTIONID NOT IN @SurvivingIds";

        public const string DELETE_OPTIONS_ALL_FOR_METAREPORT = @"
DELETE FROM MMETAREPORTASPECTOPTION
WHERE ASPECTID IN (SELECT ASPECTID FROM MMETAREPORTASPECT WHERE METAREPORTID = @MetaReportId)";

        public const string INSERT_OPTION = @"
INSERT INTO MMETAREPORTASPECTOPTION
    (OPTIONID, ASPECTID, LABEL, VALUE, VALUETYPE, SORTORDER, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON)
VALUES
    (@OptionId, @AspectId, @Label, @Value, @ValueType, @SortOrder, @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn)";

        public const string UPDATE_OPTION = @"
UPDATE MMETAREPORTASPECTOPTION
SET LABEL = @Label, VALUE = @Value, VALUETYPE = @ValueType, SORTORDER = @SortOrder,
    MODIFIEDBYID = @ModifiedById, MODIFIEDON = @ModifiedOn
WHERE OPTIONID = @OptionId";

        // ── MMETAREPORTOPTIONVARIANT — full replace per save (see SaveMetaReportDefinition's
        // ordering note: every option's applicability set is resent whole, not diffed) ───────

        public const string DELETE_OPTIONVARIANTS_FOR_METAREPORT = @"
DELETE FROM MMETAREPORTOPTIONVARIANT
WHERE OPTIONID IN (
    SELECT o.OPTIONID FROM MMETAREPORTASPECTOPTION o
    JOIN MMETAREPORTASPECT a ON a.ASPECTID = o.ASPECTID
    WHERE a.METAREPORTID = @MetaReportId
)";

        public const string INSERT_OPTIONVARIANT = "INSERT INTO MMETAREPORTOPTIONVARIANT (OPTIONID, VARIANTID) VALUES (@OptionId, @VariantId)";
    }
}