PRAGMA foreign_keys = ON;
CREATE TABLE roles (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL UNIQUE);
CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, login TEXT NOT NULL UNIQUE, last_name TEXT NOT NULL, first_name TEXT NOT NULL, middle_name TEXT, role_id INTEGER, FOREIGN KEY (role_id) REFERENCES roles(id));
CREATE TABLE categories (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL);
CREATE TABLE subcategories (id INTEGER PRIMARY KEY AUTOINCREMENT, category_id INTEGER, name TEXT NOT NULL, FOREIGN KEY (category_id) REFERENCES categories(id));
CREATE TABLE manufacturers (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL);
CREATE TABLE products (id INTEGER PRIMARY KEY AUTOINCREMENT, article TEXT UNIQUE NOT NULL, name TEXT NOT NULL, subcategory_id INTEGER, manufacturer_id INTEGER, description TEXT, composition TEXT, price REAL NOT NULL, image_path TEXT, FOREIGN KEY (subcategory_id) REFERENCES subcategories(id), FOREIGN KEY (manufacturer_id) REFERENCES manufacturers(id));
CREATE TABLE sizes (id INTEGER PRIMARY KEY AUTOINCREMENT, size_value REAL NOT NULL);
CREATE TABLE product_stock (product_id INTEGER, size_id INTEGER, quantity INTEGER NOT NULL DEFAULT 0, PRIMARY KEY (product_id, size_id), FOREIGN KEY (product_id) REFERENCES products(id), FOREIGN KEY (size_id) REFERENCES sizes(id));
CREATE TABLE orders (id INTEGER PRIMARY KEY AUTOINCREMENT, user_id INTEGER, client_full_name TEXT NOT NULL, order_date DATE NOT NULL, status TEXT DEFAULT 'Новый', FOREIGN KEY (user_id) REFERENCES users(id));
CREATE TABLE order_items (id INTEGER PRIMARY KEY AUTOINCREMENT, order_id INTEGER, product_id INTEGER, size_id INTEGER, quantity INTEGER NOT NULL, price_at_moment REAL NOT NULL, FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE, FOREIGN KEY (product_id) REFERENCES products(id), FOREIGN KEY (size_id) REFERENCES sizes(id));