CREATE TABLE sessions (
  id varchar(128) PRIMARY KEY NOT NULL,
  ip_address varchar(45) NOT NULL,
  s3_session varchar(255) NOT NULL,
  created_by int(11) NOT NULL,
  created_at datetime NOT NULL
);

CREATE TABLE users (
  id int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
  fullname varchar(255) NOT NULL,
  username varchar(255) NOT NULL,
  cognito_uuid varchar(255) NOT NULL,
  s3_session varchar(255) NOT NULL,
  role_id int(11) NOT NULL,
  company_id int(11) NOT NULL,
  active int(2) NOT NULL,
  created_by int(11) NOT NULL,
  created_at datetime NOT NULL,
  updated_by int(11) NOT NULL,
  updated_at datetime NOT NULL
);

CREATE TABLE Company (
  id int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
  name varchar(255) NOT NULL,
  type int(11) NOT NULL,
  xero_token varchar(255) NOT NULL,
  bank_accounts varchar(255) NOT NULL,
  active int(2) NOT NULL,
  created_by int(11) NOT NULL,
  created_at datetime NOT NULL,
  updated_by int(11) NOT NULL,
  updated_at datetime NOT NULL
);

CREATE TABLE master_table (
  id int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
  date datetime NOT NULL,
  action varchar(255) NOT NULL,
  company varchar(255) NOT NULL,
  invoice_number varchar(255) NOT NULL,
  description varchar(255) NOT NULL,
  qty varchar(255) NOT NULL,
  amount varchar(255) NOT NULL,
  invoice_date varchar(255) NOT NULL,
  invoice_details varchar(255) NOT NULL,
  invoice_description varchar(255) NOT NULL,
  invoice_currentcy varchar(255) NOT NULL,
  invoice_amount varchar(255) NOT NULL,
  receipt_date varchar(255) NOT NULL,
  receipt_details varchar(255) NOT NULL,
  receipt_description varchar(255) NOT NULL,
  receipt_currentcy varchar(255) NOT NULL,
  receipt_amount varchar(255) NOT NULL,
  bank_date varchar(255) NOT NULL,
  bank_details varchar(255) NOT NULL,
  bank_description varchar(255) NOT NULL,
  bank_currentcy varchar(255) NOT NULL,
  bank_amount varchar(255) NOT NULL,
  type int(11) NOT NULL,
  active int(2) NOT NULL,
  created_by int(11) NOT NULL,
  created_at datetime NOT NULL,
  updated_by int(11) NOT NULL,
  updated_at datetime NOT NULL
);

CREATE TABLE user_role (
  id int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
  role_name varchar(255) NOT NULL,
  active int(2) NOT NULL,
  created_by int(11) NOT NULL,
  created_at datetime NOT NULL,
  updated_by int(11) NOT NULL,
  updated_at datetime NOT NULL
);

CREATE TABLE settings (
  id int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
  setting_name varchar(255) NOT NULL,
  value varchar(255) NOT NULL,
  created_by int(11) NOT NULL,
  created_at datetime NOT NULL,
  updated_by int(11) NOT NULL,
  updated_at datetime NOT NULL
);

ALTER TABLE settings ADD FOREIGN KEY (updated_by) REFERENCES users (id);

ALTER TABLE Company ADD FOREIGN KEY (created_by) REFERENCES users (id);

ALTER TABLE Company ADD FOREIGN KEY (updated_by) REFERENCES users (id);

ALTER TABLE user_role ADD FOREIGN KEY (created_by) REFERENCES users (id);

ALTER TABLE user_role ADD FOREIGN KEY (updated_by) REFERENCES users (id);

ALTER TABLE sessions ADD FOREIGN KEY (created_by) REFERENCES users (id);

ALTER TABLE users ADD FOREIGN KEY (company_id) REFERENCES Company (id);

ALTER TABLE users ADD FOREIGN KEY (id) REFERENCES users (created_by);

ALTER TABLE users ADD FOREIGN KEY (updated_by) REFERENCES users (id);

ALTER TABLE users ADD FOREIGN KEY (role_id) REFERENCES user_role (id);

ALTER TABLE settings ADD FOREIGN KEY (created_by) REFERENCES users (id);
