CREATE EXTENSION IF NOT EXISTS "pgcrypto";
CREATE EXTENSION citext;


CREATE TABLE "roles" (
  "id" UUID DEFAULT gen_random_uuid(),
  "name" VARCHAR UNIQUE NOT NULL,
  PRIMARY KEY ("id")
);

CREATE TABLE "companies" (
  "id" UUID DEFAULT gen_random_uuid(),
  "name" TEXT,
  "description" TEXT,
  "address" TEXT,
  "city" TEXT,
  "zip" TEXT,
  "phone" TEXT,
  "website" TEXT,
  "linkedin" TEXT,
  PRIMARY KEY ("id")
);

CREATE TABLE "students" (
  "id" UUID DEFAULT gen_random_uuid(),
  "name" TEXT,
  "course_of_study" TEXT,
  "semester" text,
  "website" TEXT,
  "linkedin" TEXT,
  "address" TEXT,
  "city" TEXT,
  "zip" TEXT,
  "phone" TEXT,
  "bio" TEXT,
  PRIMARY KEY ("id")
);

CREATE TABLE "managers" (
  "id" UUID DEFAULT gen_random_uuid(),
  "company_id" UUID REFERENCES "companies"("id"),
  "name" TEXT NOT NULL,
  "phone" TEXT,
  "linkedin" TEXT,
  PRIMARY KEY ("id")
);

CREATE INDEX "managers_company_id_FK" ON  "managers" ("company_id");

CREATE TABLE "keywords" (
  "id" UUID DEFAULT gen_random_uuid(),
  "key" VARCHAR UNIQUE NOT NULL,
  "description" TEXT,
  PRIMARY KEY ("id")
);

CREATE TABLE "working_hours_types" (
  "id" UUID DEFAULT gen_random_uuid(),
  "type" VARCHAR UNIQUE NOT NULL,
  PRIMARY KEY ("id")
);

CREATE TABLE "positions" (
  "id" UUID DEFAULT gen_random_uuid(),
  "company_id" UUID REFERENCES "companies"("id"),
  "working_hours_type_id" UUID REFERENCES "working_hours_types"("id"),
  "title" VARCHAR NOT NULL,
  "description" TEXT NOT NULL,
  "benefits" TEXT,
  PRIMARY KEY ("id")
);

CREATE INDEX "positions_company_id_working_hours_type_id_FK" ON  "positions" ("company_id", "working_hours_type_id");


CREATE TABLE "conference_locations" (
  "id" UUID DEFAULT gen_random_uuid(),
  "address" TEXT NOT NULL,
  "city" TEXT NOT NULL,
  "zip" TEXT NOT NULL,
  PRIMARY KEY ("id")
);


CREATE TABLE "conferences" (
  "id" UUID DEFAULT gen_random_uuid(),
  "conference_location_id" UUID REFERENCES "conference_locations"("id"),
  "title" TEXT NOT NULL,
  "description" TEXT,
  PRIMARY KEY ("id")
);

CREATE INDEX "conference_conference_location_FK" ON  "conferences" ("conference_location_id");


CREATE TABLE "conference_contacts" (
  "id" UUID DEFAULT gen_random_uuid(),
  "name" TEXT NOT NULL,
  "title" TEXT,
  "phone" TEXT,
  "email" citext,
  "conference_id" UUID REFERENCES "conferences"("id"),
  PRIMARY KEY ("id")
);

create unique index on conference_contacts ((lower(email)));

CREATE INDEX "conference_contacts_conference_id_FK" ON  "conference_contacts" ("conference_id");

CREATE TABLE "admins" (
  "id" UUID DEFAULT gen_random_uuid(),
  "name" TEXT NOT NULL,
  PRIMARY KEY ("id")
);

CREATE TABLE "news_articles" (
  "id" UUID DEFAULT gen_random_uuid(),
  "admin_id" UUID NOT NULL REFERENCES "admins"("id"),
  "headline" TEXT NOT NULL,
  "body" TEXT NOT NULL,
  PRIMARY KEY ("id")
);

CREATE INDEX "news_articles_admins_id_FK" on "news_articles" ("admin_id");

CREATE TABLE "notification_sources" (
  "id" UUID DEFAULT gen_random_uuid(),
  "company_id" UUID REFERENCES "companies"("id"),
  "student_id" UUID REFERENCES "students"("id"),
  "news_article_id" UUID REFERENCES "news_articles"("id"),
  "position_id" UUID REFERENCES "positions"("id"),
  manager_id uuid references managers(id),
  conference_id uuid references conferences(id),
  check(
      (
        company_id is not null and
        student_id is null and
        news_article_id is null and
        position_id is null and
        manager_id is null and
        conference_id is null
      ) or
      (
        student_id is not null and
        company_id is null and
        news_article_id is null and
        position_id is null and
        manager_id is null and
        conference_id is null
      ) or
      (
        news_article_id is not null and
        company_id is null and
        student_id is null and
        position_id is null and
        manager_id is null and
        conference_id is null
      ) or
      (
        position_id is not null and
        company_id is null and
        student_id is null and
        news_article_id is null and
        manager_id is null and
        conference_id is null
      ) or
      (
        manager_id is not null and
        company_id is null and
        student_id is null and
        news_article_id is null and
        position_id is null and
        conference_id is null
      ) or
      (
        manager_id is null and
        company_id is null and
        student_id is null and
        news_article_id is null and
        position_id is null and
        conference_id is not null
      )
    ),
  PRIMARY KEY ("id")
);

CREATE INDEX "notification_sources_company_student_news_article_position_FK" ON  "notification_sources" ("company_id", "student_id", "news_article_id", "position_id");

CREATE TABLE "activity_types" (
  "id" UUID DEFAULT gen_random_uuid(),
  "type" VARCHAR UNIQUE NOT NULL,
  PRIMARY KEY ("id")
);

CREATE TABLE "notifications" (
  "id" UUID DEFAULT gen_random_uuid(),
  "notification_source_id" UUID REFERENCES "notification_sources"("id"),
  "activity_type_id" UUID REFERENCES "activity_types"("id"),
  "timestamp" TIMESTAMP NOT NULL default now(),
  PRIMARY KEY ("id")
);

CREATE INDEX "notifications_notification_source_id_activity_type_id_FK" ON  "notifications" ("notification_source_id", "activity_type_id");

CREATE TABLE "manager_notifications" (
  "id" UUID DEFAULT gen_random_uuid(),
  "notification_id" UUID REFERENCES "notifications"("id"),
  "manager_id" UUID REFERENCES "managers"("id"),
  "seen" TIMESTAMP,
  PRIMARY KEY ("id")
);

CREATE INDEX "manager_notifications_notification_id_manager_id_FK" ON  "manager_notifications" ("notification_id", "manager_id");

CREATE TABLE "company_notifications" (
  "id" UUID DEFAULT gen_random_uuid(),
  "notification_id" UUID REFERENCES "notifications"("id"),
  "company_id" UUID REFERENCES "companies"("id"),
  "seen" TIMESTAMP,
  PRIMARY KEY ("id")
);

CREATE INDEX "company_notifications_notification_id_company_id_FK" ON  "company_notifications" ("notification_id", "company_id");

CREATE TABLE "interested_students" (
  "id" UUID DEFAULT gen_random_uuid(),
  "company_id" UUID REFERENCES "companies"("id"),
  "student_id" UUID REFERENCES "students"("id"),
  PRIMARY KEY ("id")
);

CREATE INDEX "interested_students_company_id_student_id_FK" ON  "interested_students" ("company_id", "student_id");

CREATE TABLE "conference_dates" (
  "id" UUID DEFAULT gen_random_uuid(),
  "from_date" DATE NOT NULL,
  "to_date" DATE NOT NULL,
  "conference_id" UUID REFERENCES "conferences"("id"),
  PRIMARY KEY ("id")
);

CREATE INDEX "conference_dates_conference_id_FK" ON  "conference_dates" ("conference_id");

CREATE TABLE "company_contacts" (
  "id" UUID DEFAULT gen_random_uuid(),
  "company_id" UUID REFERENCES "companies"("id"),
  "name" TEXT NOT NULL,
  "phone" TEXT,
  "email" citext,
  PRIMARY KEY ("id")
);

create unique index on company_contacts ((lower(email)));
CREATE INDEX "company_contacts_company_id_FK" ON  "company_contacts" ("company_id");

CREATE TABLE "right_types" (
  "id" UUID DEFAULT gen_random_uuid(),
  "type" VARCHAR UNIQUE NOT NULL,
  PRIMARY KEY ("id")
);

CREATE TABLE "student_approved_rights" (
  "id" UUID DEFAULT gen_random_uuid(),
  "student_id" UUID NOT NULL REFERENCES "students"("id"),
  "right_type_id" UUID NOT NULL REFERENCES "right_types"("id"),
  PRIMARY KEY ("id")
);

CREATE INDEX "approved_rights_student_id_right_type_id_FK" ON  "student_approved_rights" ("student_id", "right_type_id");

create table student_approved_rights_values (
  id uuid default gen_random_uuid(),
  value text not null,
  right_type_id uuid not null references right_types(id)
);

create index student_approved_rights_values_rights_id_FK on student_approved_rights_values (right_type_id);

CREATE TABLE "work_experiences" (
  "id" UUID DEFAULT gen_random_uuid(),
  "student_id" UUID REFERENCES "students"("id"),
  "company_name" TEXT NOT NULL,
  "position" TEXT NOT NULL,
  "years_of_exp" SMALLINT check(years_of_exp > 0) NOT NULL,
  PRIMARY KEY ("id")
);

CREATE INDEX "work_experiences_student_id_FK" ON  "work_experiences" ("student_id");

CREATE TABLE "industry_categories" (
  "id" UUID DEFAULT gen_random_uuid(),
  "name" TEXT UNIQUE NOT NULL,
  "description" TEXT,
  PRIMARY KEY ("id")
);

create table positions_contained_categories (
  id uuid default gen_random_uuid(),
  position_id uuid not null references positions(id),
  industry_category_id uuid not null references industry_categories(id),
  primary key (id)
);

create index positions_contained_categories_positions_categories_FK on positions_contained_categories (position_id, industry_category_id);

create table work_experience_contained_categories (
  id uuid default gen_random_uuid(),
  work_experience_id uuid not null references work_experiences(id),
  industry_category_id uuid not null references industry_categories(id),
  primary key (id)
);

create index work_experience_categories_work_exp_category_FK on work_experience_contained_categories (work_experience_id, industry_category_id);

create table company_contained_categories (
  id uuid default gen_random_uuid(),
  company_id uuid not null references companies(id),
  industry_category_id uuid not null references industry_categories(id),
  primary key (id)
);

create index company_contained_categories_company_id_categories_id_FK on company_contained_categories (company_id, industry_category_id);


CREATE TABLE "skills" (
  "id" UUID DEFAULT gen_random_uuid(),
  "position_id" UUID REFERENCES "positions"("id"),
  "student_id" UUID REFERENCES "students"("id"),
  "skill_level" SMALLINT check (skill_level >= 0 and skill_level <= 5) NOT NULL,
  check (
    (
      position_id is not null and
      student_id is null
    ) or
    (
      position_id is null and
      student_id is not null
    )
  ),
  PRIMARY KEY ("id")
);

CREATE INDEX "skills_position_id_student_id_FK" ON  "skills" ("position_id", "student_id");

CREATE TABLE "works_withs" (
  "id" UUID DEFAULT gen_random_uuid(),
  "company_id" UUID NOT NULL REFERENCES "companies"("id"),
  "industry_category_id" UUID NOT NULL REFERENCES "industry_categories"("id"),
  PRIMARY KEY ("id")
);

CREATE INDEX "works_withs_company_id_industry_category_id_FK" ON  "works_withs" ("company_id", "industry_category_id");

create table skills_contained_keywords (
  id uuid default gen_random_uuid(),
  skill_id uuid not null references skills(id),
  keyword_id uuid not null references keywords(id),
  primary key(id)
);

create index skills_contained_keywords_skill_id_keyword_id_FK on skills_contained_keywords (skill_id, keyword_id);

create table works_withs_contained_keywords (
  id uuid default gen_random_uuid(),
  works_with_id uuid not null references works_withs(id),
  keyword_id uuid not null references keywords(id),
  primary key(id)
);

create index works_withs_contained_keywords_works_with_id_keyword_id_FK on works_withs_contained_keywords (works_with_id, keyword_id);

create table positions_contained_keywords (
  id uuid default gen_random_uuid(),
  position_id uuid not null references positions(id),
  keyword_id uuid not null references keywords(id),
  primary key(id)
);

create index positions_contained_keywords_position_id_keyword_id_FK on positions_contained_keywords (position_id, keyword_id);


CREATE TABLE "file_types" (
  "id" UUID DEFAULT gen_random_uuid(),
  "type" VARCHAR UNIQUE NOT NULL,
  PRIMARY KEY ("id")
);

CREATE TABLE "providers" (
  "id" UUID DEFAULT gen_random_uuid(),
  "name" VARCHAR NOT NULL,
  PRIMARY KEY ("id")
);

CREATE TABLE "preferred_companies" (
  "id" UUID DEFAULT gen_random_uuid(),
  "student_id" UUID NOT NULL REFERENCES "students"("id"),
  "company_id" UUID NOT NULL REFERENCES "companies"("id"),
  PRIMARY KEY ("id")
);

CREATE INDEX "preferred_companies_student_id_company_id_FK" ON  "preferred_companies" ("student_id", "company_id");

CREATE TABLE "admin_roles" (
  "id" UUID DEFAULT gen_random_uuid(),
  "admin_id" UUID NOT NULL REFERENCES "admins"("id"),
  "role_id" UUID NOT NULL REFERENCES "roles"("id"),
  PRIMARY KEY ("id")
);

CREATE INDEX "admin_roles_admin_id_role_id_FK" ON  "admin_roles" ("admin_id", "role_id");




CREATE TABLE "student_notifications" (
  "id" UUID DEFAULT gen_random_uuid(),
  "notification_id" UUID NOT NULL REFERENCES "notifications"("id"),
  "student_id" UUID NOT NULL REFERENCES "students"("id"),
  "seen" TIMESTAMP,
  PRIMARY KEY ("id")
);

CREATE INDEX "student_notifications_notification_id_student_id_FK" ON  "student_notifications" ("notification_id", "student_id");


CREATE TABLE "files" (
  "id" UUID DEFAULT gen_random_uuid(),
  "path" TEXT NOT NULL,
  "file_type_id" UUID NOT NULL REFERENCES "file_types"("id"),
  "manager_id" UUID REFERENCES "managers"("id"),
  "company_id" UUID REFERENCES "companies"("id"),
  "news_article_id" UUID REFERENCES "news_articles"("id"),
  "admin_id" UUID REFERENCES "admins"("id"),
  "position_id" UUID REFERENCES "positions"("id"),
  "student_id" UUID REFERENCES "students"("id"),
  check(
    (
      manager_id is not null and
      company_id is null and
      news_article_id is null and
      admin_id is null and
      position_id is null and
      student_id is null
    ) or
    (
      manager_id is null and
      company_id is not null and
      news_article_id is null and
      admin_id is null and
      position_id is null and
      student_id is null
    ) or
    (
      manager_id is null and
      company_id is null and
      news_article_id is not null and
      admin_id is null and
      position_id is null and
      student_id is null
    ) or
    (
      manager_id is null and
      company_id is null and
      news_article_id is null and
      admin_id is not null and
      position_id is null and
      student_id is null
    ) or
    (
      manager_id is null and
      company_id is null and
      news_article_id is null and
      admin_id is null and
      position_id is not null and
      student_id is null
    ) or
    (
      manager_id is null and
      company_id is null and
      news_article_id is null and
      admin_id is null and
      position_id is null and
      student_id is not null
    )
  ),
  PRIMARY KEY ("id")
);

CREATE INDEX "files_type_manager_company_article_admin_position_student_FK" ON  "files" ("file_type_id", "manager_id", "company_id", "news_article_id", "admin_id", "position_id", "student_id");

CREATE TABLE "manager_connections" (
  "id" UUID DEFAULT gen_random_uuid(),
  "manager_id" UUID NOT NULL REFERENCES "managers"("id"),
  "student_id" UUID NOT NULL REFERENCES "students"("id"),
  PRIMARY KEY ("id")
);

CREATE INDEX "manager_connections_manager_id_student_id_FK" ON  "manager_connections" ("manager_id", "student_id");

CREATE TABLE "preferred_positions" (
  "id" UUID DEFAULT gen_random_uuid(),
  "student_id" UUID NOT NULL REFERENCES "students"("id"),
  "position_id" UUID NOT NULL REFERENCES "positions"("id"),
  PRIMARY KEY ("id")
);

CREATE INDEX "preferred_positions_student_id_position_id_FK" ON  "preferred_positions" ("student_id", "position_id");


CREATE SCHEMA "private";

CREATE TYPE "private".jwt_token AS (
  "student_id" TEXT,
  "manager_id" TEXT,
  "admin_id" TEXT,
  "company_id" TEXT,
  "exp" INTEGER
);

CREATE TABLE "users" (
  "email" citext,
  "password" TEXT NOT NULL,
  "student_id" UUID REFERENCES "students"("id") ON DELETE CASCADE,
  "manager_id" UUID REFERENCES "managers"("id") ON DELETE CASCADE,
  "admin_id" UUID REFERENCES "admins"("id") ON DELETE CASCADE,
  "company_id" UUID REFERENCES "companies"("id") ON DELETE CASCADE,
  check (
    (
      student_id is not null and
      manager_id is null and
      admin_id is null and
      company_id is null
    ) or
    (
      student_id is null and
      manager_id is not null and
      admin_id is null and
      company_id is null
    ) or
    (
      student_id is null and
      manager_id is null and
      admin_id is not null and
      company_id is null
    ) or
    (
      student_id is null and
      manager_id is null and
      admin_id is null and
      company_id is not null
    )
  ),
  PRIMARY KEY("email")
);

CREATE INDEX "users_students_managers_admins_FK" ON "users" ("student_id", "manager_id", "admin_id");

create table attending_conferences (
  id uuid default gen_random_uuid(),
  company_id uuid references companies(id),
  conference_id uuid references conferences(id),
  "room" TEXT,
  "floor" TEXT,
  primary key (id)
);

create index attending_conferences_company_id_conference_id_FK on attending_conferences (company_id, conference_id);



create or replace function notify_company_and_managers_and_students_of_new_conference() returns trigger as $$
    declare
        v_notification_source_id uuid;
        v_activity_types_id uuid;
        v_notification_id uuid;
        v_company_ids uuid[];
        v_manager_ids uuid[];
        v_student_ids uuid[];
    begin

        select array_agg(id::uuid) into v_company_ids from companies;
        select array_agg(id::uuid) into v_manager_ids from managers;
        select array_agg(id::uuid) into v_student_ids from students;


        select id into v_activity_types_id
        from activity_types
        where activity_types.type = 'new_conference';

        insert into notification_sources(conference_id)
        values (NEW.id)
        returning id into v_notification_source_id;

        insert into notifications(notification_source_id, activity_type_id)
        values (v_notification_source_id, v_activity_types_id)
        returning id into v_notification_id;


        FOR index IN v_company_ids LOOP
            insert into company_notifications(notification_id, manager_id)
            values (v_notification_id, v_company_ids[index]);
        END LOOP;

        FOR index IN v_manager_ids LOOP
            insert into manager_notifications(notification_id, manager_id)
            values (v_notification_id, v_manager_ids[index]);
        END LOOP;

        FOR index IN v_student_ids LOOP
            insert into student_notifications(notification_id, manager_id)
            values (v_notification_id, v_student_ids[index]);
        END LOOP;

        return NEW;

    end;
&& language plpgsql volatile set search_path from current security definer;

create trigger trigger_company_and_managers_and_students_of_new_conference
after insert on conferences
for each row execute procedure notify_company_and_managers_and_students_of_new_conference();




--------------------------------------------------------------
--------------------------------------------------------------

--imports

\i genesis.sql;
\i row_level_sec.sql;
\i helper_functions.sql;
\i custom_functions.sql;
\i policies.sql;
\i table_comments.sql;
\i column_specific_privileges.sql;

-- triggers

--\i notify_company_and_managers_and_students_of_new_conference.sql;
--\i triggers/notify_company_and_managers_of_new_interested_student.sql;
--\i triggers/notify_manager_of_new_company_position.sql;
--\i triggers/notify_manager_of_new_student_connection.sql;
--\i triggers/notify_student_of_new_preferred_company_position.sql;
--\i triggers/notify_company_and_managers_of_upcomming_attending_conference.sql;

--------------------------------------------------------------
--------------------------------------------------------------