duJY3kVz

1do $$
2begin
3
4    drop function if exists select_company_and_sectors(int);
5    drop function if exists update_company(int, json);
6    drop function if exists update_sectors(json);
7    drop table if exists employees;
8    drop table if exists sectors;
9    drop table if exists companies;
10
11    create table companies (
12        id int not null generated always as identity primary key,
13        user_id int not null,
14        name varchar(256) not null,
15        constraint fk_comapnies_user_id__indetity_user_id foreign key (user_id)
16        references identity.user (id)
17        on update no action
18        on delete no action
19    );
20    drop index if exists idx_companies_user_id;
21    create index idx_companies_user_id on companies using btree (user_id);
22
23    create table sectors (
24        id int not null generated always as identity primary key,
25        company_id int not null,
26        name varchar(256) not null,
27        constraint fk_sectors_company_id__companies_id foreign key (company_id)
28        references companies (id)
29        on update no action
30        on delete cascade
31    );
32    drop index if exists idx_sectors_company_id;
33    create index idx_sectors_company_id on sectors using btree (company_id);
34
35    create table employees (
36        id int not null generated always as identity primary key,
37        sector_id int not null,
38        user_id int null,
39        first_name varchar(256) not null,
40        last_name varchar(256) not null,
41        constraint fk_employees_sector_id__sectors_id foreign key (sector_id)
42        references sectors (id)
43        on update no action
44        on delete cascade,
45        constraint fk_employees_user_id__indetity_user_id foreign key (user_id)
46        references identity.user (id)
47        on update no action
48        on delete no action
49    );
50    drop index if exists idx_employees_sector_id;
51    create index idx_employees_sector_id on employees using btree (sector_id);
52
53end
54$$;
55
56create function select_company_and_sectors(_user_id int)
57returns json as
58$$
59declare _company json;
60declare _company_id int;
61begin
62    select to_json(c), c.id
63    into _company, _company_id
64    from (
65        select id, name from companies c where user_id = _user_id limit 1
66    ) c;
67
68    return json_build_object(
69        'company', _company,
70        'sectors', (
71            select coalesce(json_agg(s), '[]') from (
72                select id, name from sectors where company_id = _company_id order by company_id
73            ) s
74        )
75    );
76end
77$$ language plpgsql;
78
79create or replace function update_company(_user_id int, _company json)
80returns json as
81$$
82declare _result json;
83begin
84    with cte as (
85        update companies set name = _company->>'name' where user_id = _user_id
86        returning id, name
87    )
88    select to_json(cte) into _result from cte;
89
90    if _result is null then
91        with cte as (
92            insert into companies (name, user_id) values (_company->>'name', _user_id)
93            returning id, name
94        )
95        select to_json(cte) into _result from cte;
96    end if;
97
98    return _result;
99end
100$$ language plpgsql;
101
102create function update_sectors(_sector json)
103returns json as
104$$
105declare _result json;
106declare _company_id int;
107declare _id int;
108begin
109
110    _company_id = (_sector->>'company_id')::int;
111    _id = (_sector->>'id')::int;
112    raise info '_id=%, _company_id=%', _id, _company_id;
113
114    if _sector->>'id' is null then
115        with cte as (
116            insert into sectors (name, company_id) values (_sector->>'name', _company_id)
117            returning id, name, company_id
118        )
119        select to_json(cte) into _result from cte;
120        
121    else
122        with cte as (
123            update sectors 
124            set name = _sector->>'name', company_id = _company_id
125            where id = _id
126            returning id, name, company_id
127        )
128        select to_json(cte) into _result from cte;
129    end if;
130
131    return _result;
132end
133$$ language plpgsql;