71 lines
2.4 KiB
Python
71 lines
2.4 KiB
Python
import mysql.connector, bcrypt
|
|
|
|
conn = mysql.connector.connect(host='10.7.0.1', user='nick', password='nick9924Tucker9924##', database='TonjumTechRecords')
|
|
cursor = conn.cursor()
|
|
|
|
cursor.execute("""
|
|
CREATE TABLE IF NOT EXISTS Users (
|
|
Id INT AUTO_INCREMENT PRIMARY KEY,
|
|
Username VARCHAR(50) NOT NULL UNIQUE,
|
|
PasswordHash VARCHAR(255) NOT NULL,
|
|
CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP
|
|
)
|
|
""")
|
|
|
|
cursor.execute("""
|
|
CREATE TABLE IF NOT EXISTS Clients (
|
|
Id INT AUTO_INCREMENT PRIMARY KEY,
|
|
Name VARCHAR(255) NOT NULL UNIQUE,
|
|
ContactName VARCHAR(255) DEFAULT NULL,
|
|
Phone VARCHAR(50) DEFAULT NULL,
|
|
Email VARCHAR(255) DEFAULT NULL,
|
|
Address TEXT DEFAULT NULL,
|
|
CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP
|
|
)
|
|
""")
|
|
|
|
cursor.execute("""
|
|
CREATE TABLE IF NOT EXISTS Subscriptions (
|
|
Id INT AUTO_INCREMENT PRIMARY KEY,
|
|
ClientId INT NOT NULL,
|
|
ServiceName VARCHAR(255) NOT NULL,
|
|
Amount DECIMAL(12,2) NOT NULL,
|
|
`Interval` ENUM("Weekly","Monthly","Quarterly","Annually") NOT NULL,
|
|
StartDate DATE NOT NULL,
|
|
EndDate DATE NULL,
|
|
NextBillingDate DATE NOT NULL,
|
|
IsActive BOOLEAN DEFAULT TRUE,
|
|
CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
UpdatedAt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (ClientId) REFERENCES Clients(Id) ON DELETE CASCADE
|
|
)
|
|
""")
|
|
|
|
cursor.execute("""
|
|
CREATE TABLE IF NOT EXISTS Transactions (
|
|
Id INT AUTO_INCREMENT PRIMARY KEY,
|
|
Type ENUM("Income","Expense") NOT NULL,
|
|
TransactionDate DATE NOT NULL,
|
|
ClientId INT,
|
|
Amount DECIMAL(12,2) NOT NULL,
|
|
PaymentMethod ENUM("Cash","Venmo","Check","Other","Card","Stripe") NOT NULL,
|
|
Description TEXT,
|
|
SubscriptionId INT NULL,
|
|
ImageData LONGBLOB,
|
|
ImageContentType VARCHAR(100),
|
|
CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
UpdatedAt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (ClientId) REFERENCES Clients(Id) ON DELETE SET NULL,
|
|
FOREIGN KEY (SubscriptionId) REFERENCES Subscriptions(Id) ON DELETE SET NULL
|
|
)
|
|
""")
|
|
|
|
pw_hash = bcrypt.hashpw('nick9924'.encode(), bcrypt.gensalt()).decode()
|
|
cursor.execute("INSERT IGNORE INTO Users (Username, PasswordHash) VALUES (%s, %s)", ('nick', pw_hash))
|
|
cursor.execute("UPDATE Users SET PasswordHash = %s WHERE Username = %s", (pw_hash, 'nick'))
|
|
|
|
conn.commit()
|
|
cursor.close()
|
|
conn.close()
|
|
print('All tables created and user seeded OK')
|