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

-- Table: CBSA
CREATE TABLE "bird__address"."CBSA" (
    "CBSA" BIGINT NOT NULL,
    "CBSA_name" TEXT,
    "CBSA_type" TEXT,
    CONSTRAINT "CBSA_pkey" PRIMARY KEY ("CBSA")
);

-- Table: alias
CREATE TABLE "bird__address"."alias" (
    "zip_code" BIGINT NOT NULL,
    "alias" TEXT,
    CONSTRAINT "alias_pkey" PRIMARY KEY ("zip_code")
);

-- Table: area_code
CREATE TABLE "bird__address"."area_code" (
    "zip_code" BIGINT NOT NULL,
    "area_code" BIGINT NOT NULL,
    CONSTRAINT "area_code_pkey" PRIMARY KEY ("zip_code", "area_code")
);

-- Table: avoid
CREATE TABLE "bird__address"."avoid" (
    "zip_code" BIGINT NOT NULL,
    "bad_alias" TEXT NOT NULL,
    CONSTRAINT "avoid_pkey" PRIMARY KEY ("zip_code", "bad_alias")
);

-- Table: congress
CREATE TABLE "bird__address"."congress" (
    "cognress_rep_id" TEXT NOT NULL,
    "first_name" TEXT,
    "last_name" TEXT,
    "CID" TEXT,
    "party" TEXT,
    "state" TEXT,
    "abbreviation" TEXT,
    "House" TEXT,
    "District" BIGINT,
    "land_area" DOUBLE PRECISION,
    CONSTRAINT "congress_pkey" PRIMARY KEY ("cognress_rep_id")
);

-- Table: country
CREATE TABLE "bird__address"."country" (
    "zip_code" BIGINT NOT NULL,
    "county" TEXT NOT NULL,
    "state" TEXT,
    CONSTRAINT "country_pkey" PRIMARY KEY ("zip_code", "county")
);

-- Table: state
CREATE TABLE "bird__address"."state" (
    "abbreviation" TEXT NOT NULL,
    "name" TEXT,
    CONSTRAINT "state_pkey" PRIMARY KEY ("abbreviation")
);

-- Table: zip_congress
CREATE TABLE "bird__address"."zip_congress" (
    "zip_code" BIGINT NOT NULL,
    "district" TEXT NOT NULL,
    CONSTRAINT "zip_congress_pkey" PRIMARY KEY ("zip_code", "district")
);

-- Table: zip_data
CREATE TABLE "bird__address"."zip_data" (
    "zip_code" BIGINT NOT NULL,
    "city" TEXT,
    "state" TEXT,
    "multi_county" TEXT,
    "type" TEXT,
    "organization" TEXT,
    "time_zone" TEXT,
    "daylight_savings" TEXT,
    "latitude" DOUBLE PRECISION,
    "longitude" DOUBLE PRECISION,
    "elevation" BIGINT,
    "state_fips" BIGINT,
    "county_fips" BIGINT,
    "region" TEXT,
    "division" TEXT,
    "population_2020" BIGINT,
    "population_2010" BIGINT,
    "households" BIGINT,
    "avg_house_value" BIGINT,
    "avg_income_per_household" BIGINT,
    "persons_per_household" DOUBLE PRECISION,
    "white_population" BIGINT,
    "black_population" BIGINT,
    "hispanic_population" BIGINT,
    "asian_population" BIGINT,
    "american_indian_population" BIGINT,
    "hawaiian_population" BIGINT,
    "other_population" BIGINT,
    "male_population" BIGINT,
    "female_population" BIGINT,
    "median_age" DOUBLE PRECISION,
    "male_median_age" DOUBLE PRECISION,
    "female_median_age" DOUBLE PRECISION,
    "residential_mailboxes" BIGINT,
    "business_mailboxes" BIGINT,
    "total_delivery_receptacles" BIGINT,
    "businesses" BIGINT,
    "1st_quarter_payroll" BIGINT,
    "annual_payroll" BIGINT,
    "employees" BIGINT,
    "water_area" DOUBLE PRECISION,
    "land_area" DOUBLE PRECISION,
    "single_family_delivery_units" BIGINT,
    "multi_family_delivery_units" BIGINT,
    "total_beneficiaries" BIGINT,
    "retired_workers" BIGINT,
    "disabled_workers" BIGINT,
    "parents_and_widowed" BIGINT,
    "spouses" BIGINT,
    "children" BIGINT,
    "over_65" BIGINT,
    "monthly_benefits_all" BIGINT,
    "monthly_benefits_retired_workers" BIGINT,
    "monthly_benefits_widowed" BIGINT,
    "CBSA" BIGINT,
    CONSTRAINT "zip_data_pkey" PRIMARY KEY ("zip_code")
);

-- Foreign Keys
ALTER TABLE "bird__address"."congress" ADD CONSTRAINT "fk_congress_0" FOREIGN KEY ("abbreviation") REFERENCES "bird__address"."state" ("abbreviation") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "bird__address"."country" ADD CONSTRAINT "fk_country_0" FOREIGN KEY ("state") REFERENCES "bird__address"."state" ("abbreviation") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "bird__address"."zip_congress" ADD CONSTRAINT "fk_zip_congress_0" FOREIGN KEY ("zip_code") REFERENCES "bird__address"."zip_data" ("zip_code") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "bird__address"."zip_congress" ADD CONSTRAINT "fk_zip_congress_1" FOREIGN KEY ("district") REFERENCES "bird__address"."congress" ("cognress_rep_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "bird__address"."zip_data" ADD CONSTRAINT "fk_zip_data_0" FOREIGN KEY ("CBSA") REFERENCES "bird__address"."CBSA" ("CBSA") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "bird__address"."zip_data" ADD CONSTRAINT "fk_zip_data_1" FOREIGN KEY ("state") REFERENCES "bird__address"."state" ("abbreviation") ON DELETE NO ACTION ON UPDATE NO ACTION;
