create database retail_banking;
use retail_banking;
create table dim_completeddistrict(
  district_id int primary key,
  city text,
  state_name text,
  state_abbrev text,
  region text,
  division text
);
create table dim_completedacct(
  account_id text primary key,
  district_id int,
  frequency text,
  parseddate date,
  foreign key(district_id) references dim_completeddistrict(district_id)
);
create table fact_crm_call_center_logs(
  date_received date,
  complaint_id text,
  rand_client text,
  phonefinal text,
  vru_line text, 
  call_id int,
  priority int,
  type text,
  outcome text,
  server text,
  ser_start time,
  ser_exit time,
  ser_time time,
  foreign key(rand_client) references dim_completedclient(client_id)
);
create table dim_completedclient(
  client_id text primary key,
  sex text,
  date_of_birth date,
  age int,
  social text,
  fullname text,
  phone text,
  email text,
  address_1 text,
  addres_2 text,
  city text,
  state text,
  zipcode text,
  district_id int,
  foreign key(district_id) references dim_completeddistrict(district_id)
);
create table dim_completeddisposition(
 disp_id text primary key,
 client_id text,
 account_id text,
 type text,
 foreign key(client_id) references dim_completedclient(client_id),
 foreign key(account_id) references dim_completedacct(account_id)
);
create table dim_completedcard(
  card_id text primary key,
  disp_id text,
  type text,
  fulldate date,
  foreign key(disp_id) references dim_completeddisposition(disp_id)
);
create table fact_completedloan(
  loan_id text primary key,
  account_id text,
  amount double,
  duration int,
  payments double,
  `status` text,
  fulldate date,
  location text,
  purpose text,
  foreign key(account_id) references dim_completedacct(account_id)
);
create table fact_completedtrans(
  trans_id text primary key,
  account_id text,
  type text,
  operation text,
  amount double,
  balance double,
  k_symbol text,
  bank text,
  account text, 
  fulldatewithtime timestamp,
  foreign key(account_id) references dim_completedacct(account_id),
  constraint FK_order foreign key(account) references fact_completedorder(account_to)
);
create table fact_completedorder(
  order_id int primary key,
  account_id text,
  bank_to text,
  account_to text,
  amount double, 
  k_symbol text,
  foreign key(account_id) references dim_completedacct(account_id)
);
create table fact_crm_events(
  date_received date,
  product text,
  sub_product text,
  issue text,
  sub_issue text,
  consumer_complaint_narrative text,
  tags text,
  is_consumer_consent_provided text,
  submitted_via text,
  date_sent_to_company text,
  company_response_to_consumer text,
  is_timely_response text,
  is_consumer_disputed text,
  complaint_id text,
  client_id text,
  constraint FK_crm_call foreign key(complaint_id) references fact_crm_call_center_logs(complaint_id),
  foreign key(client_id) references dim_completedclient(client_id)
);
create table dim_crm_reviews(
  date date,
  `stars` int,
  reviews text,
  product text,
  district_id int,
  foreign key(district_id) references dim_completeddistrict(district_id)
);
create table dim_luxury_loan_portfolio(
  loan_id text primary key,
  funded_amount double,
  funded_date text,
  duration_years int,
  duration_months int,
  ten_yr_treasury_index_date_funded double,
  interest_rate_percent double,
  interest_rate double,
  payments double,
  total_past_payments int,
  loan_balance double,
  property_value double,
  purpose text,
  firstname text,
  middlename text,
  lastname text,
  social text,
  phone text,
  title text,
  employment_length int,
  building_class_category text,
  present_tax_class text,
  present_building_class text,
  address_1 text,
  addres_2 text,
  zip_code int,
  city text,
  state text,
  total_units int,
  land_square_feet text,
  gross_square_feet text,
  tax_class_at_time_of_sale int
);