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')