DROP TABLE IF EXISTS stocks;
DROP TABLE IF EXISTS registry;
DROP TABLE IF EXISTS transaction_types;
DROP TABLE IF EXISTS accounts;
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 accounts (
    id INTEGER PRIMARY KEY,
    num INTEGER NOT NULL,
    name TEXT NOT NULL
);

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

CREATE TABLE stocks (
    id INTEGER PRIMARY KEY,
    item INTEGER NOT NULL,
    quantity INTEGER NOT NULL,
    FOREIGN KEY (item) REFERENCES items (ref)
);

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 accounts (id, num, name)
    VALUES(1, 101, "Capital"),
          (2, 512, "Banque"),
          (3, 601, "Achat"),
          (4, 401, "Fournisseur"),
          (5, 706, "Vente"),
          (6, 445, "TVA Collectée"),
          (7, 411, "Client");
    

INSERT INTO registry (id, dt, type, account_id, debit, credit, remarks)
    VALUES (1,  datetime("2021-09-21 01:00:00"), 4, 1,  null, 500.0, null),
	       (2,  datetime("2021-09-21 01:30:00"), 4, 2, 500.0,  null, null),
		   (3,  datetime("2021-09-21 02:00:00"), 1, 3, 152.0,  null, "Facture d'achat"),
		   (4,  datetime("2021-09-21 02:30:00"), 1, 4,  null, 152.0, "Facture Achat = dette au fournisseur"),
		   (5,  datetime("2021-09-21 03:00:00"), 2, 4, 152.0,  null, "Règlement de la facture d'achat"),
		   (6,  datetime("2021-09-21 03:30:00"), 2, 2,  null, 152.0, "Règlement de la facture d'achat"),
		   (7,  datetime("2021-09-21 04:00:00"), 3, 5,  null,  60.0, "Vente"),
		   (8,  datetime("2021-09-21 04:30:00"), 3, 6,  null,  12.0, null),
		   (9,  datetime("2021-09-21 05:00:00"), 3, 7,  72.0,  null, "Facture vente = Dette client"),
		   (10, datetime("2021-09-21 05:30:00"), 4, 7,  null,  72.0, "Encaissement de l'argent"),
		   (11, datetime("2021-09-21 06:00:00"), 4, 2,  72.0,  null, "= La dette du client est soldée");

INSERT INTO stocks (id, item, quantity)
    VALUES(1, 1, 25),
    VALUES(2, 2, 12),
    VALUES(3, 3, 50);