PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE user (
	user_id INTEGER NOT NULL,
	email_id VARCHAR(50),
	PRIMARY KEY (user_id)
);
INSERT INTO "user" VALUES(1,'yash9414@gmail.com');
INSERT INTO "user" VALUES(2,'nareshreta@gmail.com');
INSERT INTO "user" VALUES(3,'adsf');
INSERT INTO "user" VALUES(4,'yash9415@gmail.com');
INSERT INTO "user" VALUES(5,'x@gmail.com');
CREATE TABLE organisation (
	org_id INTEGER NOT NULL,
	org_name VARCHAR(50),
	org_addr VARCHAR(150),
	pan_no VARCHAR(15),
	vat_no VARCHAR(20),
	service_tax_no VARCHAR(20),
	created_by INTEGER NOT NULL,
	created_at DATETIME,
	PRIMARY KEY (org_id),
	FOREIGN KEY(created_by) REFERENCES user (user_id)
);
INSERT INTO "organisation" VALUES(1,'The tribe fitness','Test addr','231662','44636','4363626',1,NULL);
INSERT INTO "organisation" VALUES(2,'The tribe fitness','Actual address','235261','23626','346266',1,'2017-06-11 06:09:38.347373');
INSERT INTO "organisation" VALUES(3,'Swastik ortho','Test address 2','23626','3662362','3626236',2,'2017-06-11 06:25:50.263505');
INSERT INTO "organisation" VALUES(4,'Test organisation','Test address','2526','34626','34646',1,'2017-06-11 11:02:37.578854');
INSERT INTO "organisation" VALUES(5,'Google Inc.','Devrabeesanhalli','23','62','6262',1,'2017-06-11 12:40:57.077790');
INSERT INTO "organisation" VALUES(6,'asdfadf','dfgs','sdfg','sdfg','sdfg',1,'2017-06-11 13:22:45.453378');
INSERT INTO "organisation" VALUES(7,'Just making sure!','hello buddy','2352','3252','3525',1,'2017-06-11 14:03:52.303643');
INSERT INTO "organisation" VALUES(8,'asdfa','asdf','asdf','asdf','asdf',1,'2017-06-11 14:04:43.588308');
INSERT INTO "organisation" VALUES(9,'asdfads','asdf','asdf','aasdf','aadsf',1,'2017-06-11 14:08:05.213899');
INSERT INTO "organisation" VALUES(10,'He','adsf','23462436342','3462436','63424627',1,'2017-07-29 13:43:02.353090');
CREATE TABLE costcenter (
	center_id INTEGER NOT NULL,
	center_name VARCHAR(50),
	created_at DATETIME,
	created_by INTEGER NOT NULL,
	org_id INTEGER NOT NULL,
	PRIMARY KEY (center_id),
	FOREIGN KEY(created_by) REFERENCES user (user_id),
	FOREIGN KEY(org_id) REFERENCES organisation (org_id)
);
CREATE TABLE invoice (
	invoice_id INTEGER NOT NULL,
	center_id INTEGER NOT NULL,
	base_amount INTEGER NOT NULL,
	tax_amount INTEGER NOT NULL,
	paid_status BOOLEAN,
	approved_status BOOLEAN,
	payment_mode VARCHAR(10),
	created_by INTEGER,
	approved_by INTEGER,
	paid_by INTEGER,
	created_at DATETIME,
	PRIMARY KEY (invoice_id),
	FOREIGN KEY(center_id) REFERENCES costcenter (center_id),
	CHECK (paid_status IN (0, 1)),
	CHECK (approved_status IN (0, 1)),
	FOREIGN KEY(created_by) REFERENCES user (user_id),
	FOREIGN KEY(approved_by) REFERENCES user (user_id),
	FOREIGN KEY(paid_by) REFERENCES user (user_id)
);
COMMIT;