import sqlite3

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

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

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

                   customer_id      TEXT,
                   customer_name    TEXT,
                   customer_address TEXT,
                   PRIMARY KEY(type, series, id),
                   UNIQUE (id, type)
                 )''')

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

    conn.commit()
    conn.close()