-- THS-EDW DATABASE: tic_in_network_db_stg

USE tic_in_network_db_stg;

-- PROPOSED UPDATED SCHEDMA FOR PRE-PROCESSING IN-NETWORK MRF
CREATE TABLE negotiated_rates (
  reporting_entity_name varchar(255) NOT NULL,
  billing_code_type varchar(25) NOT NULL,
  billing_code varchar(255) NOT NULL,
  billing_code_type_version varchar(255) NOT NULL,
  name varchar(255) NOT NULL,
  billing_class varchar(25) NOT NULL,
  negotiated_type varchar(25) NOT NULL,
  negotiated_rate float NOT NULL,
  expiration_date date NOT NULL,
  service_code nvarchar(1000),
  provider_group_id float,
  PRIMARY KEY (reporting_entity_name, billing_code_type, billing_code, negotiated_type, negotiated_rate, provider_group_id)
);

CREATE TABLE provider_references_npi (
  reporting_entity_name varchar(255) NOT NULL,
  provider_group_id float NOT NULL,
  provider_groups_npi varchar(255) NOT NULL,
  PRIMARY KEY (reporting_entity_name, provider_group_id, provider_groups_npi)
);

CREATE TABLE provider_references_tin (
  reporting_entity_name varchar(255) NOT NULL,
  provider_group_id float NOT NULL,
  provider_groups_tin_type varchar(100),
  provider_groups_tin_value float NOT NULL,
  PRIMARY KEY (reporting_entity_name, provider_group_id, provider_groups_tin_value)
);

