


CREATE TABLE addresses(address_id BIGINT PRIMARY KEY, street_line_1 VARCHAR, street_line_2 VARCHAR, city VARCHAR, state VARCHAR, postal_code VARCHAR, UNIQUE(postal_code, street_line_1));
CREATE TABLE api_metadata(id INTEGER DEFAULT(1) PRIMARY KEY, last_request_time TIMESTAMP, total_entries INTEGER, CHECK((id = 1)));
CREATE TABLE articles(article_id INTEGER PRIMARY KEY, author VARCHAR, title VARCHAR NOT NULL, description VARCHAR, url VARCHAR UNIQUE, source VARCHAR, image_url VARCHAR, category VARCHAR, "language" VARCHAR, country VARCHAR, published_at TIMESTAMP, created_at TIMESTAMP DEFAULT(CURRENT_TIMESTAMP));
CREATE TABLE doctors(doctor_id BIGINT PRIMARY KEY, npi BIGINT NOT NULL UNIQUE, first_name VARCHAR, middle_name VARCHAR, last_name VARCHAR, credential VARCHAR);
CREATE TABLE embeddings(tax_id VARCHAR, Parent_Category VARCHAR, Definition VARCHAR, Specialty VARCHAR, Embedding FLOAT[384]);
CREATE TABLE insurance_plans(id INTEGER PRIMARY KEY, company VARCHAR, plan VARCHAR, plan_type VARCHAR);
CREATE TABLE provider_insurance_plans(id INTEGER PRIMARY KEY, npi BIGINT NOT NULL, insurance_plan_id INTEGER);
CREATE TABLE speciality(tax_id VARCHAR, Parent_Category VARCHAR, Definition VARCHAR, Specialty VARCHAR, Embedding FLOAT[384]);
CREATE TABLE specialties(tax_id VARCHAR PRIMARY KEY, Parent_Category VARCHAR, Definition VARCHAR, Specialty VARCHAR, Embedding FLOAT[384]);
CREATE TABLE top_specialty_by_state(state VARCHAR, top_specialty VARCHAR, specialty_count INTEGER);
CREATE TABLE doctor_addresses(pa_id BIGINT PRIMARY KEY, address_id BIGINT NOT NULL, npi BIGINT NOT NULL, address_type VARCHAR, address_rank INTEGER NOT NULL, FOREIGN KEY (address_id) REFERENCES addresses(address_id), FOREIGN KEY (npi) REFERENCES doctors(npi));
CREATE TABLE doctor_taxonomy(dt_id BIGINT PRIMARY KEY, npi BIGINT, tax_id VARCHAR, FOREIGN KEY (npi) REFERENCES doctors(npi), FOREIGN KEY (tax_id) REFERENCES specialties(tax_id));
CREATE TABLE reviews(review_id BIGINT PRIMARY KEY, npi BIGINT NOT NULL, rating DECIMAL(18,3), "comment" VARCHAR, date TIMESTAMP, reviewer VARCHAR, FOREIGN KEY (npi) REFERENCES doctors(npi));

CREATE VIEW "sample" AS WITH specialty_counts AS (SELECT a.state, s.Specialty, count_star() AS specialty_count FROM doctors AS d INNER JOIN doctor_taxonomy AS dt ON ((d.npi = dt.npi)) INNER JOIN specialties AS s ON ((dt.tax_id = s.tax_id)) INNER JOIN doctor_addresses AS da ON ((d.npi = da.npi)) INNER JOIN addresses AS a ON ((da.address_id = a.address_id)) WHERE (da.address_rank = 1) GROUP BY a.state, s.Specialty), ranked_specialties AS (SELECT state, Specialty, specialty_count, rank() OVER (PARTITION BY state ORDER BY specialty_count DESC) AS specialty_rank FROM specialty_counts)SELECT state, Specialty AS top_specialty, specialty_count FROM ranked_specialties WHERE (specialty_rank = 1);
CREATE VIEW search_view AS WITH search_view_temp AS (SELECT DISTINCT ON (d.doctor_id, PI.npi) d.doctor_id, d.first_name, d.middle_name, d.last_name, d.credential, s.tax_id AS taxonomy, s.Parent_Category, s.Specialty, a.street_line_1, a.city, a.state, a.postal_code, ip.company, ip.plan, ip.plan_type FROM doctors AS d INNER JOIN doctor_taxonomy AS dt ON ((d.npi = dt.npi)) INNER JOIN specialties AS s ON ((dt.tax_id = s.tax_id)) INNER JOIN doctor_addresses AS da ON ((d.npi = da.npi)) INNER JOIN addresses AS a ON ((da.address_id = a.address_id)) INNER JOIN provider_insurance_plans AS PI ON ((d.npi = PI.npi)) INNER JOIN insurance_plans AS ip ON ((PI.insurance_plan_id = ip.id)) WHERE ((s.Specialty = 'Hematology (Internal Medicine) Physician') AND (a.state = 'NY')))SELECT * FROM search_view_temp;

CREATE INDEX insurance_plans_idx_company ON insurance_plans(company);
CREATE INDEX rovider_insurance_plans_idx_code ON provider_insurance_plans(insurance_plan_id);
CREATE INDEX rovider_insurance_plans_idx_npi ON provider_insurance_plans(npi);


