CREATE TABLE address (
  id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  subject_id SMALLINT UNSIGNED NOT NULL,
  address_kind_id SMALLINT UNSIGNED NOT NULL,
  street VARCHAR(128) NULL,
  town VARCHAR(128) NULL,
  zip VARCHAR(32) NULL,
  PRIMARY KEY(id),
  INDEX index_address_kind_id(address_kind_id),
  INDEX index_subject_id(subject_id)
);

CREATE TABLE address_kind (
  id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  title VARCHAR(64) NULL,
  PRIMARY KEY(id)
);

CREATE TABLE contact_kind (
  id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  title VARCHAR(64) NULL,
  PRIMARY KEY(id)
);

CREATE TABLE invoice (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  customer_id SMALLINT UNSIGNED NOT NULL,
  supplier_id SMALLINT UNSIGNED NOT NULL,
  num VARCHAR(32) NOT NULL,
  issued DATETIME NOT NULL,
  fullfilled DATETIME NOT NULL,
  pay_due DATETIME NOT NULL,
  pay_real DATETIME NULL,
  PRIMARY KEY(id),
  INDEX index_supplier_id(supplier_id),
  INDEX index_customer_id(customer_id)
);

CREATE TABLE invoice_has_task (
  invoice_id INTEGER UNSIGNED NOT NULL,
  task_id INTEGER UNSIGNED NOT NULL,
  PRIMARY KEY(invoice_id, task_id),
  INDEX index_invoice_id(invoice_id),
  INDEX index_task_id(task_id)
);

CREATE TABLE person (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  subject_id SMALLINT UNSIGNED NOT NULL,
  firstname VARCHAR(32) NULL,
  surname VARCHAR(64) NULL,
  middlename VARCHAR(32) NULL,
  titles_pre VARCHAR(32) NULL,
  titles_post VARCHAR(32) NULL,
  PRIMARY KEY(id),
  INDEX index_subject_id(subject_id)
);

CREATE TABLE person_contact (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  person_id INTEGER UNSIGNED NOT NULL,
  contact_kind_id SMALLINT UNSIGNED NOT NULL,
  content TEXT NULL,
  PRIMARY KEY(id),
  INDEX index_contact_kind_id(contact_kind_id),
  INDEX index_person_id(person_id)
);

CREATE TABLE project (
  id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  title VARCHAR(128) NOT NULL,
  PRIMARY KEY(id)
);

CREATE TABLE subject (
  id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(64) NULL,
  ico VARCHAR(32) NULL,
  dic VARCHAR(32) NULL,
  bank_account VARCHAR(96) NULL,
  www VARCHAR(250) NULL,
  PRIMARY KEY(id)
);

CREATE TABLE task (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  description TEXT NOT NULL,
  price INTEGER UNSIGNED NULL,
  vat SMALLINT UNSIGNED NULL,
  PRIMARY KEY(id)
);

CREATE TABLE task_has_project (
  task_id INTEGER UNSIGNED NOT NULL,
  project_id SMALLINT UNSIGNED NOT NULL,
  PRIMARY KEY(task_id, project_id),
  INDEX index_task_id(task_id),
  INDEX index_project_id(project_id)
);