-- THS-EDW DATABASE PROJECT MMA001

-- USE master;
-- CREATE DATABASE tic_mma001;

USE tic_mma001
GO

-- Create STG tables for project MRF data migration

CREATE TABLE mma_negotiated_rates_stg (
  negotiated_rates_id int IDENTITY(1,1) PRIMARY KEY,
  reporting_entity_name varchar(255) NOT NULL,
  billing_code_type varchar(25) NOT NULL,
  billing_code varchar(25) NOT NULL,
  billing_code_type_version int NOT NULL,
  description nvarchar(MAX) NOT NULL,
  name varchar(255) NOT NULL,
  negotiation_arrangement varchar(25) NOT NULL,
  provider_group_ids nvarchar(MAX) NOT NULL,
  negotiated_rate float NOT NULL,
  negotiated_type varchar(50) NOT NULL,
  billing_class varchar(25) NOT NULL,
  billing_code_modifier nvarchar(MAX) NOT NULL,
  service_codes nvarchar(MAX) NOT NULL, 
  expiration_date date NOT NULL,
  additional_information varchar(255)
);

CREATE TABLE dim_provider_references_stg (
  provider_reference_key int IDENTITY(1,1) PRIMARY KEY,
  provider_group_id float NOT NULL,
  provider_type varchar(100),
  provider_type_value varchar(100),
  provider_npi int
);