#!/usr/bin/env -S mysql -h ihp-pc45.ethz.ch

-- You might want to change the table name
CREATE TABLE Calibration 
(
   WITH Table1 AS
   (
      SELECT
         -- NIGHT and RUNID of the data file
         R1.Telescope AS Telescope,
         R1.NIGHT     AS NIGHT,
         R1.RUNID     AS RUNID,
         -- NIGHT and RUNID of the DRS file
         R2.NIGHT     AS DrsNight,
         R2.RUNID     AS DrsRunID,
         -- Time difference between the two
         LEAST(
             ABS(UNIX_TIMESTAMP(R2.`DATE-OBS`)-UNIX_TIMESTAMP(R1.`DATE-OBS`)),
             ABS(UNIX_TIMESTAMP(R2.`DATE-OBS`)-UNIX_TIMESTAMP(R1.`DATE-END`)),
             ABS(UNIX_TIMESTAMP(R2.`DATE-END`)-UNIX_TIMESTAMP(R1.`DATE-OBS`)),
             ABS(UNIX_TIMESTAMP(R2.`DATE-END`)-UNIX_TIMESTAMP(R1.`DATE-END`))
         ) AS DeltaT,
         -- Assign a row number to each combination of data-file and drs-file
         -- after sorting them by time difference
         ROW_NUMBER() OVER(PARTITION BY R1.NIGHT, R1.RUNID ORDER BY
            LEAST(
            ABS(UNIX_TIMESTAMP(R2.`DATE-OBS`)-UNIX_TIMESTAMP(R1.`DATE-OBS`)),
            ABS(UNIX_TIMESTAMP(R2.`DATE-OBS`)-UNIX_TIMESTAMP(R1.`DATE-END`)),
            ABS(UNIX_TIMESTAMP(R2.`DATE-END`)-UNIX_TIMESTAMP(R1.`DATE-OBS`)),
            ABS(UNIX_TIMESTAMP(R2.`DATE-END`)-UNIX_TIMESTAMP(R1.`DATE-END`))
            )
         ) AS RowNumber
      FROM
         RawData R1
      -- Join the table with itself to get all possible combinations of runs
      CROSS JOIN
         RawData R2
      -- Join the DRS files with the table that tells you whether they can be used
      -- if the table does not (yet) exists, this can be omitted
      LEFT JOIN
         DoNotUse ON (R2.NIGHT=DoNotUse.NIGHT AND R2.RUNID=DoNotUse.RUNID)
      WHERE
         -- Of course claibration must be from the same telescope
         R1.Telescope=R2.Telescope
      AND
         -- Select all combinations that have a data file in R1 and a drs file (at step 1) in R2
         ISNULL(R1.DRSSTEP) AND R2.DRSSTEP=1
      AND 
         -- Only DRS files with 100 events and the correct ROI are considered
         R2.ZNAXIS2=1000 AND R1.NROI=R2.NROI 
      AND
         -- Check if they are valid
         IFNULL(DoNotUse.WrongVoltage,false)!=true
      -- This can be used to restrict the number of combinations     
      -- AND ABS(UNIX_TIMESTAMP(R2.`DATE-OBS`)-UNIX_TIMESTAMP(R1.`DATE-OBS`))<78*3600
      ORDER BY
         R1.Telescope,
         R1.NIGHT,
         R1.RUNID,
         DeltaT
    )
    SELECT
       Telescope,
       NIGHT,
       RUNID,
       DrsNight,
       DrsRunId,
       DeltaT/3600e0 AS DeltaT
    FROM
       Table1
    WHERE
       -- Select only the rows with the smallest DeltaT
       RowNumber=1
)