using static GB5Shared.GB5Constant.Constant;

namespace GB5Shared.Draft.Query
{
    /// <summary>
    /// SQL constants for the extended draft row system.
    /// Stored-procedure EXEC strings for SQL Server.
    /// Plain INSERT ... ON CONFLICT DO UPDATE for PostgreSQL.
    ///
    /// Required indexes (created by migration 20260520_draft_row_extension.sql):
    ///   TDRAFT_ROWS: UQ_DR_SESSION_ROW (SESSIONGUID, ROWGUID, CLIENTID)
    ///   TDRAFT_ROWS: IX_DR_SESSION_PARENT (SESSIONGUID, CLIENTID, PARENTROWGUID, OBJECTTYPEID)
    ///   TDRAFT: UQ_TDRAFT_SESSION (SESSIONGUID)
    /// </summary>
    public static class DraftRowQB
    {
        // ── Header (TDRAFT) ───────────────────────────────────────────────

        public const string GET_DRAFT_ID_BY_SESSION_SQL = @"
            SELECT DRAFTID FROM DBO.TDRAFT
            WHERE  SESSIONGUID = @SessionGuid";

        public const string GET_DRAFT_ID_BY_SESSION_PG = @"
            SELECT draftid FROM tdraft
            WHERE  sessionguid = @SessionGuid";

        public const string HEADER_GET_SQL = @"
            SELECT
                DRAFTID              AS DraftId,
                ENTITYID             AS EntityId,
                MENUID               AS MenuId,
                BIZTRANSACTIONTYPEID AS BizTransactionTypeId,
                OUID                 AS OuId,
                CODE                 AS Code,
                NAME                 AS Name,
                PARTICULARS          AS Particulars,
                DRAFTNUMBER          AS DraftNumber,
                DRAFTDATE            AS DraftDate,
                REFERENCENUMBER      AS ReferenceNumber,
                REFERENCEDATE        AS ReferenceDate,
                JSONOBJECT           AS JsonObject,
                STATUS               AS Status,
                VERSION              AS Version,
                SESSIONGUID          AS SessionGuid,
                DRAFTSTATUS          AS DraftStatus,
                OWNERUSERID          AS OwnerUserId,
                ASSIGNEDTOUSERID     AS AssignedToUserId,
                LASTACTIVITYON       AS LastActivityOn,
                CREATEDON            AS CreatedOn,
                MODIFIEDON           AS ModifiedOn
            FROM DBO.TDRAFT
            WHERE SESSIONGUID = @SessionGuid";

        public const string HEADER_GET_PG = @"
            SELECT
                draftid              AS DraftId,
                entityid             AS EntityId,
                menuid               AS MenuId,
                biztransactiontypeid AS BizTransactionTypeId,
                ouid                 AS OuId,
                code                 AS Code,
                name                 AS Name,
                particulars          AS Particulars,
                draftnumber          AS DraftNumber,
                draftdate            AS DraftDate,
                referencenumber      AS ReferenceNumber,
                referencedate        AS ReferenceDate,
                jsonobject           AS JsonObject,
                status               AS Status,
                version              AS Version,
                sessionguid          AS SessionGuid,
                draftstatus          AS DraftStatus,
                owneruserid          AS OwnerUserId,
                assignedtouserid     AS AssignedToUserId,
                lastactivityon       AS LastActivityOn,
                createdon            AS CreatedOn,
                modifiedon           AS ModifiedOn
            FROM tdraft
            WHERE sessionguid = @SessionGuid";

        // SELECT appended to the batch (not part of the SP itself) so the caller can read
        // @OutDraftId back via ExecuteScalarAsync in the same round trip, instead of a
        // separate GET_DRAFT_ID_BY_SESSION_SQL query after every header upsert — this is a
        // hot path (every debounced header edit, plus the 30s idle re-check).
        public const string EXEC_HEADER_UPSERT_SQL = @"
            DECLARE @OutDraftId INT;
            EXEC DBO.SP_DRAFT_HEADER_UPSERT
                @DraftId, @SessionGuid, @EntityId, @MenuId,
                @BizTransactionTypeId, @OuId,
                @Code, @Name, @Particulars, @DraftNumber,
                @JsonObject, @UserId, @OutDraftId OUTPUT;
            SELECT @OutDraftId AS DraftId;";

        // PostgreSQL: INSERT ... ON CONFLICT DO UPDATE (no SPs in PG)
        // Returns the DraftId via SELECT after upsert.
        public const string HEADER_UPSERT_PG = @"
            INSERT INTO tdraft (
                draftid, entityid, menuid, biztransactiontypeid, ouid,
                code, name, particulars, draftnumber,
                draftdate, referencenumber, referencedate,
                jsonobject, sessionguid,
                draftstatus, owneruserid, assignedtouserid,
                lastactivityon, status, version,
                createdbyid, createdon, modifiedbyid, modifiedon
            )
            VALUES (
                @DraftId, @EntityId, @MenuId, @BizTransactionTypeId, @OuId,
                @Code, @Name, @Particulars, @DraftNumber,
                NOW(), '', NOW(),
                @JsonObject, @SessionGuid,
                1, @UserId, @UserId,
                NOW(), 1, 1,
                @UserId, NOW(), @UserId, NOW()
            )
            ON CONFLICT (sessionguid)
            DO UPDATE SET
                jsonobject       = EXCLUDED.jsonobject,
                lastactivityon   = NOW(),
                version          = COALESCE(tdraft.version, 0) + 1,
                modifiedbyid     = EXCLUDED.modifiedbyid,
                modifiedon       = NOW();

            SELECT draftid FROM tdraft WHERE sessionguid = @SessionGuid;";

        // ── Rows (TDRAFT_ROWS) ────────────────────────────────────────────

        public const string EXEC_ROW_UPSERT_SQL = @"
            EXEC DBO.SP_DRAFT_ROW_UPSERT
                @DraftId, @SessionGuid, @ClientId, @RowGuid, @ParentRowGuid,
                @ObjectTypeId, @RowState, @ServerRowId, @ParentServerId,
                @SortOrder, @RowData";

        public const string ROW_DELETE_SQL = @"
            DELETE FROM DBO.TDRAFT_ROWS
            WHERE  SESSIONGUID = @SessionGuid
              AND  ROWGUID     = @RowGuid";

        public const string ROW_DELETE_PG = @"
            DELETE FROM tdraft_rows
            WHERE  sessionguid = @SessionGuid
              AND  rowguid     = @RowGuid";

        public const string ROW_UPSERT_PG = @"
            INSERT INTO tdraft_rows (
                draftid, sessionguid, clientid, rowguid, parentrowguid,
                objecttypeid, rowstate, serverrowid, parentserverid,
                sortorder, rowdata, createdon, modifiedon
            )
            VALUES (
                @DraftId, @SessionGuid, @ClientId, @RowGuid, @ParentRowGuid,
                @ObjectTypeId, @RowState, @ServerRowId, @ParentServerId,
                @SortOrder, @RowData, NOW(), NOW()
            )
            ON CONFLICT (sessionguid, rowguid, clientid)
            DO UPDATE SET
                rowstate       = EXCLUDED.rowstate,
                serverrowid    = EXCLUDED.serverrowid,
                parentserverid = EXCLUDED.parentserverid,
                sortorder      = EXCLUDED.sortorder,
                rowdata        = EXCLUDED.rowdata,
                modifiedon     = NOW();

            UPDATE tdraft SET lastactivityon = NOW() WHERE sessionguid = @SessionGuid;";

        public const string EXEC_ROWS_GET_SQL = @"
            EXEC DBO.SP_DRAFT_ROWS_GET @SessionGuid, @ClientId";

        public const string ROWS_GET_PG = @"
            SELECT
                draftrowid AS DraftRowId, draftid AS DraftId,
                sessionguid AS SessionGuid, clientid AS ClientId,
                rowguid AS RowGuid, parentrowguid AS ParentRowGuid,
                objecttypeid AS ObjectTypeId, rowstate AS RowState,
                serverrowid AS ServerRowId, parentserverid AS ParentServerId,
                sortorder AS SortOrder, rowdata AS RowData,
                createdon AS CreatedOn, modifiedon AS ModifiedOn
            FROM  tdraft_rows
            WHERE sessionguid = @SessionGuid
              AND clientid    = @ClientId
            ORDER BY objecttypeid, sortorder, draftrowid";

        // ── Cleanup ───────────────────────────────────────────────────────

        public const string EXEC_CLEANUP_SQL = @"
            EXEC DBO.SP_DRAFT_CLEANUP @SessionGuid, @ClientId, @ActionType, @UserId";

        public const string CLEANUP_PG = @"
            UPDATE tdraft
            SET    draftstatus  = CASE WHEN @ActionType = 7 THEN 5
                                       WHEN @ActionType = 8 THEN 6
                                       ELSE draftstatus END,
                   modifiedbyid = @UserId,
                   modifiedon   = NOW()
            WHERE  sessionguid = @SessionGuid;

            DELETE FROM tdraft_rows
            WHERE  sessionguid = @SessionGuid
              AND  clientid    = @ClientId;

            DELETE FROM tdraft WHERE sessionguid = @SessionGuid;";

        // ── Share ─────────────────────────────────────────────────────────

        public const string EXEC_SHARE_SQL = @"
            EXEC DBO.SP_DRAFT_SHARE @SessionGuid, @TargetUserId, @RequestingUserId";

        public const string SHARE_PG = @"
            UPDATE tdraft
            SET    assignedtouserid = @TargetUserId,
                   draftstatus      = 3,
                   sharedwithuserid = @TargetUserId,
                   sharedon         = NOW(),
                   modifiedbyid     = @RequestingUserId,
                   modifiedon       = NOW()
            WHERE  sessionguid  = @SessionGuid
              AND  owneruserid  = @RequestingUserId
              AND  draftstatus  = 1;";

        // ── Pool ──────────────────────────────────────────────────────────

        public const string EXEC_POOL_GET_SQL = @"
            EXEC DBO.SP_DRAFT_POOL_GET @MenuId, @UserId";

        public const string POOL_GET_PG = @"
            SELECT
                d.draftid AS DraftId, d.sessionguid AS SessionGuid,
                d.code AS Code, d.name AS Name,
                d.draftnumber AS DraftNumber,
                d.draftstatus AS DraftStatus,
                d.owneruserid AS OwnerUserId,
                d.assignedtouserid AS AssignedToUserId,
                d.lastactivityon AS LastActivityOn,
                d.createdon AS CreatedOn,
                (SELECT COUNT(*) FROM tdraft_rows dr
                 WHERE dr.sessionguid = d.sessionguid) AS DirtyRowCount
            FROM  tdraft d
            WHERE d.menuid      = @MenuId
              AND d.draftstatus IN (1, 2, 3)
              AND (d.owneruserid = @UserId OR d.assignedtouserid = @UserId)
            ORDER BY d.lastactivityon DESC";

        // ── Ownership check (application-layer, before SP call) ───────────

        public const string CHECK_OWNER_SQL = @"
            SELECT COUNT(1) FROM DBO.TDRAFT
            WHERE  SESSIONGUID = @SessionGuid
              AND  OWNERUSERID = @UserId
              AND  DRAFTSTATUS = 1";

        public const string CHECK_OWNER_PG = @"
            SELECT COUNT(1) FROM tdraft
            WHERE  sessionguid = @SessionGuid
              AND  owneruserid = @UserId
              AND  draftstatus = 1";

        // ── Orphan cleanup (called from OrphanDraftCleanupJob) ────────────

        public const string EXEC_CLEANUP_ORPHANS_SQL = @"
            EXEC DBO.SP_CLEANUP_ORPHAN_DRAFTS @RetentionHours, @BatchSize";

        public const string CLEANUP_ORPHANS_PG = @"
            WITH expired AS (
                SELECT sessionguid
                FROM   tdraft
                WHERE  draftstatus   = 1
                  AND  sessionguid   IS NOT NULL
                  AND  lastactivityon < NOW() - INTERVAL '1 hour' * @RetentionHours
                LIMIT  @BatchSize
            )
            DELETE FROM tdraft_rows
            WHERE  sessionguid IN (SELECT sessionguid FROM expired);

            WITH expired AS (
                SELECT sessionguid FROM tdraft
                WHERE  draftstatus = 1 AND sessionguid IS NOT NULL
                  AND  lastactivityon < NOW() - INTERVAL '1 hour' * @RetentionHours
                LIMIT @BatchSize
            )
            DELETE FROM tdraft WHERE sessionguid IN (SELECT sessionguid FROM expired);";
    }
}