saas_payment/database.py at main · VolenBE/saas_payment · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
import sqlite3

dbase = sqlite3.connect('database.db', isolation_level=None)
print('Database opened')
dbase.execute("PRAGMA foreign_keys = 1")

#Users

dbase.execute(''' CREATE TABLE IF NOT EXISTS Users
    (
        ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
        username VARCHAR(64) NOT NULL,
        password VARCHAR(64) NOT NULL,
        bankaccount INT NOT NULL,
        address VARCHAR(128) NOT NULL
    )''')
print("Table user created successfully")

#Companies

dbase.execute(''' CREATE TABLE IF NOT EXISTS Companies
    (
        company_id INT,
        vatid INT NOT NULL,
        company_name VARCHAR NOT NULL,
        FOREIGN KEY (company_id) REFERENCES Users(ID)
    )''')
print("Table Companies created successfully")

#Clients

dbase.execute(''' CREATE TABLE IF NOT EXISTS Clients
    (
        client_id INT NOT NULL,
        company_id INT NOT NULL,
        FOREIGN KEY (company_id) REFERENCES Companies(company_id),
        FOREIGN KEY (client_id) REFERENCES Users(id)
    )''')
print("Table Clients created successfully")

#Analytics

dbase.execute(''' CREATE TABLE IF NOT EXISTS Analytics
    (
        ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
        MRR INT NOT NULL,
        AAR INT NOT NULL,
        number_customers INT NOT NULL,
        average_revenue INT NOT NULL,
        company_id INT NOT NULL,
        FOREIGN KEY (company_id) REFERENCES Companies(company_id)
    )''')
print("Table Analytics created successfully")

#Quotes

dbase.execute(''' CREATE TABLE IF NOT EXISTS Quotes
    (
        quote_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
        company_id INT NOT NULL,
        client_id INT NOT NULL,
        quantity INT NOT NULL,
        subscriptions_list VARCHAR(248),
        price_eur INT NOT NULL,
        accepted BOOLEAN,
        FOREIGN KEY (company_id) REFERENCES Companies(company_id),
        FOREIGN KEY (client_id) REFERENCES Clients(client_id)
    )''')
print("Table Quotes created successfully")

#Invoices

dbase.execute(''' CREATE TABLE IF NOT EXISTS Invoices
    (
        invoice_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
        pending BOOLEAN,
        client_id INT NOT NULL,
        quote_id INT NOT NULL,
        amount INT NOT NULL,
        FOREIGN KEY (client_id) REFERENCES clients(client_id),
        FOREIGN KEY (quote_id) REFERENCES Quotes(quote_id)
    )''')
print("Table Invoices created successfully")

#Payments

dbase.execute(''' CREATE TABLE IF NOT EXISTS Payments
    (
        payment_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
        invoice_id INT NOT NULL,
        amount_eur INT NOT NULL,
        currency_name char(3) NOT NULL,
        amount_currency INT NOT NULL,
        success BOOLEAN NOT NULL,
        LastPaymentDate TIMESTAMP,
        FOREIGN KEY (invoice_id) REFERENCES Invoices(invoice_id)
    )''')
print("Table Payments created successfully")

#Subscriptions

dbase.execute(''' CREATE TABLE IF NOT EXISTS Subscriptions
    (
        subscription_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
        name VARCHAR(64),
        client_id INT NOT NULL,
        status INT NOT NULL,
        price INT NOT NULL,
        FOREIGN KEY (client_id) REFERENCES Clients(client_id)
    )''')
print("Table Subscriptions created successfully")

# Technical

dbase.execute('''CREATE TABLE IF NOT EXISTS Tech
    (
        LastReset TIMESTAMP
    )
''')
print("Table Tech created successfully")

dbase.close()
print('Database Closed')