CREATE TABLE state
(
    StateCode TEXT
        constraint state_pk
            primary key,
    State     TEXT,
    Region    TEXT
);
state
CREATE TABLE callcenterlogs
(
    "Date received" DATE,
    "Complaint ID"  TEXT,
    "rand client"   TEXT,
    phonefinal      TEXT,
    "vru+line"      TEXT,
    call_id         INTEGER,
    priority        INTEGER,
    type            TEXT,
    outcome         TEXT,
    server          TEXT,
    ser_start       TEXT,
    ser_exit        TEXT,
    ser_time        TEXT,
    primary key ("Complaint ID"),
    foreign key ("rand client") references client(client_id)
);
callcenterlogs
CREATE TABLE client
(
    client_id   TEXT
            primary key,
    sex         TEXT,
    day         INTEGER,
    month       INTEGER,
    year        INTEGER,
    age         INTEGER,
    social      TEXT,
    first       TEXT,
    middle      TEXT,
    last        TEXT,
    phone       TEXT,
    email       TEXT,
    address_1   TEXT,
    address_2   TEXT,
    city        TEXT,
    state       TEXT,
    zipcode     INTEGER,
    district_id INTEGER,
    foreign key (district_id) references district(district_id)
);
client
CREATE TABLE district
(
    district_id  INTEGER
            primary key,
    city         TEXT,
    state_abbrev TEXT,
    division     TEXT,
    foreign key (state_abbrev) references state(StateCode)
);
district
CREATE TABLE events
(
    "Date received"                DATE,
    Product                        TEXT,
    "Sub-product"                  TEXT,
    Issue                          TEXT,
    "Sub-issue"                    TEXT,
    "Consumer complaint narrative" TEXT,
    Tags                           TEXT,
    "Consumer consent provided?"   TEXT,
    "Submitted via"                TEXT,
    "Date sent to company"         TEXT,
    "Company response to consumer" TEXT,
    "Timely response?"             TEXT,
    "Consumer disputed?"           TEXT,
    "Complaint ID"                 TEXT,
    Client_ID                      TEXT,
    primary key ("Complaint ID", Client_ID),
    foreign key ("Complaint ID") references callcenterlogs("Complaint ID"),
    foreign key (Client_ID) references client(client_id)
);
events
CREATE TABLE reviews
(
    "Date"        DATE
            primary key,
    Stars       INTEGER,
    Reviews     TEXT,
    Product     TEXT,
    district_id INTEGER,
    foreign key (district_id) references district(district_id)
);
reviews
original_column_name,column_name,column_description,data_format,value_description
Date,,,date,
Stars,,,integer,
Reviews,,,text,
Product,,,text,
district_id,,,integer,
reviews
original_column_name,column_name,column_description,data_format,value_description
Date received,,complaint date,date,
Complaint ID,,unique id number representing each complaint,text,
rand client,,client id,text,
phonefinal,phone final,final phone number,text,
vru+line,voice response unit line,voice response unit line,text,
call_id,call id,id number identifying the call,integer,
priority,,priority of the complaint,integer,"0, 1, 2, 
null: not available,
higher: -> higher priority,
-> more serious/urgent complaint"
type,,type of complaint,text,
outcome,,the outcome of processing of complaints,text,
server,,server,text,
ser_start,server start,server start time,text,HH:MM:SS
ser_exit,server exit,server exit time,text,
ser_time,server time,server time,text,"commonsense evidence:
longer server time referring to more verbose/longer complaint"
callcenterlogs
original_column_name,column_name,column_description,data_format,value_description
district_id,district id,unique id number representing district,integer,
city,,city ,text,
state_abbrev,,state abbreviated code,text,
division,,division,text,
district
original_column_name,column_name,column_description,data_format,value_description
client_id,client id,unique id client number,text,
sex,,sex of client,text,
day,,day of the birthday,integer,
month,,month of the birthday,integer,
year,,year when is born,integer,
age,,age ,integer,"teenager: 13-19
adult: 19-65
elder: > 65"
social,,social number,text,ssn: us id number for each person
first,,first name,text,
middle,,middle name,text,
last,,last name,text,
phone,,phone number,text,
email,,email,text,"commonsense evidence:
google email / account: @gamil.com
microsoft email / account: xxx@outlook.com"
address_1,,address 1,text,
address_2,,address 2,text,"entire address = (address_1, address_2)"
city,,city ,text,
state,,state code,text,
zipcode,,zipcode,integer,
district_id,district id,district id number,integer,
client
original_column_name,column_name,column_description,data_format,value_description
Date received,,Date received,date,
Product,,complaining about which product ,text,
Sub-product,,sub product if exists,text,"commonsense evidence:
detailed product"
Issue,,problems leading to this complaints,text,
Sub-issue,,sub problems leading to this complaints if exists,text,"commonsense evidence:
more detailed issue"
Consumer complaint narrative,,Consumer complaint narrative,text,
Tags,,tags of client,text,
Consumer consent provided?,Tags Consumer consent provided?,whether the tags labeled under permission of the clients,text,"commonsense evidence:
 null, 'N/A' or empty value: indicating that the company didn't get the permission of consent.
 if the value is not empty: customers provide the consent for this tag."
Submitted via,,Submitted via,text,
Date sent to company,,Date sent to company,text,"commonsense evidence:
delay of the complaints = 'Date sent to company'"
Company response to consumer,,Company response to consumer,text,
Timely response?,,whether the response of the company is timely,text,
Consumer disputed?,,whether the consumer dispute about the response from the company.,text,
Complaint ID,,id number indicating which complaint,text,
Client_ID,Client ID,id number indicating which client,text,
events
original_column_name,column_name,column_description,data_format,value_description
StateCode,,,text,
State,,,text,
Region,,,text,
state