ichiraku/base_datos.sql at main · jjoselblas27/ichiraku · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
CREATE DATABASE restaurant;
USE restaurant;

CREATE TABLE usuarios(
    id_user int NOT NULL AUTO_INCREMENT,
	username varchar(50) NOT NULL,
	password varchar(255) NOT NULL,
	correo varchar(255),
	telefono int,
    created_date DATETIME NOT NULL,
    modified_date DATETIME NOT NULL,
    PRIMARY KEY(id_user)
);

CREATE TABLE menu(
	plato_id int NOT NULL auto_increment,
	name_plato varchar(50) NOT null,
	tipo_plato varchar(50) NOT null,
	price float NOT null,
	created_date datetime,
	modified_date datetime,
	PRIMARY kEY (plato_id)
);

CREATE TABLE reservaciones (
	id_reservacion int NOT NULL AUTO_INCREMENT,
	nombre varchar(50) NOT NULL,
	n_personas int NOT NULL,
	tipo_mesa ENUM('Ventana - Piso1','Ventana - Piso2','Dentro - Piso1','Dentro - Piso2') NOT NULL,
	hora_reservacion time NOT NULL,
	fecha datetime,
	correo varchar(255),
	telefono int,
	created_date datetime,
	modified_date datetime,
	PRIMARY KEY (id_reservacion)
);

CREATE TABLE pedidos_online(
	id_pedido int NOT NULL AUTO_INCREMENT,
	plato varchar(200) NOT NULL,
	distrito enum('Callao', 'Comas', 'Carabayllo', 'San martin de Porres', 'Los Olivos') NOT NULL,
	dirección varchar(255) NOT NULL,
	id_user int NOT NULL,
	PRIMARY KEY(id_pedido),
	FOREIGN KEY (id_user) REFERENCES usuarios(id_user)
);

CREATE TABLE comentarios(
	id_comentario int NOT NULL AUTO_INCREMENT,
	id_user int NOT NULL,
	puntuacion int NOT NULL,
	comentario varchar(5000),
    modified_date DATETIME NOT NULL,
    created_date DATETIME NOT NULL,
	PRIMARY KEY(id_comentario),
	FOREIGN KEY (id_user) REFERENCES usuarios(id_user)
);

CREATE TABLE reclamos(
	id_reclamo int NOT NULL AUTO_INCREMENT,
	nombre varchar(60) NOT NULL,
	apellido_p varchar(50)	NOT NULL,
	apellido_m varchar(50) NOT NULL,
	direccion varchar(150) NOT NULL,
	tipo_documento enum('DNI','CARNET', 'PASAPORTE') NOT NULL,
	documento int NOT NULL,
	telefono int NOT NULL,
	email varchar(255) NOT NULL,
	edad int NOT NULL,
	reclamo varchar(5000) NOT NULL,
	created_date datetime,
	modified_date datetime,
	PRIMARY KEY (id_reclamo)
);

CREATE TABLE zonas_reparto(
	id_zona int NOT NULL AUTO_INCREMENT,
	distrito varchar(255) NOT NULL,
	disponibilidad ENUM('Si','No') NOT NULL,
	modified_date DATETIME,
	created_date DATETIME,
	PRIMARY KEY(id_zona)
);


--pruebas--
INSERT INTO usuarios(id_user, username, password, correo, telefono, created_date, modified_date)
VALUES (1,'JUAN', MD5('12345'), NULL, NULL, NOW(), NOW());
INSERT INTO usuarios(id_user, username, password, correo, telefono, created_date, modified_date)
VALUES (2,'JOSE', MD5('54321'), NULL, NULL, NOW(), NOW());

INSERT INTO comentarios(id_comentario, id_user, puntuacion, comentario, modified_date, created_date)
VALUES (1,1,5,"maso el restaurante :V", NOW(), NOW());
INSERT INTO comentarios(id_comentario, id_user, puntuacion, comentario, modified_date, created_date)
VALUES (2,2,3,"a nada :|", NOW(), NOW());

insert into menu values(1, 'California Roll', 'Maki', '25', '2019-09-14', '2020-08-15');
insert into menu values(2, 'Acevichado', 'Maki', '28' , '2020-07-14', '2020-09-15');
insert into menu values(3, 'Causa Roll ', 'Maki', '25', '2020-09-14', '2021-08-15');
insert into menu values(4, 'Lomo Saltado Maki ', 'Maki', '20', '2020-09-14', '2021-08-15');
INSERT INTO menu(plato_id, name_plato, tipo_plato, price, created_date, modified_date)
VALUES(5, "Ramen", "Sopa", 20.00, NOW(), NOW());
INSERT INTO menu(plato_id, name_plato, tipo_plato, price, created_date, modified_date)
VALUES(6, "Yakitori","brocheta", 40.00, NOW(), NOW());

INSERT INTO zonas_reparto(id_zona, distrito, disponibilidad, modified_Date, created_Date)
VALUES (1,'Callao','Si',NOW(),NOW());
INSERT INTO zonas_reparto(id_zona, distrito, disponibilidad, modified_Date, created_Date)
VALUES (2,'Comas','Si',NOW(),NOW());
INSERT INTO zonas_reparto(id_zona, distrito, disponibilidad, modified_Date, created_Date)
VALUES (3, 'Carabayllo','Si',NOW(),NOW());
INSERT INTO zonas_reparto(id_zona, distrito, disponibilidad, modified_Date, created_Date)
VALUES (4, 'San Martin de Porres','Si',NOW(),NOW());
INSERT INTO zonas_reparto(id_zona, distrito, disponibilidad, modified_Date, created_Date)
VALUES (5, 'Los Olivos','No',NOW(),NOW());


INSERT INTO reclamos (id_reclamo, nombre, apellido_p, apellido_m, direccion, tipo_documento, documento, telefono, email, edad, reclamo, modified_date, created_date)
VALUES (1,'ale','a','b','av.are','DNI', 73011282, 972043522,'[email protected]',19,'pesimo restaurante :/',NOW(), NOW());