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

-- Table: frpm
CREATE TABLE "bird__california_schools"."frpm" (
    "CDSCode" TEXT NOT NULL,
    "Academic Year" TEXT,
    "County Code" TEXT,
    "District Code" BIGINT,
    "School Code" TEXT,
    "County Name" TEXT,
    "District Name" TEXT,
    "School Name" TEXT,
    "District Type" TEXT,
    "School Type" TEXT,
    "Educational Option Type" TEXT,
    "NSLP Provision Status" TEXT,
    "Charter School (Y/N)" BIGINT,
    "Charter School Number" TEXT,
    "Charter Funding Type" TEXT,
    "IRC" BIGINT,
    "Low Grade" TEXT,
    "High Grade" TEXT,
    "Enrollment (K-12)" DOUBLE PRECISION,
    "Free Meal Count (K-12)" DOUBLE PRECISION,
    "Percent (%) Eligible Free (K-12)" DOUBLE PRECISION,
    "FRPM Count (K-12)" DOUBLE PRECISION,
    "Percent (%) Eligible FRPM (K-12)" DOUBLE PRECISION,
    "Enrollment (Ages 5-17)" DOUBLE PRECISION,
    "Free Meal Count (Ages 5-17)" DOUBLE PRECISION,
    "Percent (%) Eligible Free (Ages 5-17)" DOUBLE PRECISION,
    "FRPM Count (Ages 5-17)" DOUBLE PRECISION,
    "Percent (%) Eligible FRPM (Ages 5-17)" DOUBLE PRECISION,
    "2013-14 CALPADS Fall 1 Certification Status" BIGINT,
    CONSTRAINT "frpm_pkey" PRIMARY KEY ("CDSCode")
);

-- Table: satscores
CREATE TABLE "bird__california_schools"."satscores" (
    "cds" TEXT NOT NULL,
    "rtype" TEXT NOT NULL,
    "sname" TEXT,
    "dname" TEXT,
    "cname" TEXT,
    "enroll12" BIGINT NOT NULL,
    "NumTstTakr" BIGINT NOT NULL,
    "AvgScrRead" BIGINT,
    "AvgScrMath" BIGINT,
    "AvgScrWrite" BIGINT,
    "NumGE1500" BIGINT,
    CONSTRAINT "satscores_pkey" PRIMARY KEY ("cds")
);

-- Table: schools
CREATE TABLE "bird__california_schools"."schools" (
    "CDSCode" TEXT NOT NULL,
    "NCESDist" TEXT,
    "NCESSchool" TEXT,
    "StatusType" TEXT NOT NULL,
    "County" TEXT NOT NULL,
    "District" TEXT NOT NULL,
    "School" TEXT,
    "Street" TEXT,
    "StreetAbr" TEXT,
    "City" TEXT,
    "Zip" TEXT,
    "State" TEXT,
    "MailStreet" TEXT,
    "MailStrAbr" TEXT,
    "MailCity" TEXT,
    "MailZip" TEXT,
    "MailState" TEXT,
    "Phone" TEXT,
    "Ext" TEXT,
    "Website" TEXT,
    "OpenDate" DATE,
    "ClosedDate" DATE,
    "Charter" BIGINT,
    "CharterNum" TEXT,
    "FundingType" TEXT,
    "DOC" TEXT NOT NULL,
    "DOCType" TEXT NOT NULL,
    "SOC" TEXT,
    "SOCType" TEXT,
    "EdOpsCode" TEXT,
    "EdOpsName" TEXT,
    "EILCode" TEXT,
    "EILName" TEXT,
    "GSoffered" TEXT,
    "GSserved" TEXT,
    "Virtual" TEXT,
    "Magnet" BIGINT,
    "Latitude" DOUBLE PRECISION,
    "Longitude" DOUBLE PRECISION,
    "AdmFName1" TEXT,
    "AdmLName1" TEXT,
    "AdmEmail1" TEXT,
    "AdmFName2" TEXT,
    "AdmLName2" TEXT,
    "AdmEmail2" TEXT,
    "AdmFName3" TEXT,
    "AdmLName3" TEXT,
    "AdmEmail3" TEXT,
    "LastUpdate" DATE NOT NULL,
    CONSTRAINT "schools_pkey" PRIMARY KEY ("CDSCode")
);

-- Foreign Keys
ALTER TABLE "bird__california_schools"."frpm" ADD CONSTRAINT "fk_frpm_0" FOREIGN KEY ("CDSCode") REFERENCES "bird__california_schools"."schools" ("CDSCode") ON DELETE NO ACTION ON UPDATE NO ACTION;
