-- Schema: bird__food_inspection_2
CREATE SCHEMA IF NOT EXISTS "bird__food_inspection_2";

-- Table: employee
CREATE TABLE "bird__food_inspection_2"."employee" (
    "employee_id" BIGINT NOT NULL,
    "first_name" TEXT,
    "last_name" TEXT,
    "address" TEXT,
    "city" TEXT,
    "state" TEXT,
    "zip" BIGINT,
    "phone" TEXT,
    "title" TEXT,
    "salary" BIGINT,
    "supervisor" BIGINT,
    CONSTRAINT "employee_pkey" PRIMARY KEY ("employee_id")
);

-- Table: establishment
CREATE TABLE "bird__food_inspection_2"."establishment" (
    "license_no" BIGINT NOT NULL,
    "dba_name" TEXT,
    "aka_name" TEXT,
    "facility_type" TEXT,
    "risk_level" BIGINT,
    "address" TEXT,
    "city" TEXT,
    "state" TEXT,
    "zip" BIGINT,
    "latitude" DOUBLE PRECISION,
    "longitude" DOUBLE PRECISION,
    "ward" BIGINT,
    CONSTRAINT "establishment_pkey" PRIMARY KEY ("license_no")
);

-- Table: inspection
CREATE TABLE "bird__food_inspection_2"."inspection" (
    "inspection_id" BIGINT NOT NULL,
    "inspection_date" DATE,
    "inspection_type" TEXT,
    "results" TEXT,
    "employee_id" BIGINT,
    "license_no" BIGINT,
    "followup_to" BIGINT,
    CONSTRAINT "inspection_pkey" PRIMARY KEY ("inspection_id")
);

-- Table: inspection_point
CREATE TABLE "bird__food_inspection_2"."inspection_point" (
    "point_id" BIGINT NOT NULL,
    "Description" TEXT,
    "category" TEXT,
    "code" TEXT,
    "fine" BIGINT,
    "point_level" TEXT,
    CONSTRAINT "inspection_point_pkey" PRIMARY KEY ("point_id")
);

-- Table: violation
CREATE TABLE "bird__food_inspection_2"."violation" (
    "inspection_id" BIGINT NOT NULL,
    "point_id" BIGINT NOT NULL,
    "fine" BIGINT,
    "inspector_comment" TEXT,
    CONSTRAINT "violation_pkey" PRIMARY KEY ("inspection_id", "point_id")
);

-- Foreign Keys
ALTER TABLE "bird__food_inspection_2"."inspection" ADD CONSTRAINT "fk_inspection_0" FOREIGN KEY ("followup_to") REFERENCES "bird__food_inspection_2"."inspection" ("inspection_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "bird__food_inspection_2"."violation" ADD CONSTRAINT "fk_violation_0" FOREIGN KEY ("point_id") REFERENCES "bird__food_inspection_2"."inspection_point" ("point_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "bird__food_inspection_2"."violation" ADD CONSTRAINT "fk_violation_1" FOREIGN KEY ("inspection_id") REFERENCES "bird__food_inspection_2"."inspection" ("inspection_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
