/**
* MechaRocket
* @author evilnapsis
**/
create database mecharocketpro;
use mecharocketpro;



create table kind(
	id int not null auto_increment primary key,
	name varchar(255) not null
);

insert into kind (name) value ("Administrador");
insert into kind (name) value ("Inspector");
insert into kind (name) value ("Compras");
insert into kind (name) value ("Mecanico");
insert into kind (name) value ("Hojalateria y Pintura");
insert into kind (name) value ("Calidad");
insert into kind (name) value ("Ventas");
insert into kind (name) value ("Almacenista");



create table user(
	id int not null auto_increment primary key,
	bio text,
	image varchar(255),
	name varchar(50) ,
	lastname varchar(50) ,
	username varchar(50),
	email varchar(255) ,
	password varchar(60) ,
	phone varchar(255),
	address varchar(255),
	code varchar(20),
	status boolean not null default 1,
	kind boolean not null default 0,
	created_at datetime 
);

insert into user(email,password,status,kind,created_at) value ("admin","90b9aa7e25f80cf4f64e990b78a9fc5ebd6cecad",1,1,NOW());

create table client (
id int not null auto_increment primary key,
name text,
lastname text,
lastname2 text,
dni varchar(255),
address varchar(255),
company varchar(255),
phone varchar(255),
phone2 varchar(255),
email varchar(255),
password varchar(255),
status int default 1,
created_at datetime
);

create table provider (
id int not null auto_increment primary key,
name text,
lastname text,
lastname2 text,
dni varchar(255),
address varchar(255),
company varchar(255),
phone varchar(255),
phone2 varchar(255),
email varchar(255),
password varchar(255),
status int default 1,
created_at datetime
);

create table investor (
id int not null auto_increment primary key,
name text);
create table category(
	id int not null auto_increment primary key,
	name varchar(255),
	description varchar(255)
);

create table machine(
	id int not null auto_increment primary key,
	image1 varchar(255),
	image2 varchar(255),
	image3 varchar(255),
	image4 varchar(255),

	final_image1 varchar(255),
	final_image2 varchar(255),
	final_image3 varchar(255),
	final_image4 varchar(255),
	imprimir_valoracion text,

	title text,
	brand text,
	model text,
	serie text,
	pedimento text,
	tenencia text,
	description varchar(255),
	price double,
	status boolean not null default 1,
	investor_id int,
	work text,
	work2 text,
	work3 text,
	money double,
	category_id int,
	kind int default 1, /* 1. reparacion y venta, 2. solo reparacion, 3. directo a venta*/
	start_at date ,
	finish_at date,
	created_at datetime ,
	foreign key(category_id) references category(id),
	foreign key(investor_id) references investor(id)
);

create table machine_investor(
	id int not null auto_increment primary key,
	machine_id int not null,
	investor_id int not null,
	percent int,
	foreign key(machine_id) references machine(id),
	foreign key(investor_id) references investor(id)
);

create table machine_file(
	id int not null auto_increment primary key,
	machine_id int not null,
	file varchar(255) ,
	description varchar(512),
	created_at datetime,
	foreign key(machine_id) references machine(id)
);


create table category2(
	id int not null auto_increment primary key,
	name varchar(255),
	description varchar(255)
);

create table product(
	id int not null auto_increment primary key,
	title text,
	brand text,
	model text,
	serie text,
	umed varchar(50),
	description varchar(255),
	image varchar(255),
	price double,
	category2_id int,
	status boolean not null default 1,
	created_at datetime not null,
	foreign key (category2_id) references category2(id)
);


create table machine_product(
	id int not null auto_increment primary key,
	machine_id int not null,
	description varchar(1024),
	product_id int not null,
	investor_id int,
	q int,
	foreign key(investor_id) references investor(id),
	foreign key(machine_id) references machine(id),
	foreign key(product_id) references product(id)
);

create table status(
	id int not null auto_increment primary key,
	name varchar(255),
	color varchar(255)
);

insert into status(id, name, color) value(1,"Pendiente","warning");
insert into status(id, name, color) value(2,"En Proceso","info");
insert into status(id, name, color) value(3,"Terminado","success");
insert into status(id, name, color) value(4,"Vendido","primary");
insert into status(id, name, color) value(5,"Cancelado","danger");

create table action(
	id int not null auto_increment primary key,
	title text,
	description text,
	kind int /* 1. mecanico , 2. pintura, 3. calidad */
);

create table service (
	id int not null auto_increment primary key,
	image text,
	title text,
	description text,
	kind int, /* 1. mecanico , 2. pintura, 3. calidad */
	machine_id int not null,
	user_id int not null,
	status_id int not null default 1,
	created_at datetime,
	foreign key (user_id) references user(id),
	foreign key (machine_id) references machine(id)
);

create table progress (
	id int not null auto_increment primary key,
	image text,
	title text,
	description text,
	val int,
	machine_id int not null,
	service_id int not null,
	user_id int not null,
	created_at datetime,
	foreign key (user_id) references user(id),
	foreign key (machine_id) references machine(id),
	foreign key (service_id) references service(id)
);

create table progress_image(
	id int not null auto_increment primary key,
	image text,
	progress_id int not null,
	machine_id int not null,
	service_id int not null,
	foreign key (progress_id) references progress(id),
	foreign key (machine_id) references machine(id),
	foreign key (service_id) references service(id)
);

create table purchase (
	id int not null auto_increment primary key,
	code varchar(255),
	provider_id int not null,
	created_at datetime,
	foreign key (provider_id) references provider(id)
);

create table purchase_item (
	id int not null auto_increment primary key,
	code varchar(255),
	purchase_id int not null,
	product_id int not null,
	q int,
	created_at datetime,
	foreign key (product_id) references product(id),
	foreign key (purchase_id) references purchase(id)
);
/*
create table material_order (
	id int not null auto_increment primary key,
	image text,
	title text,
	description text,
	kind int, 
	machine_id int not null,
	user_id int not null,
	status_id int not null default 1,
	created_at datetime,
	foreign key (user_id) references user(id),
	foreign key (machine_id) references machine(id)
);
*/
/*
create table material_item (
	id int not null auto_increment primary key,
	code varchar(255),
	service_id int not null,
	machine_id int not null,
	item varchar(255),
	q int,
	created_at datetime,
	foreign key (service_id) references service(id)
);
*/
create table material_item (
	id int not null auto_increment primary key,
	code varchar(255),
	service_id int not null,
	machine_id int not null,
	product_id int not null,
	item varchar(255),
	q int,
	created_at datetime,
	foreign key (product_id) references product(id),
	foreign key (service_id) references service(id)
);
create table material_product (
	id int not null auto_increment primary key,
	code varchar(255),
	service_id int not null,
	machine_id int not null,
	product_id int not null,
	q int,
	created_at datetime,
	foreign key (product_id) references product(id),
	foreign key (service_id) references service(id)
);


create table sell (
	id int not null auto_increment primary key,
	code varchar(255),
	conditions varchar(255),
	machine_id int not null,
	client_id int not null,
	price double not null,
	user_id int not null,
	created_at datetime,
	foreign key (user_id) references user(id),
	foreign key (client_id) references client(id),
	foreign key (machine_id) references machine(id)
);

create table notification(
	id int not null auto_increment primary key,
	user_from int,
	user_to int,
	content varchar(512),
	machine_id int,
	service_id int,
	created_at datetime,
	readed int default 0,
	foreign key (user_from) references user(id),
	foreign key (user_to) references user(id),
	foreign key (machine_id) references machine(id) on delete cascade,
	foreign key (service_id) references service(id) on delete cascade
);


create table machine_image(
	id int not null auto_increment primary key,
	image text,
	machine_id int not null,
	foreign key (machine_id) references machine(id)
);