109 lines
3.4 KiB
Python
109 lines
3.4 KiB
Python
import sqlite3
|
|
|
|
def create_tables():
|
|
conn = sqlite3.connect('horse_farm.db')
|
|
cursor = conn.cursor()
|
|
|
|
# Таблица для услуг
|
|
cursor.execute('''
|
|
CREATE TABLE IF NOT EXISTS services (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT NOT NULL,
|
|
description TEXT,
|
|
price INTEGER,
|
|
duration TEXT
|
|
)
|
|
''')
|
|
|
|
# Таблица для абонементов
|
|
cursor.execute('''
|
|
CREATE TABLE IF NOT EXISTS abonements (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT NOT NULL,
|
|
price INTEGER,
|
|
description TEXT
|
|
)
|
|
''')
|
|
|
|
# Таблица для подарочных сертификатов
|
|
cursor.execute('''
|
|
CREATE TABLE IF NOT EXISTS certificates (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT NOT NULL,
|
|
price INTEGER,
|
|
description TEXT
|
|
)
|
|
''')
|
|
|
|
# Таблица для пользователей
|
|
cursor.execute('''
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
username TEXT UNIQUE NOT NULL,
|
|
password TEXT NOT NULL,
|
|
email TEXT,
|
|
role TEXT DEFAULT 'user',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
)
|
|
''')
|
|
|
|
# Таблица для заказов
|
|
cursor.execute('''
|
|
CREATE TABLE IF NOT EXISTS orders (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id INTEGER,
|
|
service_type TEXT NOT NULL,
|
|
service_name TEXT NOT NULL,
|
|
service_id INTEGER,
|
|
date TEXT NOT NULL,
|
|
time TEXT NOT NULL,
|
|
persons INTEGER DEFAULT 1,
|
|
price INTEGER,
|
|
status TEXT DEFAULT 'pending',
|
|
comment TEXT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (user_id) REFERENCES users (id)
|
|
)
|
|
''')
|
|
|
|
# Таблица для отзывов (добавляем поле order_id)
|
|
cursor.execute('''
|
|
CREATE TABLE IF NOT EXISTS reviews (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id INTEGER,
|
|
order_id INTEGER,
|
|
username TEXT,
|
|
rating INTEGER CHECK(rating >= 1 AND rating <= 5),
|
|
comment TEXT NOT NULL,
|
|
status TEXT DEFAULT 'pending',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (user_id) REFERENCES users (id),
|
|
FOREIGN KEY (order_id) REFERENCES orders (id),
|
|
UNIQUE(user_id, order_id)
|
|
)
|
|
''')
|
|
|
|
# Таблица для сообщений
|
|
cursor.execute('''
|
|
CREATE TABLE IF NOT EXISTS messages (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
order_id INTEGER,
|
|
user_id INTEGER,
|
|
admin_id INTEGER,
|
|
message TEXT NOT NULL,
|
|
sender_type TEXT NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
is_read BOOLEAN DEFAULT 0,
|
|
FOREIGN KEY (order_id) REFERENCES orders (id),
|
|
FOREIGN KEY (user_id) REFERENCES users (id),
|
|
FOREIGN KEY (admin_id) REFERENCES users (id)
|
|
)
|
|
''')
|
|
|
|
conn.commit()
|
|
conn.close()
|
|
print("Таблицы успешно созданы!")
|
|
|
|
if __name__ == '__main__':
|
|
create_tables()
|