Offline Mode in your FlutterFlow app

				
					CREATE TABLE apartments (
    apartment_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    apartment_code VARCHAR(50) NOT NULL,
    apartment_address VARCHAR(255) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE rooms_dictionary (
    room_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    room_name VARCHAR(100) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE apartment_rooms (
    apartment_room_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    room_id UUID NOT NULL,
    apartment_id UUID NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (room_id) REFERENCES rooms_dictionary(room_id),
    FOREIGN KEY (apartment_id) REFERENCES apartments(apartment_id)
);


CREATE TABLE inspections (
    inspection_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    inspection_code VARCHAR(50) NOT NULL,
    apartment_id UUID NOT NULL,
    user_id UUID NOT NULL,
    inspection_status VARCHAR(50),
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (apartment_id) REFERENCES apartments(apartment_id)
    
    /*INSPECTION STATUSES:
     * NEW
     * IN PROGRESS
     * FINISHED
     * */
);

CREATE TABLE inspected_rooms (
    inspected_rooms_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    inspection_id UUID NOT NULL,
    apartment_room_id UUID NOT NULL,
    is_room_ok VARCHAR(10),
    is_room_inspected VARCHAR(10),
    room_image text,
    notes text,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (inspection_id) REFERENCES inspections(inspection_id),
    FOREIGN KEY (apartment_room_id) REFERENCES apartment_rooms(apartment_room_id)
    
    /*IS ROOM OK?:
     * YES
     * NO
     * */
    
    /*IS ROOM INSPECTED?:
     * YES
     * NO
     * */
    
);



CREATE TABLE users (
    id UUID PRIMARY KEY,
    name text,
    email text ,
    profile_image_link text,
    profile_image_link_expire_date text
    
);



INSERT INTO apartments (apartment_code, apartment_address)
VALUES
('A001', '123 Main St, New York, NY 10001'),
('A002', '456 Elm St, New York, NY 10002'),
('A003', '789 Oak St, New York, NY 10003');


INSERT INTO rooms_dictionary (room_name)
VALUES
('Bedroom 1'),
('Bedroom 2'),
('Bathroom 1'),
('Kitchen'),
('Living Room');


INSERT INTO apartment_rooms (room_id, apartment_id)
VALUES
('978ea9cf-23b2-4750-8d65-b16fedbb899a', '0da95faa-c910-48fd-9f42-63afe8794630'), 
('4fd8a934-fd0b-46f7-835b-bf778a4ac6b0', '0da95faa-c910-48fd-9f42-63afe8794630'), 
('16f3dd0a-7460-4ecc-af1b-13ca18dce1ee', '0da95faa-c910-48fd-9f42-63afe8794630'), 
('e4011843-5206-44f7-9f20-1da6cdf1df2e', '0da95faa-c910-48fd-9f42-63afe8794630'), 
('0ec1e4f5-0d59-48e8-beac-cb10393027bf', '0da95faa-c910-48fd-9f42-63afe8794630'),
('978ea9cf-23b2-4750-8d65-b16fedbb899a', 'af225af5-093d-4323-a154-a64d7bdee8d7'), 
('16f3dd0a-7460-4ecc-af1b-13ca18dce1ee', 'af225af5-093d-4323-a154-a64d7bdee8d7'), 
('e4011843-5206-44f7-9f20-1da6cdf1df2e', 'af225af5-093d-4323-a154-a64d7bdee8d7'), 
('0ec1e4f5-0d59-48e8-beac-cb10393027bf', 'af225af5-093d-4323-a154-a64d7bdee8d7'),
('978ea9cf-23b2-4750-8d65-b16fedbb899a', 'a5e18502-b62f-4bbd-992f-ccac48c68d31'), 
('16f3dd0a-7460-4ecc-af1b-13ca18dce1ee', 'a5e18502-b62f-4bbd-992f-ccac48c68d31'), 
('e4011843-5206-44f7-9f20-1da6cdf1df2e', 'a5e18502-b62f-4bbd-992f-ccac48c68d31'), 
('0ec1e4f5-0d59-48e8-beac-cb10393027bf', 'a5e18502-b62f-4bbd-992f-ccac48c68d31');


INSERT INTO inspections (inspection_code, apartment_id,user_id,inspection_status)
VALUES
('I001', '0da95faa-c910-48fd-9f42-63afe8794630','689930c5-f981-4639-ab83-014c916896bf','FINISHED'),
('I002', 'af225af5-093d-4323-a154-a64d7bdee8d7','689930c5-f981-4639-ab83-014c916896bf','FINISHED'),
('I003', 'a5e18502-b62f-4bbd-992f-ccac48c68d31','689930c5-f981-4639-ab83-014c916896bf','FINISHED');



INSERT INTO inspected_rooms (inspection_id, apartment_room_id,is_room_ok,is_room_inspected)
VALUES
('6b9e1a31-d164-4556-802f-2aa8b287a692', 'b410c16b-b722-4af8-bb4c-6e75d0470c58','YES','YES'), 
('6b9e1a31-d164-4556-802f-2aa8b287a692', 'f21c92e1-82e8-4c0e-93bc-11ecbf5fa139','YES','YES'), 
('28852acd-6dd3-4ad8-b6c0-034d311029ad', 'fc2c33e6-0c4f-40b6-8223-655cd0e24341','YES','YES'), 
('45bd5758-28cd-417b-83a0-4b8147ebf30d', 'a784d1df-ef32-42da-9275-bbf238dda9db','YES','YES');

commit;


--Функция для того, чтобы  создать Inspection Code

CREATE OR REPLACE FUNCTION generate_inspection_code()
RETURNS TRIGGER AS $$
DECLARE
    prefix CHAR(1) := 'I';
    index_num INTEGER;
    new_code TEXT;
BEGIN
    -- Get the maximum index number in the table
    SELECT COALESCE(MAX(SUBSTRING(inspection_code FROM 2)::INTEGER), 0) INTO index_num FROM inspections;

    -- Increment the index number
    index_num := index_num + 1;

    -- Format the index number based on whether it's greater than 1000
    IF index_num <= 1000 THEN
        new_code := prefix || LPAD(index_num::TEXT, 3, '0');
    ELSE
        new_code := prefix || index_num;
    END IF;

    -- Set the generated code to the inspection_code column of the new row
    NEW.inspection_code := new_code;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger to automatically generate the inspection code on insertion
CREATE TRIGGER before_insert_inspection_code
BEFORE INSERT ON inspections
FOR EACH ROW
EXECUTE FUNCTION generate_inspection_code();
;


--Get list of all apartments

CREATE OR REPLACE FUNCTION public.get_all_apartments()
 RETURNS text[]
 LANGUAGE plpgsql
AS $function$

declare
   apartments_array TEXT [];
  
  begin
  	select array_agg(apartment_id) into apartments_array from apartments order by apartment_code ;
  	return apartments_array;
  end;
 $function$
;
 
CREATE OR REPLACE FUNCTION public.get_all_apartments_labels()
 RETURNS text[]
 LANGUAGE plpgsql
AS $function$

declare
   apartments_array TEXT [];
  
  begin
  	select array_agg(apartment_code) into apartments_array from apartments order by apartment_code ;
  	return apartments_array;
  end;
 $function$
;

CREATE OR REPLACE FUNCTION public.get_last_inspection_code_index()
RETURNS integer
LANGUAGE plpgsql
AS $function$
DECLARE
    index_num INTEGER;
BEGIN
    SELECT COALESCE(MAX(SUBSTRING(inspection_code FROM 2)::INTEGER), 0) INTO index_num FROM inspections;
    RETURN index_num;
END;
$function$;





create or replace view inspections_main_info_view as
select i.inspection_id,
	   i.inspection_code,
	   i.inspection_status,
	   a.apartment_code,
	   a.apartment_address,
	   i.user_id,
	   i.created_at,
	   i.last_updated_at 
from inspections i 
inner join apartments a on i.apartment_id =a.apartment_id 
;

create or replace view apartment_rooms_view as
select ar.apartment_room_id,
	   ar.room_id,
	   ar.apartment_id,
	   rd.room_name
from apartment_rooms ar 
inner join rooms_dictionary rd on ar.room_id =rd.room_id 
;


create or replace view inspected_rooms_view as
select ir.inspection_id,
       ir.inspected_rooms_id,
       ir.is_room_ok,
       ir.is_room_inspected,
       ir.room_image,
       ir.notes,
       ir.created_at,
       ir.last_updated_at,
	   ar.apartment_room_id,
	   ar.room_id,
	   ar.apartment_id,
	   rd.room_name
from   inspected_rooms ir 
inner join apartment_rooms ar on ir.apartment_room_id =ar.apartment_room_id  
inner join rooms_dictionary rd on ar.room_id =rd.room_id 
;