64403d6fd6
- Реструктуризация: src/ разбит на middleware/, utils/, repositories/ (удалены), routes/ (удалены) - Добавлен src/original-html.ts — полный HTML с reportModal - Добавлен src/index.tsx.backup — React-компонент с reportModal - Миграции переименованы (0001_initial_schema.sql) - Добавлена миграция 0018 (удалена позже) - Docker: multi-stage build, wrangler.toml - Frontend: public/static/app.js + style.css - seed.sql добавлен - Документация: CHANGELOG, CHANGES_v4.1.0-4.1.9, PROJECT_STRUCTURE
81 lines
2.6 KiB
SQL
81 lines
2.6 KiB
SQL
-- Users table
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
username TEXT UNIQUE NOT NULL,
|
|
password_hash TEXT NOT NULL,
|
|
full_name TEXT NOT NULL,
|
|
role TEXT NOT NULL DEFAULT 'user',
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
deleted_at DATETIME DEFAULT NULL,
|
|
deleted_by INTEGER DEFAULT NULL
|
|
);
|
|
|
|
-- Production records table
|
|
CREATE TABLE IF NOT EXISTS production_records (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
month INTEGER NOT NULL,
|
|
year INTEGER NOT NULL,
|
|
client_name TEXT NOT NULL,
|
|
type TEXT,
|
|
offer_number TEXT NOT NULL,
|
|
work_number TEXT NOT NULL,
|
|
quantity INTEGER NOT NULL,
|
|
color TEXT,
|
|
notes TEXT,
|
|
problems TEXT,
|
|
installer TEXT,
|
|
price DECIMAL(10, 2),
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
deleted_at DATETIME DEFAULT NULL,
|
|
deleted_by INTEGER DEFAULT NULL
|
|
);
|
|
|
|
-- Status checkboxes table
|
|
CREATE TABLE IF NOT EXISTS status_checkboxes (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
record_id INTEGER NOT NULL,
|
|
material_date DATE,
|
|
material2_date DATE,
|
|
package_date DATE,
|
|
worksheets_date DATE,
|
|
cutting_date DATE,
|
|
glazing_date DATE,
|
|
ready_date DATE,
|
|
issued_date DATE,
|
|
worksheets_error INTEGER DEFAULT 0,
|
|
cutting_error INTEGER DEFAULT 0,
|
|
glazing_error INTEGER DEFAULT 0,
|
|
ready_error INTEGER DEFAULT 0,
|
|
issued_error INTEGER DEFAULT 0,
|
|
material_confirmed INTEGER DEFAULT 0,
|
|
material2_confirmed INTEGER DEFAULT 0,
|
|
worksheets_confirmed INTEGER DEFAULT 0,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (record_id) REFERENCES production_records(id) ON DELETE CASCADE
|
|
);
|
|
|
|
-- Audit log table
|
|
CREATE TABLE IF NOT EXISTS audit_log (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id INTEGER,
|
|
record_id INTEGER,
|
|
field_name TEXT NOT NULL,
|
|
old_value TEXT,
|
|
new_value TEXT,
|
|
action_type TEXT NOT NULL,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (user_id) REFERENCES users(id),
|
|
FOREIGN KEY (record_id) REFERENCES production_records(id)
|
|
);
|
|
|
|
-- Create indexes
|
|
CREATE INDEX IF NOT EXISTS idx_production_records_month_year ON production_records(month, year);
|
|
CREATE INDEX IF NOT EXISTS idx_production_records_client ON production_records(client_name);
|
|
CREATE INDEX IF NOT EXISTS idx_production_records_deleted ON production_records(deleted_at);
|
|
CREATE INDEX IF NOT EXISTS idx_status_checkboxes_record ON status_checkboxes(record_id);
|
|
CREATE INDEX IF NOT EXISTS idx_audit_log_record ON audit_log(record_id);
|
|
CREATE INDEX IF NOT EXISTS idx_audit_log_user ON audit_log(user_id);
|