using Admin.NET.Plugin.AiDOP.Entity;

namespace Admin.NET.Plugin.AiDOP.SmartOps;

public sealed class KpiTargetConfigStore : IKpiTargetConfigStore, ITransient
{
    private readonly ISqlSugarClient _db;

    public KpiTargetConfigStore(ISqlSugarClient db) => _db = db;

    public Task<AdoSmartOpsKpiMaster?> FindKpiAsync(long tenantId, string metricCode, CancellationToken ct = default) =>
        _db.Queryable<AdoSmartOpsKpiMaster>()
            .ClearFilter<ITenantIdFilter>()
            .Where(x => x.TenantId == tenantId && x.MetricCode == metricCode)
            .FirstAsync(ct);

    public Task<List<AdoSmartOpsKpiTargetConfig>> ListAsync(long tenantId, string metricCode, long? factoryId, CancellationToken ct = default)
    {
        var q = _db.Queryable<AdoSmartOpsKpiTargetConfig>()
            .Where(x => x.TenantId == tenantId && x.MetricCode == metricCode);
        if (factoryId.HasValue)
            q = q.Where(x => x.FactoryId == factoryId.Value);
        return q.OrderBy(x => x.FactoryId).OrderBy(x => x.EffectiveFrom, OrderByType.Desc).ToListAsync(ct);
    }

    public Task<List<AdoSmartOpsKpiTargetConfig>> ListActiveScopeAsync(long tenantId, long factoryId, string metricCode, CancellationToken ct = default) =>
        _db.Queryable<AdoSmartOpsKpiTargetConfig>()
            .Where(x => x.TenantId == tenantId && x.FactoryId == factoryId && x.MetricCode == metricCode && x.Status == 1)
            .ToListAsync(ct);

    public Task<AdoSmartOpsKpiTargetConfig?> GetByIdAsync(long tenantId, long id, CancellationToken ct = default) =>
        _db.Queryable<AdoSmartOpsKpiTargetConfig>()
            .Where(x => x.TenantId == tenantId && x.Id == id)
            .FirstAsync(ct);

    public async Task<AdoSmartOpsKpiTargetConfig> InsertAsync(AdoSmartOpsKpiTargetConfig row, CancellationToken ct = default)
    {
        var id = await _db.Insertable(row).ExecuteReturnIdentityAsync();
        row.Id = id;
        return row;
    }

    public Task UpdateAsync(AdoSmartOpsKpiTargetConfig row, CancellationToken ct = default) =>
        _db.Updateable(row).ExecuteCommandAsync();

    public async Task<bool> IsReferencedByDailyAsync(long tenantId, long configId, CancellationToken ct = default)
    {
        foreach (var table in new[] { "ado_s9_kpi_value_l1_day", "ado_s9_kpi_value_l2_day", "ado_s9_kpi_value_l3_day", "ado_s9_kpi_value_l4_day" })
        {
            var n = await _db.Ado.GetIntAsync(
                $"SELECT COUNT(*) FROM {table} WHERE tenant_id=@TenantId AND target_config_id=@Id AND is_deleted=0",
                new List<SugarParameter> { new("@TenantId", tenantId), new("@Id", configId) });
            if (n > 0) return true;
        }
        return false;
    }

    public Task<int> CountCommittedHashAsync(long tenantId, string sha256, CancellationToken ct = default) =>
        _db.Queryable<AdoSmartOpsKpiTargetImportBatch>()
            .Where(x => x.TenantId == tenantId && x.FileSha256 == sha256 && x.Status == "COMMITTED")
            .CountAsync(ct);

    public async Task<AdoSmartOpsKpiTargetImportBatch> InsertBatchAsync(AdoSmartOpsKpiTargetImportBatch batch, CancellationToken ct = default)
    {
        var id = await _db.Insertable(batch).ExecuteReturnIdentityAsync();
        batch.Id = id;
        return batch;
    }

    public Task<AdoSmartOpsKpiTargetImportBatch?> GetBatchAsync(long tenantId, long batchId, CancellationToken ct = default) =>
        _db.Queryable<AdoSmartOpsKpiTargetImportBatch>()
            .Where(x => x.TenantId == tenantId && x.Id == batchId)
            .FirstAsync(ct);

    public Task UpdateBatchAsync(AdoSmartOpsKpiTargetImportBatch batch, CancellationToken ct = default) =>
        _db.Updateable(batch).ExecuteCommandAsync();
}