namespace FrameworkDAL.Query.EntityViewer
{
    // ============================================================
    // EntityLayoutQB — Entity Viewer layout registry queries.
    //
    // Tables:
    //   MENTITYLAYOUT         — header (EntityCode + optional TenantId override)
    //   MENTITYLAYOUTVERSION  — versioned LayoutSchema JSON snapshots
    //
    // MENTITYLAYOUT is NOT tenant-mandatory the way business tables are —
    // TENANTID IS NULL rows are the intentional global-default layout.
    // MENTITYLAYOUTVERSION carries no TenantId of its own (inherits its
    // header's).
    // ============================================================
    public static class EntityLayoutQB
    {
        // ── Layout header ─────────────────────────────────────────

        public const string SAVE_LAYOUT = @"
            INSERT INTO MENTITYLAYOUT
                (ENTITYCODE, TENANTID, TITLE, ISCOMPONENT, VERSION, STATUS, SORTORDER, SOURCETYPE,
                 CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON)
            OUTPUT INSERTED.ENTITYLAYOUTID
            VALUES
                (@EntityCode, @TenantId, @Title, @IsComponent, 1, 1, @SortOrder, 5,
                 @CreatedById, GETDATE(), @ModifiedById, GETDATE());";

        // Update: header rename/re-tenant/reorder — no compile step, so nothing else about
        // an existing header ever needs changing. Guarded against a soft-deleted header
        // (STATUS = 2) so a deleted-then-recreated EntityCode can't silently resurrect the
        // old row's identity.
        public const string UPDATE_LAYOUT_HEADER = @"
            UPDATE MENTITYLAYOUT
            SET    TITLE        = @Title,
                   TENANTID     = @TenantId,
                   SORTORDER    = @SortOrder,
                   ISCOMPONENT  = @IsComponent,
                   MODIFIEDBYID = @ModifiedById,
                   MODIFIEDON   = GETDATE(),
                   VERSION      = VERSION + 1
            WHERE  ENTITYLAYOUTID = @EntityLayoutId
              AND  STATUS <> 2;";

        // Soft delete only — GET_LAYOUT_LIST/GET_ACTIVE_LAYOUT already filter STATUS <> 2, so
        // this immediately removes the header from the Designer's list and from resolution,
        // without touching its version history.
        public const string DELETE_LAYOUT_HEADER = @"
            UPDATE MENTITYLAYOUT
            SET    STATUS       = 2,
                   MODIFIEDBYID = @ModifiedById,
                   MODIFIEDON   = GETDATE(),
                   VERSION      = VERSION + 1
            WHERE  ENTITYLAYOUTID = @EntityLayoutId;";

        public const string GET_LAYOUT_HEADER_BY_ID = @"
            SELECT  el.ENTITYLAYOUTID   AS EntityLayoutId,
                    el.ENTITYCODE       AS EntityCode,
                    el.TENANTID         AS TenantId,
                    el.TITLE            AS Title,
                    el.ISCOMPONENT      AS IsComponent,
                    el.STATUS           AS Status,
                    el.SORTORDER        AS SortOrder
            FROM    MENTITYLAYOUT el
            WHERE   el.ENTITYLAYOUTID = @EntityLayoutId;";

        // Layout Designer's list screen: every header, its current Active version's
        // summary (if any — a freshly-created header may have only Draft versions),
        // and the MENTITY display name for real entity types (NULL for components,
        // since their ENTITYCODE never matches a real MENTITY.ENTITYCODE row).
        public const string GET_LAYOUT_LIST = @"
            SELECT  el.ENTITYLAYOUTID          AS EntityLayoutId,
                    el.ENTITYCODE              AS EntityCode,
                    el.TENANTID                AS TenantId,
                    el.TITLE                   AS Title,
                    el.ISCOMPONENT             AS IsComponent,
                    me.ENTITYNAME              AS EntityName,
                    elv.VERSIONNO              AS ActiveVersionNo,
                    elv.VERSIONLABEL           AS ActiveVersionLabel,
                    elv.ACTIVATEDON            AS ActivatedOn
            FROM    MENTITYLAYOUT el
            LEFT JOIN MENTITYLAYOUTVERSION elv
                    ON  elv.ENTITYLAYOUTID = el.ENTITYLAYOUTID
                    AND elv.VERSIONSTATUS  = 1
            LEFT JOIN MENTITY me
                    ON  me.ENTITYCODE = el.ENTITYCODE
            WHERE   el.STATUS <> 2
              AND   el.ISCOMPONENT = @IsComponent
            ORDER BY el.SORTORDER, el.ENTITYCODE;";

        // Resolution: prefer the caller's own TenantId row, fall back to the
        // TenantId IS NULL global default — only among rows that actually
        // have an Active version.
        public const string GET_ACTIVE_LAYOUT = @"
            SELECT  TOP 1
                    el.ENTITYLAYOUTID      AS EntityLayoutId,
                    el.ENTITYCODE          AS EntityCode,
                    el.TENANTID            AS TenantId,
                    el.TITLE               AS Title,
                    elv.ENTITYLAYOUTVERSIONID AS EntityLayoutVersionId,
                    elv.VERSIONNO          AS VersionNo,
                    elv.VERSIONLABEL       AS VersionLabel,
                    elv.SNAPSHOTJSON       AS SnapshotJson,
                    elv.ACTIVATEDON        AS ActivatedOn
            FROM    MENTITYLAYOUT el
            INNER JOIN MENTITYLAYOUTVERSION elv
                    ON  elv.ENTITYLAYOUTID = el.ENTITYLAYOUTID
                    AND elv.VERSIONSTATUS  = 1
            WHERE   el.ENTITYCODE = @EntityCode
              AND   (el.TENANTID = @TenantId OR el.TENANTID IS NULL)
              AND   el.STATUS   <> 2
            ORDER BY CASE WHEN el.TENANTID = @TenantId THEN 0 ELSE 1 END, el.SORTORDER;";

        // ── Layout version ────────────────────────────────────────

        // VERSIONSTATUS starts at 0 (Draft, not yet active) — SnapshotJson is supplied
        // directly by the admin at save time (there's no compile step for layouts,
        // unlike qualifier plans; the JSON *is* the authored artifact).
        public const string SAVE_LAYOUT_VERSION = @"
            INSERT INTO MENTITYLAYOUTVERSION
                (ENTITYLAYOUTID, VERSIONNO, VERSIONLABEL, VERSIONSTATUS, SNAPSHOTJSON, ACTIVATEDON,
                 VERSION, STATUS, SORTORDER, SOURCETYPE,
                 CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON)
            OUTPUT INSERTED.ENTITYLAYOUTVERSIONID
            VALUES
                (@EntityLayoutId, @VersionNo, @VersionLabel, 0, @SnapshotJson, NULL,
                 1, 1, @SortOrder, 5,
                 @CreatedById, GETDATE(), @ModifiedById, GETDATE());";

        // Includes SNAPSHOTJSON (not just the summary columns) so the Designer can load ANY
        // past version's content — not only the currently-Active one GetActiveLayout returns
        // — to view an Archived version or resume editing a Draft. STATUS <> 2 excludes
        // discarded drafts (see DELETE_LAYOUT_VERSION).
        public const string GET_LAYOUT_VERSION_LIST = @"
            SELECT  elv.ENTITYLAYOUTVERSIONID AS EntityLayoutVersionId,
                    elv.ENTITYLAYOUTID        AS EntityLayoutId,
                    elv.VERSIONNO             AS VersionNo,
                    elv.VERSIONLABEL          AS VersionLabel,
                    elv.VERSIONSTATUS         AS VersionStatus,
                    elv.SNAPSHOTJSON          AS SnapshotJson,
                    elv.ACTIVATEDON           AS ActivatedOn
            FROM    MENTITYLAYOUTVERSION elv
            WHERE   elv.ENTITYLAYOUTID = @EntityLayoutId
              AND   elv.STATUS <> 2
            ORDER BY elv.VERSIONNO DESC;";

        // Update a version's content — ONLY while still Draft (VERSIONSTATUS = 0). Active and
        // Archived versions are immutable history; the WHERE guard makes an attempt to edit
        // either a silent 0-rows-affected no-op that the BLL turns into an explicit error,
        // rather than ever mutating a version that already went live or superseded one.
        public const string UPDATE_LAYOUT_VERSION = @"
            UPDATE MENTITYLAYOUTVERSION
            SET    VERSIONLABEL  = @VersionLabel,
                   SNAPSHOTJSON  = @SnapshotJson,
                   MODIFIEDBYID  = @ModifiedById,
                   MODIFIEDON    = GETDATE(),
                   VERSION       = VERSION + 1
            WHERE  ENTITYLAYOUTVERSIONID = @EntityLayoutVersionId
              AND  VERSIONSTATUS = 0
              AND  STATUS <> 2;";

        // Discard a Draft that was never activated — same VERSIONSTATUS = 0 guard as UPDATE,
        // for the same reason: Active/Archived rows are permanent history, never deletable.
        public const string DELETE_LAYOUT_VERSION = @"
            UPDATE MENTITYLAYOUTVERSION
            SET    STATUS       = 2,
                   MODIFIEDBYID = @ModifiedById,
                   MODIFIEDON   = GETDATE(),
                   VERSION      = VERSION + 1
            WHERE  ENTITYLAYOUTVERSIONID = @EntityLayoutVersionId
              AND  VERSIONSTATUS = 0;";

        // Activate: archive existing Active version, promote the target — both run
        // in the same transaction (called from BLL) to prevent dual-active on crash.
        public const string ARCHIVE_ACTIVE_VERSION = @"
            UPDATE MENTITYLAYOUTVERSION
            SET    VERSIONSTATUS = 2,   -- Archived
                   MODIFIEDBYID  = @ModifiedById,
                   MODIFIEDON    = GETDATE(),
                   VERSION       = VERSION + 1
            WHERE  ENTITYLAYOUTID = @EntityLayoutId
              AND  VERSIONSTATUS  = 1;";

        public const string ACTIVATE_VERSION = @"
            UPDATE MENTITYLAYOUTVERSION
            SET    VERSIONSTATUS = 1,   -- Active
                   ACTIVATEDON   = GETDATE(),
                   MODIFIEDBYID  = @ModifiedById,
                   MODIFIEDON    = GETDATE(),
                   VERSION       = VERSION + 1
            WHERE  ENTITYLAYOUTVERSIONID = @EntityLayoutVersionId;";
    }
}