CREATE DATABASE tucniak;
USE tucniak;
CREATE TABLE s_items (
	pk_s_items		INT 		NOT NULL PRIMARY KEY AUTO_INCREMENT,
	fk_s_codes		INT			NOT NULL,
	fk_s_messures	INT			NOT NULL,
	number			VARCHAR(10) NOT NULL,
	name 			VARCHAR(40) NOT NULL
);
CREATE TABLE s_messures (
	pk_s_messures	INT 		NOT NULL PRIMARY KEY AUTO_INCREMENT,
	messure 		VARCHAR(10) NOT NULL
);
CREATE TABLE s_codes (
	pk_s_codes		INT 		NOT NULL PRIMARY KEY AUTO_INCREMENT,
	code 			VARCHAR(10) NOT NULL
);
CREATE TABLE s_add (
	pk_s_add		INT			NOT NULL PRIMARY KEY AUTO_INCREMENT,
	fk_s_inputs 	INT			NOT NULL,
	fk_s_items	 	INT			NOT NULL,
	count			INT			NOT NULL,
	price			INT			NOT NULL
);
#	guarantiee		DATETIME	NOT NULL DEFAULT ""
CREATE TABLE s_inputs (
	pk_s_inputs		INT			NOT NULL PRIMARY KEY AUTO_INCREMENT,
	fk_a_letters	INT			NOT NULL,
	code			VARCHAR(10)	NOT NULL,
	date			DATETIME	NOT NULL
);
CREATE TABLE s_serial (
	pk_s_serial		INT			NOT NULL PRIMARY KEY AUTO_INCREMENT,
	fk_s_add		INT			NOT NULL,
	number			VARCHAR(20)	NOT NULL
);
CREATE TABLE s_output (
	pk_s_output		INT			NOT NULL PRIMARY KEY AUTO_INCREMENT,
	fk_s_outputs	INT 		NOT NULL,
	count			INT			NOT NULL,
	price			INT			NOT NULL
);
CREATE TABLE s_outputs (
	pk_s_outputs	INT			NOT NULL PRIMARY KEY AUTO_INCREMENT,
	fk_a_letters	INT			NOT NULL,
	code			VARCHAR(10)	NOT NULL,
	date			DATETIME	NOT NULL
);

#ADDRESS BOOK

CREATE TABLE c_company (
	pk_c_company	INT			NOT NULL PRIMARY KEY AUTO_INCREMENT,
	name			VARCHAR(20) NOT NULL,
	idn				VARCHAR(20) NOT NULL,
	txn				VARCHAR(20) NOT NULL DEFAULT ""
);
CREATE TABLE c_person (
	pk_c_person		INT			NOT NULL PRIMARY KEY AUTO_INCREMENT,
	name			VARCHAR(20) NOT NULL DEFAULT "",
	surname			VARCHAR(20) NOT NULL DEFAULT "",
	nickname		VARCHAR(20) NOT NULL DEFAULT ""
);
CREATE TABLE list_c_employ (
	pk_list_c_employ	INT		NOT NULL PRIMARY KEY AUTO_INCREMENT,
	fk_c_company		INT		NOT NULL DEFAULT "1",
	fk_c_person			INT		NOT NULL
);
CREATE TABLE c_address (
	pk_c_adress		INT			NOT NULL PRIMARY KEY AUTO_INCREMENT,
	street			VARCHAR(20) NOT NULL,
	city			VARCHAR(20) NOT NULL,
	zip				VARCHAR(20) NOT NULL,
	state			VARCHAR(20) NOT NULL
);
CREATE TABLE c_phone (
	pk_c_phone		INT			NOT NULL PRIMARY KEY AUTO_INCREMENT,
	number			VARCHAR(20) NOT NULL
);
CREATE TABLE c_email (
	pk_c_email		INT			NOT NULL PRIMARY KEY AUTO_INCREMENT,
	url				VARCHAR(20) NOT NULL
);
CREATE TABLE c_account (
	pk_c_account	INT			NOT NULL PRIMARY KEY AUTO_INCREMENT,
	fk_c_bank		INT			NOT NULL,
	number			VARCHAR(20) NOT NULL
);
CREATE TABLE c_bank (
	pk_c_bank		INT			NOT NULL PRIMARY KEY AUTO_INCREMENT,
	name			VARCHAR(20) NOT NULL,
	code			VARCHAR(10) NOT NULL
);
CREATE TABLE lists_c (
	pk_lists_c		INT		NOT NULL PRIMARY KEY AUTO_INCREMENT,
	fk_table		INT 	NOT NULL,
	fk_contact		INT 	NOT NULL,
	c_table			ENUM("PERSON", "COMPANY"),
	c_contact		ENUM("ADDRESS", "PHONE", "EMAIL", "ACCOUNT", "PERSON", "COMPANY"),
	priority		ENUM("P","S") DEFAULT "S",
	note			VARCHAR(20) NOT NULL DEFAULT ""
);