% MATLAB script to read spectrophotometer pH data and prepare for SQL upload

% Read the data file
% Skip the first line (pHSys_V4.0), use second line as variable names
opts = detectImportOptions('spec_ph_composite.txt', 'Delimiter', '\t');

% Start reading from line 2 (skip the version line)
opts.DataLines = [3 Inf];

% Specify data types for each column
opts = setvartype(opts, 'Comments', 'string');
opts = setvartype(opts, 'DateTime', 'string');
% opts.DatetimeFormat = 'M/d/yyyy h:mm a';  % Format: 4/14/2025 9:30 PM
opts = setvartype(opts, 'DyeID', 'string');
opts = setvartype(opts, 'DyeCalcEqn', 'string');
opts = setvartype(opts, 'SampID', 'string');
% opts = setvartype(opts, 'Station', 'string');
% opts = setvartype(opts, 'Niskin', 'string');

% All absorbance and numeric columns are doubles
numericCols = {'A578_Blank1', 'A434_Blank1', 'A730_Blank1', 'A487_Blank1', ...
               'A578_Blank2', 'A434_Blank2', 'A730_Blank2', 'A487_Blank2', ...
               'A578_Blank3', 'A434_Blank3', 'A730_Blank3', 'A487_Blank3', ...
               'A578_Samp1', 'A434_Samp1', 'A730_Samp1', 'A487_Samp1', ...
               'A578_Samp2', 'A434_Samp2', 'A730_Samp2', 'A487_Samp2', ...
               'A578_Samp3', 'A434_Samp3', 'A730_Samp3', 'A487_Samp3', ...
               'Station', 'Niskin', 'DyeAdded', 'DyePort', 'pKa', ...
               'A578_used', 'A434_used', 'A730_used', 'A487_used', ...
               'Temp_Blank', 'Temp_Blank_std', 'Temp_Samp', 'Temp_Samp_std', ...
               'Temp_Bath_set', 'Temp_used', 'Salinity', ...
               'Dye_slope', 'Dye_Intercept', 'pH_tot'};

for i = 1:length(numericCols)
    opts = setvartype(opts, numericCols{i}, 'double');
end

% n_8453Errors should be integer
opts = setvartype(opts, 'n_8453Errors', 'double');  % Will convert to int later

% Read the table
data = readtable('spec_ph_composite.txt', opts);

% data.DateTime.Format = "uuuu-MM-dd HH:mm:SS";
% Replace empty strings with missing for string columns
% data.Comments = replace(data.Comments, '', missing);
% data.SampID = replace(data.SampID, '', missing);

% Display summary
fprintf('Successfully loaded %d rows of data\n', height(data));
% fprintf('Date range: %s to %s\n', ...
%     datestr(min(data.DateTime)), datestr(max(data.DateTime)));

% Display first few rows
disp('First 5 rows:');
disp(data(1:min(5,height(data)), :));

% Optional: Save as MAT file for later use
% save('spec_ph_data.mat', 'data');
% fprintf('Data saved to spec_ph_data.mat\n');

%% Upload to SQL Server - New Rows Only
conn = connect_penguin();

if isopen(conn)
    fprintf('Database connection successful\n');
    tablename = 'k0_seawater_spectrophotometer_ph';

    % --- Fetch existing keys from the database ---
    existingData = fetch(conn, sprintf('SELECT DateTime FROM %s', tablename));

    if isempty(existingData)
        newRows = data;  % Nothing in DB yet, upload everything
    else
        % Create a composite key for both datasets to compare
        existingKeys = existingData.DateTime;
        localKeys    = data.DateTime;

        % Find rows in local data whose key is NOT in the existing keys
        isNew = ~ismember(localKeys, existingKeys);
        newRows = data(isNew, :);
    end

    % --- Upload only new rows ---
    if height(newRows) == 0
        fprintf('No new rows to upload — database is already up to date.\n');
    else
        sqlwrite(conn, tablename, newRows);
        fprintf('Uploaded %d new rows to %s (skipped %d existing)\n', ...
            height(newRows), tablename, height(data) - height(newRows));
    end

    close(conn);
else
    error('Failed to connect to database: %s', conn.Message);
end