### A Pluto.jl notebook ###
# v0.20.8

using Markdown
using InteractiveUtils

# ╔═╡ 1d057382-316a-11f0-1fbd-c9c7f879bbb1
begin
    using Pkg
    Pkg.activate(".")
    using CSV
	using SQLite
    using DataFrames
    using PlutoUI
    using PSBoardDataBase
end

# ╔═╡ 3bf488c4-cd2a-4b2c-b2ee-a6f8d84368df
TableOfContents(depth = 3)

# ╔═╡ f8b25d6d-5756-42fa-ac9f-a238d815dad2
second_run_result = let
    result_file = PSBoardDataBase.DownloadCSVs.download_second_run_csv()
    df = CSV.read(result_file, DataFrame)
    PSBoardDataBase.prepare_eighth_campaign_result(df)
    df
end

# ╔═╡ 7d7c80a3-800f-48cf-908b-172730320571
md"""
# Count summary
"""

# ╔═╡ d597786d-3112-476e-a640-d494aed630bd
filter(:motherboard_id => ismissing, second_run_result)

# ╔═╡ da2d6bb7-d970-4eeb-9a1a-bbdda6506312
findall(ismissing, second_run_result.motherboard_id)

# ╔═╡ 9dae37ff-614a-4d50-9f32-908e799e325e
@view(second_run_result[1315, :])

# ╔═╡ c3954e6c-5239-445e-bdeb-6caad6b3b377
sdf_wrong_psbid = @view(second_run_result[1:1315, :])

# ╔═╡ 157a0ca3-a92a-4f6d-a3d3-c9650b4bf573
sdf_correct_psbid = @view(second_run_result[1315:end, :])

# ╔═╡ 346993ff-fdb0-4f0f-a83f-23584e2bc540
let
    correct_psbs = Set(sdf_correct_psbid.motherboard_id |> skipmissing)
    filter(Set(sdf_wrong_psbid.motherboard_id |> skipmissing)) do psbid
        !(psbid in correct_psbs)
    end
end

# ╔═╡ c7adb9ed-ef92-4727-a446-556d65cf6f3f
falied_boards = [895, 896, 1142, 1276, 1094, 1095]

# ╔═╡ b8b31ee3-ffc9-4d55-80ff-001dfebfc776
md"""
# slave log validation
"""

# ╔═╡ f464ed57-7d42-42e5-a388-c815bba0ad60
map(eachrow(second_run_result)) do row
    row
end

# ╔═╡ 2bd267ea-78ab-454e-93e4-81a2f7a5d671
second_run_writing_result = filter(:motherboard_id => (id -> begin
    coalesce(id != 999999, false)
end), second_run_result)

# ╔═╡ 383e1e04-ba3e-49bf-9f99-46c53b37204b
combined_result = map(eachrow(second_run_writing_result)) do row
    slavelog_dir = "../test/input/slavelogs/main/"
    logfilename = "$(row.motherboard_id)_$(row.runid).txt"
    logfilepath = joinpath(slavelog_dir, logfilename)
    if !ispath(logfilepath)
        return (psbid = row.motherboard_id, slavelog_psbid = missing, qspip = row.qspip)
    end
    if row.qspip != 1
        return (psbid = row.motherboard_id, slavelog_psbid = missing, qspip = row.qspip)
    end
    psbid = PSBoardDataBase.SlaveLogParserV2.parse_slave_log_v2(logfilepath)
    (psbid = row.motherboard_id, slavelog_psbid = psbid, qspip = row.qspip)
end |> DataFrame

# ╔═╡ 162ef015-7acd-4431-91bd-f3c5f1d26f8a
md"""
## result
**Looks OK**
"""

# ╔═╡ a721c7e2-a19e-4a8a-87ba-6809fb9d1cc4
combined_result.psbid |> unique |> length

# ╔═╡ e0411f36-fa75-445c-a275-b58cfa5ec73c
filter(:qspip => ==(1), combined_result).psbid |> unique |> length

# ╔═╡ 5d66892d-3d12-4f0a-a128-18b95eab7123
filter(:qspip => ==(0), combined_result).psbid |> unique |> length

# ╔═╡ abcf438d-7ebe-461d-bc19-1dedcf4861cc
filter(:slavelog_psbid => ismissing, combined_result).psbid |> unique |> length

# ╔═╡ 8ccfce73-9fdf-48bd-a78f-39bfda3c7121
combined_by_psbid = combine(groupby(combined_result, :psbid), [:slavelog_psbid, :qspip] => ((slavelog_psbid, qspip) -> begin
	# @info "" slavelog_psbid qspip
	if any(==(1), qspip)
		[(; qspip_ok = true)]
	else
		[(; qspip_ok = false)]
	end
end) => [:qspip_ok])

# ╔═╡ eb77e281-1d5e-4ba1-a6d8-8e7235d5a7d0
filter(:qspip_ok => !, combined_by_psbid).psbid

# ╔═╡ cb2d2f25-e008-4f62-a6a0-83febaddb2ef
md"""
# Compare with QAQC
"""

# ╔═╡ c0d22530-f0a0-461d-b15c-779b1b6a2a42
db = SQLite.DB("../psboard_qaqc.db")

# ╔═╡ 2244386c-5608-4914-abdd-c7403c1f0a74
SQLite.tables(db)

# ╔═╡ 0d502ae5-a7a3-44f7-bfb9-49cf77e22cfe
ps_boards = DBInterface.execute(db, "select * from ps_boards") |> DataFrame

# ╔═╡ 516e6255-63d4-4e83-9d9d-4a3dc46b8e56
qaqc_single_result = DBInterface.execute(db, "select * from qaqc_single_run_results") |> DataFrame |> (df -> groupby(df, :psboard_id))

# ╔═╡ 9a1eb3d1-ed35-4032-b345-e18fa0657268
qaqc_extra_result = DBInterface.execute(db, "select * from qaqc_extra_run_results") |> DataFrame |> (df -> groupby(df, :psboard_id))

# ╔═╡ f7710e79-86e4-4048-b3cf-03c7097b388c
qaqc_dispatch = DBInterface.execute(db, "select * from qaqc_dispatch") |> DataFrame

# ╔═╡ 2beefcb1-a3e7-485c-bcca-d2fb1624119f
qaqc_dispatch.psb_id .∈ Ref(filter(:qspip_ok => identity, combined_by_psbid).psbid)

# ╔═╡ f334909b-0a09-42fc-8fc0-64978ca6e067
let
	passed = filter(:qspip_ok => identity, combined_by_psbid).psbid
	@info "" passed
	filter(qaqc_dispatch.psb_id) do psbid
		!(psbid in passed)
	end
end

# ╔═╡ 2ef8303f-84a9-4053-a38e-b31fcd766796
let
	qspi_tried = combined_by_psbid.psbid
	qspi_passed = filter(:qspip_ok => identity, combined_by_psbid).psbid
	Iterators.flatmap(qaqc_dispatch.psb_id) do psbid
		if psbid in qspi_passed
			return []
		end
		[(; psbid, qspi_tried = psbid in qspi_tried, qspi_passed = psbid in qspi_passed)]
	end |> DataFrame
end

# ╔═╡ f16b9b80-242e-4349-99ee-a8de614a7b3e
md"""
## diff from dispatch list
差分は全て、以下の2通りのいずれかに該当する。
また、61はプレ量産なので、出荷リストに含まれない。

### QSPIパラメータ書き込みしようとしたが、失敗したもの
```
61
896
1094
1142
1160
```

### 通常のファームウェア・QSPIパラメータ書き込み試験で漏れていて、あとで手動で統合試験ベンチで書き込んだもの
```
110
108
483
484
1402
1401
1553
1533
```
"""

# ╔═╡ Cell order:
# ╠═1d057382-316a-11f0-1fbd-c9c7f879bbb1
# ╠═3bf488c4-cd2a-4b2c-b2ee-a6f8d84368df
# ╠═f8b25d6d-5756-42fa-ac9f-a238d815dad2
# ╠═7d7c80a3-800f-48cf-908b-172730320571
# ╠═d597786d-3112-476e-a640-d494aed630bd
# ╠═da2d6bb7-d970-4eeb-9a1a-bbdda6506312
# ╠═9dae37ff-614a-4d50-9f32-908e799e325e
# ╠═c3954e6c-5239-445e-bdeb-6caad6b3b377
# ╠═157a0ca3-a92a-4f6d-a3d3-c9650b4bf573
# ╠═346993ff-fdb0-4f0f-a83f-23584e2bc540
# ╠═c7adb9ed-ef92-4727-a446-556d65cf6f3f
# ╠═b8b31ee3-ffc9-4d55-80ff-001dfebfc776
# ╠═f464ed57-7d42-42e5-a388-c815bba0ad60
# ╠═2bd267ea-78ab-454e-93e4-81a2f7a5d671
# ╠═383e1e04-ba3e-49bf-9f99-46c53b37204b
# ╠═162ef015-7acd-4431-91bd-f3c5f1d26f8a
# ╠═a721c7e2-a19e-4a8a-87ba-6809fb9d1cc4
# ╠═e0411f36-fa75-445c-a275-b58cfa5ec73c
# ╠═5d66892d-3d12-4f0a-a128-18b95eab7123
# ╠═abcf438d-7ebe-461d-bc19-1dedcf4861cc
# ╠═8ccfce73-9fdf-48bd-a78f-39bfda3c7121
# ╠═eb77e281-1d5e-4ba1-a6d8-8e7235d5a7d0
# ╠═cb2d2f25-e008-4f62-a6a0-83febaddb2ef
# ╠═c0d22530-f0a0-461d-b15c-779b1b6a2a42
# ╠═2244386c-5608-4914-abdd-c7403c1f0a74
# ╠═0d502ae5-a7a3-44f7-bfb9-49cf77e22cfe
# ╠═516e6255-63d4-4e83-9d9d-4a3dc46b8e56
# ╠═9a1eb3d1-ed35-4032-b345-e18fa0657268
# ╠═f7710e79-86e4-4048-b3cf-03c7097b388c
# ╠═2beefcb1-a3e7-485c-bcca-d2fb1624119f
# ╠═f334909b-0a09-42fc-8fc0-64978ca6e067
# ╠═2ef8303f-84a9-4053-a38e-b31fcd766796
# ╠═f16b9b80-242e-4349-99ee-a8de614a7b3e