namespace SwDAL.Query.Project;

public static class ProjectQB
{
    public static readonly HashSet<string> AllowedSortColumns = new(StringComparer.OrdinalIgnoreCase)
    {
        "projectname", "createdon"
    };

    // Required index: IX_MSWPROJECT_TENANTID ON SW.MSWPROJECT (TENANTID, STATUS)
    public const string GET_BY_ID = @"
        SELECT  p.PROJECTID     AS ProjectId,
                p.PROJECTNAME   AS ProjectName,
                p.DESCRIPTION   AS Description,
                p.CLIENTCODE    AS ClientCode,
                p.ENVIRONMENT   AS Environment,
                p.TENANTID      AS TenantId,
                p.VERSION       AS Version,
                p.STATUS        AS Status,
                p.CREATEDBYID   AS CreatedById,
                p.CREATEDON     AS CreatedOn,
                p.MODIFIEDBYID  AS ModifiedById,
                p.MODIFIEDON    AS ModifiedOn
        FROM    SW.MSWPROJECT p
        WHERE   p.PROJECTID = @ProjectId
        AND     p.STATUS    = 1";

    public const string GET_SELECT_LIST = @"SELECT
                                            p.PROJECTID   AS ProjectId,
                                            p.PROJECTNAME AS ProjectName
                                    FROM SW.MSWPROJECT p
                                    WHERE (@TenantId = -1 OR p.TENANTID = @TenantId)
                                      AND p.STATUS = 1
                                    ORDER BY p.PROJECTNAME
                                    OFFSET CASE WHEN @FirstNumber < 0 THEN 0 ELSE @FirstNumber END ROWS
                                    FETCH NEXT CASE WHEN @MaxResult < 1 THEN 100 ELSE @MaxResult END ROWS ONLY;";

    public const string INSERT = @"
        INSERT INTO SW.MSWPROJECT
               (PROJECTNAME, DESCRIPTION, CLIENTCODE, ENVIRONMENT, TENANTID, VERSION, STATUS, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON)
        VALUES (@ProjectName, @Description, @ClientCode, @Environment, @TenantId, 1, 1, @CreatedById, GETUTCDATE(), @ModifiedById, GETUTCDATE());
        SELECT CAST(SCOPE_IDENTITY() AS INT);";

    public const string UPDATE = @"
        UPDATE SW.MSWPROJECT
        SET    PROJECTNAME  = @ProjectName,
               DESCRIPTION  = @Description,
               CLIENTCODE   = @ClientCode,
               ENVIRONMENT  = @Environment,
               VERSION      = VERSION + 1,
               MODIFIEDBYID = @ModifiedById,
               MODIFIEDON   = GETUTCDATE()
        WHERE  PROJECTID = @ProjectId
        AND    STATUS    = 1";

    public const string DELETE = @"
        UPDATE SW.MSWPROJECT
        SET    STATUS       = 0,
               MODIFIEDBYID = @ModifiedById,
               MODIFIEDON   = GETUTCDATE()
        WHERE  PROJECTID = @ProjectId
        AND    STATUS    = 1";

    // ── Project ↔ DbServer membership ──────────────────────────────────────

    public const string GET_SERVERS = @"
        SELECT  ps.PROJECTSERVERID AS ProjectServerId,
                ps.PROJECTID       AS ProjectId,
                ps.DBSERVERID      AS DbServerId,
                s.HOSTNAME         AS HostName
        FROM    SW.MSWPROJECTSERVER ps
        JOIN    SW.MSWDBSERVER s ON s.DBSERVERID = ps.DBSERVERID
        WHERE   ps.PROJECTID = @ProjectId
        ORDER BY s.HOSTNAME";

    public const string ADD_SERVER = @"
        IF NOT EXISTS (SELECT 1 FROM SW.MSWPROJECTSERVER WHERE PROJECTID = @ProjectId AND DBSERVERID = @DbServerId)
        INSERT INTO SW.MSWPROJECTSERVER (PROJECTID, DBSERVERID, TENANTID)
        VALUES (@ProjectId, @DbServerId, @TenantId)";

    public const string REMOVE_SERVER = @"
        DELETE FROM SW.MSWPROJECTSERVER
        WHERE  PROJECTID  = @ProjectId
        AND    DBSERVERID = @DbServerId";

    // ── Project ↔ User (manager) membership ────────────────────────────────

    public const string GET_MANAGERS = @"
        SELECT  pm.PROJECTMANAGERID AS ProjectManagerId,
                pm.PROJECTID        AS ProjectId,
                pm.USERID           AS UserId,
                u.USERNAME          AS UserName
        FROM    SW.MSWPROJECTMANAGER pm
        JOIN    MUSER u ON u.USERID = pm.USERID
        WHERE   pm.PROJECTID = @ProjectId
        ORDER BY u.USERNAME";

    public const string ADD_MANAGER = @"
        IF NOT EXISTS (SELECT 1 FROM SW.MSWPROJECTMANAGER WHERE PROJECTID = @ProjectId AND USERID = @UserId)
        INSERT INTO SW.MSWPROJECTMANAGER (PROJECTID, USERID, TENANTID)
        VALUES (@ProjectId, @UserId, @TenantId)";

    public const string REMOVE_MANAGER = @"
        DELETE FROM SW.MSWPROJECTMANAGER
        WHERE  PROJECTID = @ProjectId
        AND    USERID    = @UserId";

    public static (string sql, string countSql) BuildProjectList(
        string? searchText, string sortColumn, string sortDirection, bool activeOnly)
    {
        string safeSort = AllowedSortColumns.Contains(sortColumn) ? sortColumn.ToUpperInvariant() : "PROJECTNAME";
        string safeDir  = sortDirection.Equals("DESC", StringComparison.OrdinalIgnoreCase) ? "DESC" : "ASC";

        var where = new System.Text.StringBuilder("WHERE p.TENANTID = @TenantId");
        if (activeOnly)                             where.Append(" AND p.STATUS = 1");
        if (!string.IsNullOrWhiteSpace(searchText))  where.Append(" AND p.PROJECTNAME LIKE @SearchText");

        string body = $@"
        FROM SW.MSWPROJECT p
        {where}";

        string sql = $@"
        SELECT  p.PROJECTID    AS ProjectId,
                p.PROJECTNAME  AS ProjectName,
                p.CLIENTCODE   AS ClientCode,
                p.ENVIRONMENT  AS Environment
        {body}
        ORDER BY p.{safeSort} {safeDir}
        OFFSET @PageOffset ROWS FETCH NEXT @PageSize ROWS ONLY";

        string countSql = $"SELECT COUNT(1) {body}";

        return (sql, countSql);
    }
}