# frozen_string_literal: true

require "innodb/record_describer"

# CREATE TABLE employees (
#     emp_no      INT             NOT NULL,
#     birth_date  DATE            NOT NULL,
#     first_name  VARCHAR(14)     NOT NULL,
#     last_name   VARCHAR(16)     NOT NULL,
#     gender      ENUM ('M','F')  NOT NULL,
#     hire_date   DATE            NOT NULL,
#     PRIMARY KEY (emp_no)
# );

class Employees_employees_PRIMARY < Innodb::RecordDescriber
  type :clustered
  key "emp_no",         :INT, :NOT_NULL
  row "birth_date",     :MEDIUMINT, :NOT_NULL
  row "first_name",     "VARCHAR(14)", :NOT_NULL
  row "last_name",      "VARCHAR(16)", :NOT_NULL
  row "gender",         :TINYINT, :UNSIGNED, :NOT_NULL
  row "hire_date",      :MEDIUMINT, :NOT_NULL
end

# CREATE TABLE departments (
#     dept_no     CHAR(4)         NOT NULL,
#     dept_name   VARCHAR(40)     NOT NULL,
#     PRIMARY KEY (dept_no),
#     UNIQUE  KEY (dept_name)
# );

class Employees_departments_PRIMARY < Innodb::RecordDescriber
  type :clustered
  key "dept_no",        "CHAR(4)", :NOT_NULL
  row "dept_name",      "VARCHAR(40)", :NOT_NULL
end

class Employees_departments_dept_name < Innodb::RecordDescriber
  type :secondary
  key "dept_name",      "VARCHAR(40)", :NOT_NULL
  row "dept_no",        "CHAR(4)", :NOT_NULL
end

# CREATE TABLE dept_manager (
#    dept_no      CHAR(4)         NOT NULL,
#    emp_no       INT             NOT NULL,
#    from_date    DATE            NOT NULL,
#    to_date      DATE            NOT NULL,
#    KEY         (emp_no),
#    KEY         (dept_no),
#    FOREIGN KEY (emp_no)  REFERENCES employees (emp_no)    ON DELETE CASCADE,
#    FOREIGN KEY (dept_no) REFERENCES departments (dept_no) ON DELETE CASCADE,
#    PRIMARY KEY (emp_no,dept_no)
# );

class Employees_dept_manager_PRIMARY < Innodb::RecordDescriber
  type :clustered
  key "emp_no",         :INT, :NOT_NULL
  key "dept_no",        "CHAR(4)", :NOT_NULL
  row "from_date",      :MEDIUMINT, :NOT_NULL
  row "to_date",        :MEDIUMINT, :NOT_NULL
end

class Employees_dept_manager_emp_no < Innodb::RecordDescriber
  type :secondary
  key "emp_no",         :INT, :NOT_NULL
  row "dept_no",        "CHAR(4)", :NOT_NULL
end

class Employees_dept_manager_dept_no < Innodb::RecordDescriber
  type :secondary
  key "dept_no",        "CHAR(4)", :NOT_NULL
  row "emp_no",         :INT, :NOT_NULL
end

# CREATE TABLE dept_emp (
#     emp_no      INT             NOT NULL,
#     dept_no     CHAR(4)         NOT NULL,
#     from_date   DATE            NOT NULL,
#     to_date     DATE            NOT NULL,
#     KEY         (emp_no),
#     KEY         (dept_no),
#     FOREIGN KEY (emp_no)  REFERENCES employees   (emp_no)  ON DELETE CASCADE,
#     FOREIGN KEY (dept_no) REFERENCES departments (dept_no) ON DELETE CASCADE,
#     PRIMARY KEY (emp_no,dept_no)
# );

class Employees_dept_emp_PRIMARY < Innodb::RecordDescriber
  type :clustered
  key "emp_no",         :INT, :NOT_NULL
  key "dept_no",        "CHAR(4)", :NOT_NULL
  row "from_date",      :MEDIUMINT, :NOT_NULL
  row "to_date",        :MEDIUMINT, :NOT_NULL
end

class Employees_dept_emp_emp_no < Innodb::RecordDescriber
  type :secondary
  key "emp_no",         :INT, :NOT_NULL
  row "dept_no",        "CHAR(4)", :NOT_NULL
end

class Employees_dept_emp_dept_no < Innodb::RecordDescriber
  type :secondary
  key "dept_no",        "CHAR(4)", :NOT_NULL
  row "emp_no",         :INT, :NOT_NULL
end

# CREATE TABLE titles (
#     emp_no      INT             NOT NULL,
#     title       VARCHAR(50)     NOT NULL,
#     from_date   DATE            NOT NULL,
#     to_date     DATE,
#     KEY         (emp_no),
#     FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,
#     PRIMARY KEY (emp_no,title, from_date)
# );

class Employees_titles_PRIMARY < Innodb::RecordDescriber
  type :clustered
  key "emp_no",         :INT, :NOT_NULL
  key "title",          "VARCHAR(50)", :NOT_NULL
  key "from_date",      :MEDIUMINT, :NOT_NULL
  row "to_date",        :MEDIUMINT, :NOT_NULL
end

class Employees_titles_emp_no < Innodb::RecordDescriber
  type :secondary
  key "emp_no",         :INT, :NOT_NULL
  row "title",          "VARCHAR(50)", :NOT_NULL
  row "from_date",      :MEDIUMINT, :NOT_NULL
end

# CREATE TABLE salaries (
#     emp_no      INT             NOT NULL,
#     salary      INT             NOT NULL,
#     from_date   DATE            NOT NULL,
#     to_date     DATE            NOT NULL,
#     KEY         (emp_no),
#     FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,
#     PRIMARY KEY (emp_no, from_date)
# );

class Employees_salaries_PRIMARY < Innodb::RecordDescriber
  type :clustered
  key "emp_no",         :INT, :NOT_NULL
  key "from_date",      :MEDIUMINT, :NOT_NULL
  row "salary",         :INT, :NOT_NULL
  row "to_date",        :MEDIUMINT, :NOT_NULL
end

class Employees_salaries_emp_no < Innodb::RecordDescriber
  type :secondary
  key "emp_no",         :INT, :NOT_NULL
  row "from_date",      :MEDIUMINT, :NOT_NULL
end
