create_database_script.sql

-- Database: la vanderia

-- DROP DATABASE la vanderia;

CREATE DATABASE la_vanderia
    WITH 
    OWNER = postgres
    ENCODING = 'UTF8'
    LC_COLLATE = 'Portuguese_Brazil.1252'
    LC_CTYPE = 'Portuguese_Brazil.1252'
    TABLESPACE = pg_default
    CONNECTION LIMIT = -1;
	
CREATE TABLE LAVANDERIA 
	(NOME VARCHAR(30) NOT NULL,
	CNPJ CHAR(14) NOT NULL,
	
	PRIMARY KEY(CNPJ));


CREATE TABLE USUARIO (
	NOME VARCHAR(40) NOT NULL,
	SENHA VARCHAR(40) NOT NULL,
	TELEFONE VARCHAR(11),
	CPF VARCHAR(11) NOT NULL,
	CNPJ_LAVANDERIA VARCHAR(14) NOT NULL,
	
	PRIMARY KEY(CPF),
	FOREIGN KEY (CNPJ_LAVANDERIA) REFERENCES LAVANDERIA(CNPJ) ON DELETE CASCADE);


CREATE TABLE CUSTOS (
	DESPESA REAL NOT NULL,
	CUSTO_MAO REAL NOT NULL,
	CUSTO_MAQUINA REAL NOT NULL,
	CUSTO_SECO REAL NOT NULL,
	LUCRO_ESPERADO REAL NOT NULL,
	CNPJ_LAVANDERIA VARCHAR(14) NOT NULL,
	
	FOREIGN KEY (CNPJ_LAVANDERIA) REFERENCES LAVANDERIA(CNPJ) ON DELETE CASCADE);


CREATE TABLE CLIENTE (NOME VARCHAR(30) NOT NULL,
	TELEFONE VARCHAR(11),
	EMAIL VARCHAR(100) NOT NULL,
	CPF VARCHAR(11) NOT NULL,
	CNPJ_LAVANDERIA VARCHAR(14) NOT NULL,
					  
	PRIMARY KEY(CPF),
	FOREIGN KEY (CNPJ_LAVANDERIA) REFERENCES LAVANDERIA(CNPJ) ON DELETE CASCADE);


CREATE TABLE PEDIDO (
	ID_PEDIDO SERIAL,
	PESO REAL NOT NULL, 
	TIPO INTEGER NOT NULL,
	STATUS VARCHAR(15) NOT NULL,
	PRECO REAL NOT NULL,
	CPF_CLIENTE VARCHAR(11) NOT NULL,
																						
	PRIMARY KEY(ID_PEDIDO),
	FOREIGN KEY (CPF_CLIENTE) REFERENCES CLIENTE(CPF) ON DELETE CASCADE);
	
INSERT INTO LAVANDERIA(NOME, CNPJ)
VALUES('Teste do sistema', '00000000000000');

INSERT INTO USUARIO(NOME, SENHA, TELEFONE, CPF, CNPJ_LAVANDERIA)
VALUES('TEST USER 0', '0', '31900000000', '00000000000', '00000000000000');
INSERT INTO USUARIO(NOME, SENHA, TELEFONE, CPF, CNPJ_LAVANDERIA)
VALUES('TEST USER 1', '1', '31900000001', '00000000001', '00000000000000');

INSERT INTO CLIENTE(NOME, TELEFONE, EMAIL, CPF, CNPJ_LAVANDERIA)
VALUES
('CLIENTE 1','31900000010', 'cliente1@teste.com', '00000000010', '00000000000000'),
('CLIENTE 2','31900000011', 'cliente2@teste.com', '00000000011', '00000000000000'),
('CLIENTE 3','31900000012', 'cliente3@teste.com', '00000000012', '00000000000000'),
('CLIENTE 4','31900000013', 'cliente4@teste.com', '00000000013', '00000000000000'),
('CLIENTE 5','31900000014', 'cliente1@teste.com', '00000000014', '00000000000000');

INSERT INTO CUSTOS(DESPESA, CUSTO_MAO, CUSTO_MAQUINA, CUSTO_SECO, LUCRO_ESPERADO, CNPJ_LAVANDERIA)
VALUES (10.00, 5.00, 2.5, 300, 20000, '00000000000000');

INSERT INTO PEDIDO(PESO, STATUS, TIPO, PRECO, CPF_CLIENTE)
VALUES
(10, 'NA FILA', 1, 100, '00000000010'),
(12.5, 'LAVANDO', 2, 20, '00000000011'),
(20, 'FINALIZADO', 3, 50, '00000000012'),
(5, 'ENTREGUE', 2, 30, '00000000013');