schema.sql

-- In this SQL file, write (and comment!) the schema of your database, including the CREATE TABLE, CREATE INDEX, CREATE VIEW, etc. statements that compose it

-- TABLEs
-- -- to represent students taking the course
CREATE TABLE IF NOT EXISTS "students" (
    "id" INTEGER PRIMARY KEY,
    "student_number" TEXT NOT NULL UNIQUE
        CHECK(length("student_number") = 9 AND "student_number" GLOB '[0-9]*'),
    "first_name" TEXT NOT NULL,
    "last_name" TEXT NOT NULL,
    "email" TEXT NOT NULL UNIQUE
);
-- -- to represent companies the students intern for
CREATE TABLE IF NOT EXISTS "companies" (
    "id" INTEGER PRIMARY KEY,
    "name" TEXT NOT NULL UNIQUE,
    "industry" TEXT NOT NULL CHECK("industry" IN ('film and tv', 'radio', 'journalism', 'animation')),
    "city" TEXT NOT NULL,
    "province" TEXT NOT NULL CHECK ("province" IN (
        'AB','BC','MB','NB','NL','NS','NT','NU','ON','PE','QC','SK','YT'
        )
    ),
    "blacklisted" NUMERIC DEFAULT NULL
);
-- -- to represent supervisors of students, each who works for a given company
CREATE TABLE IF NOT EXISTS "supervisors" (
    "id" INTEGER PRIMARY KEY,
    "first_name" TEXT NOT NULL,
    "last_name" TEXT NOT NULL,
    "company_id" INTEGER NOT NULL,
    "position" TEXT NOT NULL,
    "phone_number" TEXT NOT NULL UNIQUE,
    "email" TEXT NOT NULL UNIQUE,

    FOREIGN KEY("company_id") REFERENCES "companies"("id")
);
-- -- to represent the internship relationship between student and supervisor
CREATE TABLE IF NOT EXISTS "internships" (
    "id" INTEGER PRIMARY KEY,
    "student_id" INTEGER NOT NULL,
    "supervisor_id" INTEGER NOT NULL,
    "student_position" TEXT NOT NULL,
    "work_type" TEXT CHECK("work_type" IN ('in person', 'remote', 'hybrid')),
    "start_date" NUMERIC NOT NULL DEFAULT CURRENT_DATE,
    "end_date" NUMERIC,

    CHECK("end_date" IS NULL OR "end_date" >= "start_date"),
    FOREIGN KEY("student_id") REFERENCES "students"("id"),
    FOREIGN KEY("supervisor_id") REFERENCES "supervisors"("id")
);
-- -- to represent how supervisors evaluated students
CREATE TABLE IF NOT EXISTS "supervisor_feedback" (
    "internship_id" INTEGER PRIMARY KEY,
    "initiative_score" INTEGER NOT NULL CHECK("initiative_score" BETWEEN 0 AND 5),
    "quality_of_work_score" INTEGER NOT NULL CHECK("quality_of_work_score" BETWEEN 0 AND 5),
    "productivity_score" INTEGER NOT NULL CHECK("productivity_score" BETWEEN 0 AND 5),
    "reliability_score" INTEGER NOT NULL CHECK("reliability_score" BETWEEN 0 AND 5),
    "comment" TEXT,

    FOREIGN KEY("internship_id") REFERENCES "internships"("id")
);
-- -- to represent how students evaluated internships experiences
CREATE TABLE IF NOT EXISTS "student_feedback" (
    "internship_id" INTEGER PRIMARY KEY,
    "internship_score" INTEGER NOT NULL CHECK("internship_score" BETWEEN 0 AND 5),
    "comment" TEXT,

    FOREIGN KEY("internship_id") REFERENCES "internships"("id")
);


-- INDEXes
-- -- of blacklisted companies
CREATE INDEX IF NOT EXISTS "idx_blacklist"
ON "companies" ("blacklisted")
WHERE "blacklisted" IS NOT NULL;
-- -- of student id
CREATE INDEX IF NOT EXISTS "idx_student_id"
ON "internships" ("student_id");
-- -- of supervisor id
CREATE INDEX IF NOT EXISTS "idx_supervisor_id"
ON "internships" ("supervisor_id");
-- of supervisor email
CREATE INDEX IF NOT EXISTS "idx_supervisor_email"
ON "supervisors" ("email");
-- -- of internship id
CREATE INDEX IF NOT EXISTS "idx_internship_id_supervisor_feedback"
ON "supervisor_feedback" ("internship_id");
-- -- of work types
CREATE INDEX IF NOT EXISTS "idx_work_type"
ON "internships" ("work_type");
-- -- of start dates
CREATE INDEX IF NOT EXISTS "idx_start_date"
ON "internships" ("start_date")
WHERE "start_date" >= '2025-01-01';


-- VIEWs
-- -- of whitelisted companies
CREATE VIEW IF NOT EXISTS "companies_whitelist" AS
    SELECT "id", "name", "industry", "city", "province" FROM "companies"
    WHERE "blacklisted" IS NULL;
-- -- of blacklisted companies
CREATE VIEW IF NOT EXISTS "companies_blacklist" AS
    SELECT "id", "name", "industry", "city", "province" FROM "companies"
    WHERE "blacklisted" IS NOT NULL;
-- -- of whitelisted companies which have hired the most
CREATE VIEW IF NOT EXISTS "companies_by_hire_amount" AS
    SELECT "c"."id", "c"."name", COUNT("i"."student_id") AS "intern_count"
    FROM "companies_whitelist" AS "c"
    JOIN "supervisors" AS "s" ON "c"."id" = "s"."company_id"
    JOIN "internships" AS "i" ON "s"."id" = "i"."supervisor_id"
    GROUP BY "c"."id"
    ORDER BY "intern_count" DESC, "c"."name";
-- -- of highest rated whitelisted companies
CREATE VIEW IF NOT EXISTS "companies_by_score" AS
    SELECT "c"."id", "c"."name", ROUND(AVG("sf"."internship_score"), 2) AS "average_score"
    FROM "companies_whitelist" AS "c"
    JOIN "supervisors" AS "s" ON "c"."id" = "s"."company_id"
    JOIN "internships" AS "i" ON "s"."id" = "i"."supervisor_id"
    JOIN "student_feedback" AS "sf" ON "i"."id" = "sf"."internship_id"
    GROUP BY "c"."id"
    ORDER BY "average_score" DESC, "c"."name";


-- TRIGGERs
-- -- to blacklist a company over deleting it
CREATE TRIGGER IF NOT EXISTS "blacklist_company" BEFORE DELETE ON "companies"
FOR EACH ROW
BEGIN
    UPDATE "companies" SET "blacklisted" = CURRENT_DATE
    WHERE "id" = OLD."id" AND "blacklisted" IS NULL;
    SELECT RAISE(IGNORE);
END;