DROP TABLE IF EXISTS registry;
DROP TABLE IF EXISTS transaction_types;
DROP TABLE IF EXISTS items;
DROP TABLE IF EXISTS categories;

CREATE TABLE categories (
    id INTEGER PRIMARY KEY,
	name TEXT NOT NULL
);

CREATE TABLE items (
    ref INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price REAL NOT NULL,
    subscriberPrice REAL NOT NULL,
	category INTEGER NOT NULL,
	FOREIGN KEY (category) REFERENCES categories (id)
);

CREATE TABLE transaction_types (
    id INTEGER PRIMARY KEY,
	name TEXT NOT NULL
);

CREATE TABLE registry (
    id INTEGER PRIMARY KEY,
	dt INTEGER NOT NULL,
	type INTEGER NOT NULL,
	description TEXT NOT NULL,
	debit REAL,
	credit REAL,
	remarks TEXT
);

INSERT INTO categories (id, name)
    VALUES (1, "Boissons"),
	       (2, "Snacks");

INSERT INTO items (ref, name, price, subscriberPrice, category)
    VALUES (1, "Jus d'orange", 1.00, 0.50, 1),
           (2, "Coca-Cola",    1.50, 1.00, 1),
		   (3, "Twix",         1.10, 0.80, 2);

INSERT INTO transaction_types (id, name)
    VALUES (1, "ACHAT"),
	       (2, "REGLEMENT"),
		   (3, "VENTE"),
		   (4, "ENCAISSEMENT");
		   
INSERT INTO registry (id, dt, type, description, debit, credit, remarks)
    VALUES (1,  datetime("2021-09-21 01:00:00"), 4, "101 Capital",       null, 500.0, null),
	       (2,  datetime("2021-09-21 01:30:00"), 4, "512 Banque",        500.0, null,  null),
		   (3,  datetime("2021-09-21 02:00:00"), 1, "601 Achat",         152.0, null, "Facture d'achat"),
		   (4,  datetime("2021-09-21 02:30:00"), 1, "401 Fournisseur",   null, 152.0, "Facture Achat = dette au fournisseur"),
		   (5,  datetime("2021-09-21 03:00:00"), 2, "401 Fournisseur",   152.0, null, "Règlement de la facture d'achat"),
		   (6,  datetime("2021-09-21 03:30:00"), 2, "512 Banque",        null, 152.0, "Règlement de la facture d'achat"),
		   (7,  datetime("2021-09-21 04:00:00"), 3, "706 Vente",         null,  60.0, "Vente"),
		   (8,  datetime("2021-09-21 04:30:00"), 3, "445 TVA Collectée", null,  12.0, null),
		   (9,  datetime("2021-09-21 05:00:00"), 3, "411 Client",        72.0,  null, "Facture vente = Dette client"),
		   (10, datetime("2021-09-21 05:30:00"), 4, "411 Client",        null,  72.0, "Encaissement de l'argent"),
		   (11, datetime("2021-09-21 06:00:00"), 4, "512 Banque",        72.0,  null, "= La dette du client est soldée");