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

-- Table: institution_details
CREATE TABLE "bird__college_completion"."institution_details" (
    "unitid" BIGINT NOT NULL,
    "chronname" TEXT,
    "city" TEXT,
    "state" TEXT,
    "level" TEXT,
    "control" TEXT,
    "basic" TEXT,
    "hbcu" TEXT,
    "flagship" TEXT,
    "long_x" DOUBLE PRECISION,
    "lat_y" DOUBLE PRECISION,
    "site" TEXT,
    "student_count" BIGINT,
    "awards_per_value" DOUBLE PRECISION,
    "awards_per_state_value" DOUBLE PRECISION,
    "awards_per_natl_value" DOUBLE PRECISION,
    "exp_award_value" BIGINT,
    "exp_award_state_value" BIGINT,
    "exp_award_natl_value" BIGINT,
    "exp_award_percentile" BIGINT,
    "ft_pct" DOUBLE PRECISION,
    "fte_value" BIGINT,
    "fte_percentile" BIGINT,
    "med_sat_value" TEXT,
    "med_sat_percentile" TEXT,
    "aid_value" BIGINT,
    "aid_percentile" BIGINT,
    "endow_value" TEXT,
    "endow_percentile" TEXT,
    "grad_100_value" DOUBLE PRECISION,
    "grad_100_percentile" BIGINT,
    "grad_150_value" DOUBLE PRECISION,
    "grad_150_percentile" BIGINT,
    "pell_value" DOUBLE PRECISION,
    "pell_percentile" BIGINT,
    "retain_value" DOUBLE PRECISION,
    "retain_percentile" BIGINT,
    "ft_fac_value" DOUBLE PRECISION,
    "ft_fac_percentile" BIGINT,
    "vsa_year" TEXT,
    "vsa_grad_after4_first" TEXT,
    "vsa_grad_elsewhere_after4_first" TEXT,
    "vsa_enroll_after4_first" TEXT,
    "vsa_enroll_elsewhere_after4_first" TEXT,
    "vsa_grad_after6_first" TEXT,
    "vsa_grad_elsewhere_after6_first" TEXT,
    "vsa_enroll_after6_first" TEXT,
    "vsa_enroll_elsewhere_after6_first" TEXT,
    "vsa_grad_after4_transfer" TEXT,
    "vsa_grad_elsewhere_after4_transfer" TEXT,
    "vsa_enroll_after4_transfer" TEXT,
    "vsa_enroll_elsewhere_after4_transfer" TEXT,
    "vsa_grad_after6_transfer" TEXT,
    "vsa_grad_elsewhere_after6_transfer" TEXT,
    "vsa_enroll_after6_transfer" TEXT,
    "vsa_enroll_elsewhere_after6_transfer" TEXT,
    "similar" TEXT,
    "state_sector_ct" BIGINT,
    "carnegie_ct" BIGINT,
    "counted_pct" TEXT,
    "nicknames" TEXT,
    "cohort_size" BIGINT,
    CONSTRAINT "institution_details_pk" PRIMARY KEY ("unitid")
);

-- Table: institution_grads
CREATE TABLE "bird__college_completion"."institution_grads" (
    "unitid" BIGINT,
    "year" BIGINT,
    "gender" TEXT,
    "race" TEXT,
    "cohort" TEXT,
    "grad_cohort" TEXT,
    "grad_100" TEXT,
    "grad_150" TEXT,
    "grad_100_rate" TEXT,
    "grad_150_rate" TEXT
);

-- Table: state_sector_details
CREATE TABLE "bird__college_completion"."state_sector_details" (
    "stateid" BIGINT NOT NULL,
    "state" TEXT,
    "state_post" TEXT,
    "level" TEXT NOT NULL,
    "control" TEXT NOT NULL,
    "schools_count" BIGINT,
    "counted_pct" TEXT,
    "awards_per_state_value" TEXT,
    "awards_per_natl_value" DOUBLE PRECISION,
    "exp_award_state_value" TEXT,
    "exp_award_natl_value" BIGINT,
    "state_appr_value" TEXT,
    "state_appr_rank" TEXT,
    "grad_rate_rank" TEXT,
    "awards_per_rank" TEXT,
    CONSTRAINT "state_sector_details_pkey" PRIMARY KEY ("stateid", "level", "control")
);

-- Table: state_sector_grads
CREATE TABLE "bird__college_completion"."state_sector_grads" (
    "stateid" BIGINT,
    "state" TEXT,
    "state_abbr" TEXT,
    "control" TEXT,
    "level" TEXT,
    "year" BIGINT,
    "gender" TEXT,
    "race" TEXT,
    "cohort" TEXT,
    "grad_cohort" TEXT,
    "grad_100" TEXT,
    "grad_150" TEXT,
    "grad_100_rate" TEXT,
    "grad_150_rate" TEXT,
    "grad_cohort_ct" BIGINT
);
