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

-- Table: businesses
CREATE TABLE "bird__food_inspection"."businesses" (
    "business_id" BIGINT NOT NULL,
    "name" TEXT NOT NULL,
    "address" TEXT,
    "city" TEXT,
    "postal_code" TEXT,
    "latitude" DOUBLE PRECISION,
    "longitude" DOUBLE PRECISION,
    "phone_number" BIGINT,
    "tax_code" TEXT,
    "business_certificate" BIGINT NOT NULL,
    "application_date" DATE,
    "owner_name" TEXT NOT NULL,
    "owner_address" TEXT,
    "owner_city" TEXT,
    "owner_state" TEXT,
    "owner_zip" TEXT,
    CONSTRAINT "businesses_pkey" PRIMARY KEY ("business_id")
);

-- Table: inspections
CREATE TABLE "bird__food_inspection"."inspections" (
    "business_id" BIGINT NOT NULL,
    "score" BIGINT,
    "date" DATE NOT NULL,
    "type" TEXT NOT NULL
);

-- Table: violations
CREATE TABLE "bird__food_inspection"."violations" (
    "business_id" BIGINT NOT NULL,
    "date" DATE NOT NULL,
    "violation_type_id" TEXT NOT NULL,
    "risk_category" TEXT NOT NULL,
    "description" TEXT NOT NULL
);

-- Foreign Keys
ALTER TABLE "bird__food_inspection"."inspections" ADD CONSTRAINT "fk_inspections_0" FOREIGN KEY ("business_id") REFERENCES "bird__food_inspection"."businesses" ("business_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "bird__food_inspection"."violations" ADD CONSTRAINT "fk_violations_0" FOREIGN KEY ("business_id") REFERENCES "bird__food_inspection"."businesses" ("business_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
