namespace Admin.NET.Plugin.AiDOP.DataPlatform;

/// <summary>
/// 把源岗位原文写入 <c>mdp_employee_position_map</c>。MANUAL 行不覆盖。
/// </summary>
public sealed class EmployeePositionMapService : ITransient
{
    private readonly ISqlSugarClient _db;

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

    public async Task SyncNativeAsync(long tenantId, CancellationToken cancellationToken = default)
    {
        var rows = await _db.Ado.SqlQueryAsync<RawPosition>(
            """
            SELECT DISTINCT IFNULL(Domain,'') AS Domain,
                   COALESCE(NULLIF(JobTitle,''), NULLIF(WorkCtr,'')) AS Raw
            FROM EmployeeMaster
            WHERE tenant_id=@tenant
              AND COALESCE(NULLIF(JobTitle,''), NULLIF(WorkCtr,'')) IS NOT NULL
            """,
            new { tenant = tenantId });
        await UpsertAsync(tenantId, MdpSourceIdentity.Native, "JobTitle", rows, cancellationToken);
    }

    public async Task SyncT8Async(long tenantId, CancellationToken cancellationToken = default)
    {
        var rows = await _db.Ado.SqlQueryAsync<RawPosition>(
            """
            SELECT DISTINCT IFNULL(ztid,'') AS Domain, IFNULL(gw,'') AS Raw
            FROM mdp_std_t8_sys_pelist
            WHERE tenant_id=@tenant AND gw IS NOT NULL AND gw<>''
            """,
            new { tenant = tenantId });
        await UpsertAsync(tenantId, "T8", "gw", rows, cancellationToken);
    }

    public async Task UpsertAsync(long tenantId, string sourceSystem, string field, IEnumerable<RawPosition> rows, CancellationToken cancellationToken = default)
    {
        foreach (var row in rows)
        {
            cancellationToken.ThrowIfCancellationRequested();
            var code = EmployeePositionRules.Classify(row.Raw);
            await _db.Ado.ExecuteCommandAsync(
                """
                INSERT INTO mdp_employee_position_map
                  (tenant_id, source_system, domain, src_position_raw, src_position_field, position_code, role_source)
                VALUES (@tenant, @src, @domain, @raw, @field, @code, 'AUTO_KEYWORD')
                ON DUPLICATE KEY UPDATE
                  position_code = IF(role_source='MANUAL', position_code, VALUES(position_code)),
                  src_position_field = IF(role_source='MANUAL', src_position_field, VALUES(src_position_field))
                """,
                new { tenant = tenantId, src = sourceSystem, domain = row.Domain ?? "", raw = row.Raw, field, code });
            if (code == EmployeePositionRules.Unknown)
            {
                await _db.Ado.ExecuteCommandAsync(
                    """
                    INSERT INTO mdp_source_gate_log
                      (tenant_id, std_object, source_system, gate_reason, row_count, sample_keys)
                    VALUES (@tenant, 'EMPLOYEE', @src, 'POSITION_UNMAPPED', 1, @raw)
                    """,
                    new { tenant = tenantId, src = sourceSystem, raw = row.Raw });
            }
        }
    }

    public sealed class RawPosition
    {
        public string Domain { get; set; } = "";
        public string Raw { get; set; } = "";
    }
}