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;