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

-- Table: donations
CREATE TABLE "bird__donor"."donations" (
    "donationid" TEXT NOT NULL,
    "projectid" TEXT,
    "donor_acctid" TEXT,
    "donor_city" TEXT,
    "donor_state" TEXT,
    "donor_zip" TEXT,
    "is_teacher_acct" TEXT,
    "donation_timestamp" TIMESTAMP,
    "donation_to_project" DOUBLE PRECISION,
    "donation_optional_support" DOUBLE PRECISION,
    "donation_total" DOUBLE PRECISION,
    "dollar_amount" TEXT,
    "donation_included_optional_support" TEXT,
    "payment_method" TEXT,
    "payment_included_acct_credit" TEXT,
    "payment_included_campaign_gift_card" TEXT,
    "payment_included_web_purchased_gift_card" TEXT,
    "payment_was_promo_matched" TEXT,
    "via_giving_page" TEXT,
    "for_honoree" TEXT,
    "donation_message" TEXT,
    CONSTRAINT "donations_pkey" PRIMARY KEY ("donationid")
);

-- Table: essays
CREATE TABLE "bird__donor"."essays" (
    "projectid" TEXT,
    "teacher_acctid" TEXT,
    "title" TEXT,
    "short_description" TEXT,
    "need_statement" TEXT,
    "essay" TEXT
);

-- Table: projects
CREATE TABLE "bird__donor"."projects" (
    "projectid" TEXT NOT NULL,
    "teacher_acctid" TEXT,
    "schoolid" TEXT,
    "school_ncesid" TEXT,
    "school_latitude" DOUBLE PRECISION,
    "school_longitude" DOUBLE PRECISION,
    "school_city" TEXT,
    "school_state" TEXT,
    "school_zip" BIGINT,
    "school_metro" TEXT,
    "school_district" TEXT,
    "school_county" TEXT,
    "school_charter" TEXT,
    "school_magnet" TEXT,
    "school_year_round" TEXT,
    "school_nlns" TEXT,
    "school_kipp" TEXT,
    "school_charter_ready_promise" TEXT,
    "teacher_prefix" TEXT,
    "teacher_teach_for_america" TEXT,
    "teacher_ny_teaching_fellow" TEXT,
    "primary_focus_subject" TEXT,
    "primary_focus_area" TEXT,
    "secondary_focus_subject" TEXT,
    "secondary_focus_area" TEXT,
    "resource_type" TEXT,
    "poverty_level" TEXT,
    "grade_level" TEXT,
    "fulfillment_labor_materials" DOUBLE PRECISION,
    "total_price_excluding_optional_support" DOUBLE PRECISION,
    "total_price_including_optional_support" DOUBLE PRECISION,
    "students_reached" BIGINT,
    "eligible_double_your_impact_match" TEXT,
    "eligible_almost_home_match" TEXT,
    "date_posted" DATE,
    CONSTRAINT "projects_pkey" PRIMARY KEY ("projectid")
);

-- Table: resources
CREATE TABLE "bird__donor"."resources" (
    "resourceid" TEXT NOT NULL,
    "projectid" TEXT,
    "vendorid" BIGINT,
    "vendor_name" TEXT,
    "project_resource_type" TEXT,
    "item_name" TEXT,
    "item_number" TEXT,
    "item_unit_price" DOUBLE PRECISION,
    "item_quantity" BIGINT,
    CONSTRAINT "resources_pkey" PRIMARY KEY ("resourceid")
);

-- Foreign Keys
ALTER TABLE "bird__donor"."donations" ADD CONSTRAINT "fk_donations_0" FOREIGN KEY ("projectid") REFERENCES "bird__donor"."projects" ("projectid") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "bird__donor"."resources" ADD CONSTRAINT "fk_resources_0" FOREIGN KEY ("projectid") REFERENCES "bird__donor"."projects" ("projectid") ON DELETE NO ACTION ON UPDATE NO ACTION;
