import sqlite3

def create(dbname):
    conn = sqlite3.connect(dbname)
    c = conn.cursor()

    c.execute('''CREATE TABLE products
                 ( id          INTEGER PRIMARY KEY,
                   invoice_id  INTEGER,
                   description TEXT NOT NULL,
                   units       REAL NOT NULL,
                   price_unit  REAL NOT NULL,
                   vat         REAL,
                   FOREIGN KEY(invoice_id) REFERENCES invoices(id)
                   ON DELETE CASCADE )''')

    # date is %Y-%m-%d
    c.execute('''CREATE TABLE invoices
                 ( id       INTEGER PRIMARY KEY,
                   type     TEXT NOT NULL,
                   series   INTEGER NOT NULL,
                   date     TEXT NOT NULL,
                   notes    TEXT,

                   customer_id      TEXT,
                   customer_name    TEXT,
                   customer_address TEXT
                 )''')

    c.execute('''CREATE TABLE taxes
                 ( name         TEXT NOT NULL,
                   invoice_id   INTEGER,
                   ratio        REAL,
                   PRIMARY KEY(name, invoice_id)
                   FOREIGN KEY(invoice_id) REFERENCES invoices(id)
                   ON DELETE CASCADE )''')

    conn.commit()
    conn.close()