%% append_cn_to_db_FIXED.m
% Robust append NEW Cn Excel to main DB (Daily/Hourly auto-detect)
% Works on older MATLAB versions (uses xlsfinfo instead of sheetnames)
% Auto-detects datetime & Cn columns safely and converts types

clear; clc;

% --------- USER SETTINGS ----------
dbFile  = "D:\DB_cleaned(test).xlsx";   % main database path
newFile = "D:\CN(7-9-2025).xlsx";        % new file path
% ----------------------------------

%% 1) Read DB sheets
Tdaily  = readtable(dbFile, "Sheet","Daily",  "VariableNamingRule","preserve");
Thourly = readtable(dbFile, "Sheet","Hourly", "VariableNamingRule","preserve");

%% 2) Read NEW file (first sheet) - compatible way
[~, sheetList] = xlsfinfo(newFile);
if isempty(sheetList)
    error("No sheets found in new file: %s", newFile);
end
Tnew = readtable(newFile, "Sheet", sheetList{1}, "VariableNamingRule","preserve");

newVars = string(Tnew.Properties.VariableNames);
lvNew   = lower(newVars);

%% 3) Robustly detect NEW datetime column by trying conversions
bestDtIdx = [];
bestValid = -1;
bestDt = [];

for i = 1:width(Tnew)
    col = Tnew{:,i};
    dt = [];

    try
        if isdatetime(col)
            dt = col;
        elseif isnumeric(col)
            % could be Excel serial datetime
            dt = datetime(col, "ConvertFrom","excel");
        else
            % try parse text/cell/string
            if iscell(col), col = string(col); end
            dt = datetime(string(col), "InputFormat",""); %#ok<DTCH>
            % if it fails, catch will handle
        end

        % count valid
        v = sum(~isnat(dt));
        if v > bestValid
            bestValid = v;
            bestDtIdx = i;
            bestDt = dt;
        end
    catch
        % ignore this column
    end
end

if isempty(bestDtIdx) || bestValid < max(5, round(height(Tnew)*0.3))
    % fallback by header keywords if conversion approach failed
    dtIdx = find(contains(lvNew,"time") | contains(lvNew,"date"), 1, "first");
    if isempty(dtIdx)
        error("Datetime column not found in new file.");
    end
    bestDtIdx = dtIdx;
    col = Tnew{:,bestDtIdx};
    if isdatetime(col)
        bestDt = col;
    elseif isnumeric(col)
        bestDt = datetime(col,"ConvertFrom","excel");
    else
        if iscell(col), col = string(col); end
        bestDt = datetime(string(col));
    end
end

dtNewAll = bestDt;
dtNewAll = dtNewAll(:);

% remove NaT rows
validNew = ~isnat(dtNewAll);
dtNew = dtNewAll(validNew);

%% 4) Detect NEW Cn column robustly
% Strategy:
%  - Prefer columns whose name contains 'cn' but not 'time/date'
%  - Then test convertibility to numeric and pick the one with most finite values
cnCandidates = find(contains(lvNew,"cn") & ~contains(lvNew,"time") & ~contains(lvNew,"date"));
if isempty(cnCandidates)
    cnCandidates = find(contains(lvNew,"cn")); % fallback
end
if isempty(cnCandidates)
    error("No column containing 'Cn' found in new file.");
end

bestCnIdx = [];
bestCnScore = -1;

for j = cnCandidates(:)'
    raw = Tnew{:,j};
    raw = raw(validNew,:);

    % reject if datetime
    if isdatetime(raw)
        continue;
    end

    % convert to numeric safely
    try
        x = raw;
        if iscell(x), x = string(x); end
        if isstring(x) || ischar(x)
            x = str2double(string(x));
        end
        x = double(x);
        score = sum(isfinite(x));  % count numeric usable rows

        if score > bestCnScore
            bestCnScore = score;
            bestCnIdx = j;
        end
    catch
        % ignore
    end
end

if isempty(bestCnIdx) || bestCnScore < max(5, round(numel(dtNew)*0.3))
    error("Cn column detection failed (could not find a numeric-like Cn column).");
end

cnRaw = Tnew{:, bestCnIdx};
cnRaw = cnRaw(validNew,:);

% convert to numeric
if iscell(cnRaw), cnRaw = string(cnRaw); end
if isstring(cnRaw) || ischar(cnRaw)
    cnNew = str2double(string(cnRaw));
else
    cnNew = double(cnRaw);
end
cnNew = cnNew(:);

%% 5) Detect Daily vs Hourly (median time step)
dtSorted = sort(dtNew);
if numel(dtSorted) < 2
    error("Not enough datetime rows in new file.");
end
stepMed = median(diff(dtSorted));
isDaily = stepMed >= hours(20);

%% 6) Pick target DB table/sheet
if isDaily
    Tdb = Tdaily;
    sheetTarget = "Daily";
else
    Tdb = Thourly;
    sheetTarget = "Hourly";
end

dbVars = string(Tdb.Properties.VariableNames);
lvDb   = lower(dbVars);

%% 7) Find DB datetime column (prefer 'Date Time' exact-ish, else best datetime-like)
dbDtIdx = find(lvDb=="date time" | lvDb=="date_time", 1, "first");
if isempty(dbDtIdx)
    % keyword fallback
    dbDtIdx = find(contains(lvDb,"date") | contains(lvDb,"time"), 1, "first");
end
if isempty(dbDtIdx)
    % conversion fallback: choose column that converts to datetime with most valid rows
    best = [];
    bestV = -1;
    for i = 1:width(Tdb)
        col = Tdb{:,i};
        try
            if isdatetime(col)
                dt = col;
            elseif isnumeric(col)
                dt = datetime(col, "ConvertFrom","excel");
            else
                if iscell(col), col = string(col); end
                dt = datetime(string(col));
            end
            v = sum(~isnat(dt));
            if v > bestV
                bestV = v;
                best = i;
            end
        catch
        end
    end
    dbDtIdx = best;
end

if isempty(dbDtIdx)
    error("Datetime column not found in DB sheet %s.", sheetTarget);
end

dtDb = Tdb{:, dbDtIdx};
if ~isdatetime(dtDb)
    try
        if isnumeric(dtDb)
            dtDb = datetime(dtDb, "ConvertFrom","excel");
        else
            if iscell(dtDb), dtDb = string(dtDb); end
            dtDb = datetime(string(dtDb));
        end
    catch
        error("Could not convert DB datetime column to datetime.");
    end
end
dtDb = dtDb(:);

%% 8) Find DB Cn column (avoid date/time)
dbCnIdx = find(contains(lvDb,"cn") & ~contains(lvDb,"time") & ~contains(lvDb,"date"), 1, "first");
if isempty(dbCnIdx)
    % fallback: any cn
    dbCnIdx = find(contains(lvDb,"cn"), 1, "first");
end
if isempty(dbCnIdx)
    error("Cn column not found in DB sheet %s.", sheetTarget);
end

%% 9) Keep only NEW rows: newer than max DB datetime and not duplicate datetimes
dtDbValid = dtDb(~isnat(dtDb));
if isempty(dtDbValid)
    maxDb = datetime(0,0,0); % very old origin
else
    maxDb = max(dtDbValid);
end

maskKeep = (dtNew > maxDb) & ~ismember(dtNew, dtDbValid);

dtAdd = dtNew(maskKeep);
cnAdd = cnNew(maskKeep);

if isempty(dtAdd)
    fprintf("No new rows to append to %s.\n", sheetTarget);
    return;
end

%% 10) Build Tadd safely with correct height and types
n = numel(dtAdd);

% Create table with same vars and n rows
Tadd = Tdb(1:n,:);

% Fill all columns with missing values, preserving type
for k = 1:width(Tadd)
    x = Tadd{:,k};
    if isdatetime(x)
        Tadd{:,k} = NaT(n,1);
    elseif isnumeric(x)
        Tadd{:,k} = NaN(n,1);
    elseif isstring(x)
        Tadd{:,k} = strings(n,1);
    elseif iscell(x)
        Tadd{:,k} = cell(n,1);
    elseif islogical(x)
        Tadd{:,k} = false(n,1);
    else
        % for categorical or other types
        try
            Tadd{:,k} = repmat(missing, n, 1);
        catch
        end
    end
end

% Assign datetime
Tadd{:, dbDtIdx} = dtAdd(:);

% Assign Cn respecting DB column type
targetCol = Tdb{:, dbCnIdx};

if isdatetime(targetCol)
    error("DB Cn column seems datetime (dbCnIdx wrong). Please check DB headers.");
end

if isnumeric(targetCol)
    Tadd{:, dbCnIdx} = double(cnAdd(:));
elseif iscell(targetCol)
    Tadd{:, dbCnIdx} = cellstr(string(cnAdd(:)));
elseif isstring(targetCol)
    Tadd{:, dbCnIdx} = string(cnAdd(:));
else
    % fallback
    Tadd{:, dbCnIdx} = double(cnAdd(:));
end

%% 11) Append + sort
Tout = [Tdb; Tadd];
dtAll = Tout{:, dbDtIdx};
if ~isdatetime(dtAll)
    dtAll = datetime(dtAll);
end
[~,ord] = sort(dtAll);
Tout = Tout(ord,:);

%% 12) Write back only target sheet
writetable(Tout, dbFile, "Sheet", sheetTarget, "WriteMode","overwritesheet");

fprintf("✅ Appended %d rows to %s sheet.\n", n, sheetTarget);
fprintf("   NEW file datetime col: %s\n", Tnew.Properties.VariableNames{bestDtIdx});
fprintf("   NEW file Cn col:       %s\n", Tnew.Properties.VariableNames{bestCnIdx});
fprintf("   DB sheet datetime col: %s\n", Tdb.Properties.VariableNames{dbDtIdx});
fprintf("   DB sheet Cn col:       %s\n", Tdb.Properties.VariableNames{dbCnIdx});
fprintf("   Last appended datetime: %s\n", string(max(dtAdd)));