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

CREATE TABLE SoftwareTrigger
(
    Telescope TINYINT   UNSIGNED NOT NULL,
    NIGHT     INT       UNSIGNED NOT NULL,
    RUNID     MEDIUMINT UNSIGNED NOT NULL,
    Cnt500    INT       UNSIGNED NOT NULL,
    Cnt750    INT       UNSIGNED NOT NULL,
    Cnt1000   INT       UNSIGNED NOT NULL,
    Cnt1250   INT       UNSIGNED NOT NULL,
    Cnt1500   INT       UNSIGNED NOT NULL,
    Cnt2000   INT       UNSIGNED NOT NULL,
    Cnt5000   INT       UNSIGNED NOT NULL,
    PRIMARY KEY (Telescope, NIGHT, RUNID)
) 
AS
(
    SELECT
        Telescope,
    	NIGHT,
    	RUNID,
    	COUNT(IF(Amplitude>500,  1, NULL)) AS Cnt500,
   	COUNT(IF(Amplitude>750,  1, NULL)) AS Cnt750,
    	COUNT(IF(Amplitude>1000, 1, NULL)) AS Cnt1000,
        COUNT(IF(Amplitude>1250, 1, NULL)) AS Cnt1250,
        COUNT(IF(Amplitude>1500, 1, NULL)) AS Cnt1500,
        COUNT(IF(Amplitude>2000, 1, NULL)) AS Cnt2000,
        COUNT(IF(Amplitude>5000, 1, NULL)) AS Cnt5000
    FROM
    	`Callisto`
    GROUP BY
        Telescope,
    	NIGHT,
    	RUNID
    ORDER BY
        Telescope,
    	NIGHT,
    	RUNID
)