tp5/serverinit/migrate.sql

DROP TABLE IF exists settings;
-- Create the "settings" table
CREATE TABLE settings (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid() UNIQUE,
    user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
    field TEXT NOT NULL,
    value TEXT NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    created_at TIMESTAMPTZ DEFAULT now() NOT NULL
);
ALTER TABLE settings
ADD CONSTRAINT unique_user_field UNIQUE (user_id, field);


-- Enable Row-Level Security (RLS) on the "settings" table
ALTER TABLE settings ENABLE ROW LEVEL SECURITY;

-- Create a policy to allow each user to access only their data
CREATE POLICY select_policy ON settings
    FOR SELECT
    USING (auth.uid() = user_id);

-- Create a policy to allow each user to insert their data
CREATE POLICY insert_policy ON settings
    FOR INSERT
    WITH CHECK (auth.uid() = user_id);

-- Create a policy to allow each user to update only their data
CREATE POLICY update_policy ON settings
    FOR UPDATE
    USING (auth.uid() = user_id);

-- Create a policy to allow each user to delete only their data
CREATE POLICY delete_policy ON settings
    FOR DELETE
    USING (auth.uid() = user_id);

-- Add a rule to automatically update the "updated_at" column on update
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = now();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_updated_at
BEFORE UPDATE ON settings
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();


-- csvhichupdate
-- Create the csvhichupdates table
CREATE TABLE csvhichupdates (

    id UUID PRIMARY KEY DEFAULT gen_random_uuid() UNIQUE,
--    id SERIAL PRIMARY KEY,  -- Auto-incrementing unique identifier
    filename TEXT NOT NULL,  -- Filename as text
    updated_at TIMESTAMPTZ DEFAULT NOW()  -- Timestamp with timezone, default to current time
);

-- Enable Realtime on the csvhichupdates table
-- SELECT pg_create_logical_replication_slot('csvhichupdates_slot', 'pgoutput');

-- Grant permissions for RLS
ALTER TABLE csvhichupdates ENABLE ROW LEVEL SECURITY;

-- Create policies to allow access for anon, service_key, and authenticated users
CREATE POLICY select_policy ON csvhichupdates
    FOR SELECT
    USING (auth.role() IN ('anon', 'service_key', 'authenticated'));

--create buckets
INSERT INTO storage.buckets (id, name)
VALUES ('csv', 'csv');
INSERT INTO storage.buckets (id, name)
VALUES ('csvhich', 'csvhich');
INSERT INTO storage.buckets (id, name)
VALUES ('csvhich_archive', 'csvhich_archive');

--create bucket policies
CREATE POLICY "read_csv 2492_0" ON "storage"."objects" USING (((bucket_id = 'csv'::text) OR (bucket_id = 'csvhich'::text) ));
CREATE POLICY "insert_in_csv 2492_0" ON storage.objects FOR INSERT TO service_role, authenticated WITH CHECK (bucket_id = 'csv' OR bucket_id = 'csvhich' );
CREATE POLICY "insert_in_csv 2492_1" ON storage.objects FOR UPDATE TO service_role, authenticated USING (bucket_id = 'csv' OR bucket_id = 'csvhich' );


-- tables csv
create table IF NOT EXISTS public.aclegs_csv (
	"leg_no" TEXT,
	"fn_carrier" TEXT,
	"fn_number" TEXT,
	"fn_suffix" TEXT,
	"day_of_origin" TEXT,
	"ac_owner" TEXT,
	"ac_subtype" TEXT,
	"ac_version" TEXT,
	"ac_registration" TEXT,
	"dep_ap_actual" TEXT,
	"dep_ap_sched" TEXT,
	"dep_dt_est" TEXT,
	"dep_sched_dt" TEXT,
	"arr_ap_actual" TEXT,
	"arr_ap_sched" TEXT,
	"arr_dt_est" TEXT,
	"arr_sched_dt" TEXT,
	"slot_time_actual" TEXT,
	"leg_type" TEXT,
	"status" TEXT,
	"employer_cockpit" TEXT,
	"employer_cabin" TEXT,
	"cycles" TEXT,
	"delay_code_01" TEXT,
	"delay_code_02" TEXT,
	"delay_code_03" TEXT,
	"delay_code_04" TEXT,
	"delay_time_01" TEXT,
	"delay_time_02" TEXT,
	"delay_time_03" TEXT,
	"delay_time_04" TEXT,
	"subdelay_code_01" TEXT,
	"subdelay_code_02" TEXT,
	"subdelay_code_03" TEXT,
	"subdelay_code_04" TEXT,
	"pax_booked_c" TEXT,
	"pax_booked_y" TEXT,
	"pax_booked_trs_c" TEXT,
	"pax_booked_trs_y" TEXT,
	"pad_booked_c" TEXT,
	"pad_booked_y" TEXT,
	"offblock_dt_a" TEXT,
	"airborne_dt_a" TEXT,
	"landing_dt_a" TEXT,
	"onblock_dt_a" TEXT,
	"offblock_dt_f" TEXT,
	"airborne_dt_f" TEXT,
	"landing_dt_f" TEXT,
	"onblock_dt_f" TEXT,
	"offblock_dt_m" TEXT,
	"airborne_dt_m" TEXT,
	"landing_dt_m" TEXT,
	"onblock_dt_m" TEXT,
	"eet" TEXT,
    id SERIAL PRIMARY KEY
);

create table IF NOT EXISTS public.pnlegs_csv (
date TEXT,
tlc TEXT,
actype TEXT,
al TEXT,
fnum TEXT,
ddep TEXT,
hdep TEXT,
ddes TEXT,
hdes TEXT,
dep TEXT,
des TEXT,
label TEXT,
type TEXT,
id SERIAL PRIMARY KEY
);

create table IF NOT EXISTS public.pnlegsmois_csv (
date TEXT,
tlc TEXT,
actype TEXT,
al TEXT,
fnum TEXT,
ddep TEXT,
hdep TEXT,
ddes TEXT,
hdes TEXT,
dep TEXT,
des TEXT,
label TEXT,
type TEXT,
id SERIAL PRIMARY KEY
);

create table IF NOT EXISTS public.licences_csv (
tlc TEXT,
fname TEXT,
mname TEXT,
lname TEXT,
expire TEXT,
ac TEXT,
college TEXT,
base TEXT,
id SERIAL PRIMARY KEY
);






CREATE OR REPLACE VIEW pnlegs_csv_clean AS
select TO_DATE(DATE,'DD/MM/YYYY') as date,
       tlc,
       actype,
       al,
       fnum,
       TO_TIMESTAMP(ddep || COALESCE(hdep, '0000'), 'DD/MM/YYYYHH24MI') AT TIME ZONE 'UTC' as hdep,
       TO_TIMESTAMP(ddes || COALESCE(hdes, '0000'), 'DD/MM/YYYYHH24MI') AT TIME ZONE 'UTC' as hdes, 
       dep,
       des,
       label,
       TYPE
       FROM pnlegs_csv;


CREATE OR REPLACE VIEW aclegs_csv_clean AS
	SELECT 
		fn_carrier AS al  ,
		fn_number AS fnum  ,
		DATE(TO_TIMESTAMP(DAY_OF_ORIGIN,'YYYY-MM-DD HH24:MI:SS') AT TIME ZONE 'UTC' ) AS date ,
		json_build_object('owner',ac_owner,'subtype',ac_subtype,'version',ac_version) AS ac  ,
		ac_registration AS reg,
		COALESCE(dep_ap_actual,dep_ap_sched,null) AS dep  ,
		COALESCE(arr_ap_actual,arr_ap_sched,null) AS des  ,
		TO_TIMESTAMP(dep_sched_dt,'YYYY-MM-DD HH24:MI:SS') AT TIME ZONE 'UTC' AS hdep_sched  ,
		TO_TIMESTAMP(arr_sched_dt,'YYYY-MM-DD HH24:MI:SS') AT TIME ZONE 'UTC'  AS hdes_sched  ,
		COALESCE(COALESCE(offblock_dt_m,offblock_dt_a,offblock_dt_f,NULL),dep_dt_est,dep_sched_dt)::timestamp AS hdep  ,

		COALESCE(
			COALESCE(onblock_dt_m,onblock_dt_a,onblock_dt_f,NULL)::timestamp,
			(CASE
				WHEN COALESCE(landing_dt_m,landing_dt_a,landing_dt_f,null) IS NOT NULL THEN 
					COALESCE(landing_dt_m,landing_dt_a,landing_dt_f,NULL)::timestamp+taxi_in(arr_ap_actual)::INTERVAL 
				WHEN COALESCE(airborne_dt_m,airborne_dt_a,airborne_dt_f,null) IS NOT NULL AND EET IS NOT NULL THEN 
					COALESCE(airborne_dt_m,airborne_dt_a,airborne_dt_f,NULL)::TIMESTAMP+(EET||' minutes')::INTERVAL +taxi_in(arr_ap_actual)::INTERVAL
				WHEN COALESCE(offblock_dt_m,offblock_dt_a,offblock_dt_f,null) IS NOT NULL AND EET IS NOT NULL THEN 
					COALESCE(offblock_dt_m,offblock_dt_a,offblock_dt_f,NULL)::timestamp+taxi_out(dep_ap_actual)::INTERVAL+(EET||' minutes')::INTERVAL +taxi_in(arr_ap_actual)::INTERVAL
				WHEN COALESCE(offblock_dt_m,offblock_dt_a,offblock_dt_f,null) IS NOT NULL AND EET IS NULL THEN
					COALESCE(offblock_dt_m,offblock_dt_a,offblock_dt_f,NULL)::timestamp+(arr_sched_dt::TIMESTAMP-dep_sched_dt::TIMESTAMP)
				WHEN ( (dep_dt_est IS NOT NULL) AND (EET IS NOT NULL) ) THEN
					dep_dt_est::TIMESTAMP + taxi_out(dep_ap_actual)::INTERVAL + (EET||' minutes')::INTERVAL + taxi_in(arr_ap_actual)::INTERVAL  
				WHEN ( (dep_dt_est IS NOT NULL) AND (EET IS NULL) ) THEN 
					dep_dt_est::TIMESTAMP + (arr_sched_dt::TIMESTAMP-dep_sched_dt::TIMESTAMP)  
				WHEN (  (EET IS NOT NULL) ) THEN
					dep_sched_dt::TIMESTAMP + taxi_out(dep_ap_actual)::INTERVAL + (EET||' minutes')::INTERVAL + taxi_in(arr_ap_actual)::INTERVAL  

				ELSE 
					arr_sched_dt::timestamp
			END)::timestamp
		)::timestamp
		AS hdes  ,
		
		nullif(array_remove(ARRAY [delay_code_01 || '|' || subdelay_code_01 || '|' || delay_time_01,delay_code_02 || '|' || subdelay_code_02 || '|' || delay_time_02,delay_code_03 || '|' || subdelay_code_03 || '|' || delay_time_03,delay_code_04 || '|' || subdelay_code_04 || '|' || delay_time_04],'||'),'{}') AS delay  ,
	
		pax_booked_c AS pax_c  ,
		pax_booked_y AS pax_y  ,
		COALESCE(offblock_dt_m,offblock_dt_a,offblock_dt_f,NULL)::timestamp AS block_off  ,
		COALESCE(airborne_dt_m,airborne_dt_a,airborne_dt_f,null)::timestamp AS block_air  ,
		COALESCE(landing_dt_m,landing_dt_a,landing_dt_f,null)::timestamp AS block_ldg  ,
		COALESCE(onblock_dt_m,onblock_dt_a,onblock_dt_f,null)::timestamp AS block_on  ,
		slot_time_actual::timestamp AS ctot  ,
		'1 minutes'::interval * eet::integer  AS EET  ,
			CASE
				WHEN COALESCE(onblock_dt_m,onblock_dt_a,onblock_dt_f,null) IS NOT NULL THEN 'ARRIVED'
				WHEN COALESCE(landing_dt_m,landing_dt_a,landing_dt_f,null) IS NOT NULL THEN 'LANDED'
				WHEN COALESCE(airborne_dt_m,airborne_dt_a,airborne_dt_f,null) IS NOT NULL THEN 'AIRBORN'
				WHEN COALESCE(offblock_dt_m,offblock_dt_a,offblock_dt_f,null) IS NOT NULL THEN 'DEPARTED'
				WHEN ( (dep_dt_est IS NOT NULL) AND (dep_sched_dt <> dep_dt_est) ) THEN 'DELAYED'
				else
				'SCHED'
			END
		 AS status ,
		json_build_object('FN_CARRIER',FN_CARRIER,'FN_NUMBER',FN_NUMBER,'DAY_OF_ORIGIN',DAY_OF_ORIGIN,'AC_OWNER',AC_OWNER,'AC_SUBTYPE',AC_SUBTYPE,'AC_VERSION',AC_VERSION,'AC_REGISTRATION',AC_REGISTRATION,'DEP_AP_ACTUAL',DEP_AP_ACTUAL,'DEP_AP_SCHED',DEP_AP_SCHED,'DEP_DT_EST',DEP_DT_EST,'DEP_SCHED_DT',DEP_SCHED_DT,'ARR_AP_ACTUAL',ARR_AP_ACTUAL,'ARR_AP_SCHED',ARR_AP_SCHED,'ARR_DT_EST',ARR_DT_EST,'ARR_SCHED_DT',ARR_SCHED_DT,'SLOT_TIME_ACTUAL',SLOT_TIME_ACTUAL,'LEG_TYPE',LEG_TYPE,'EMPLOYER_COCKPIT',EMPLOYER_COCKPIT,'EMPLOYER_CABIN',EMPLOYER_CABIN,'DELAY_CODE_01',DELAY_CODE_01,'DELAY_CODE_02',DELAY_CODE_02,'DELAY_CODE_03',DELAY_CODE_03,'DELAY_CODE_04',DELAY_CODE_04,'DELAY_TIME_01',DELAY_TIME_01,'DELAY_TIME_02',DELAY_TIME_02,'DELAY_TIME_03',DELAY_TIME_03,'DELAY_TIME_04',DELAY_TIME_04,'SUBDELAY_CODE_01',SUBDELAY_CODE_01,'SUBDELAY_CODE_02',SUBDELAY_CODE_02,'SUBDELAY_CODE_03',SUBDELAY_CODE_03,'SUBDELAY_CODE_04',SUBDELAY_CODE_04,'PAX_BOOKED_C',PAX_BOOKED_C,'PAX_BOOKED_Y',PAX_BOOKED_Y,'OFFBLOCK_DT_A',OFFBLOCK_DT_A,'AIRBORNE_DT_A',AIRBORNE_DT_A,'LANDING_DT_A',LANDING_DT_A,'ONBLOCK_DT_A',ONBLOCK_DT_A,'OFFBLOCK_DT_F',OFFBLOCK_DT_F,'AIRBORNE_DT_F',AIRBORNE_DT_F,'LANDING_DT_F',LANDING_DT_F,'ONBLOCK_DT_F',ONBLOCK_DT_F,'OFFBLOCK_DT_M',OFFBLOCK_DT_M,'AIRBORNE_DT_M',AIRBORNE_DT_M,'LANDING_DT_M',LANDING_DT_M,'ONBLOCK_DT_M',ONBLOCK_DT_M,'EET',EET) AS DATA  
	FROM aclegs_csv;