DROP TABLE IF EXISTS category CASCADE;
DROP TABLE IF EXISTS activity CASCADE;
DROP TABLE IF EXISTS post CASCADE;

CREATE TABLE category(
	id SERIAL PRIMARY KEY NOT NULL,
	name varchar(250) NOT NULL
);
CREATE TABLE activity(
	id SERIAL PRIMARY KEY NOT NULL,
    idcat INTEGER REFERENCES category(id),
	title varchar(250) NOT NULL,
	description TEXT NOT NULL,
	name varchar(250) NOT NULL,
	phone varchar(10) NULL,
	email varchar(250)
);
CREATE TABLE post(
	title varchar(250) NOT NULL,
	description TEXT NOT NULL,
	name varchar(250) NOT NULL,
	phone varchar(10),
	email varchar(250),
    constraint key primary key(name, title)
);


INSERT INTO category(name) values('bar');
INSERT INTO category(name) values('concert');
INSERT INTO category(name)  values('monument');




INSERT INTO activity(title,idcat,description,name) values('soiree etudiants ',(SELECT id from category WHERE name='bar'),'Orient organise une soiree etudiants','orient express');
INSERT INTO activity(title,idcat,description,name) values('concert ACDC',(SELECT id from category WHERE name='concert'),'derniere concert acdc a ne pas rater','Caen Events');
INSERT INTO activity(title,idcat,description,name) values('Visite Chateau caen',(SELECT id from category WHERE name='monument'),'une visite guidée','mairie caen');

INSERT INTO post(title,description,name) values('Chat Perdu','je chercher mon chat qui a ete perdu avant hier sur la rue ec' ,'Julie');
INSERT INTO post(title,description,name) values('colocation','bonjour , jeune etudiant je cherche une colocation a partir de mai' ,'Sami');