378 lines
14 KiB
Python
378 lines
14 KiB
Python
"""
|
||
ScanLook Database Initialization
|
||
Creates all tables and indexes for the inventory management system
|
||
UPDATED: Reflects post-migration schema (CURRENT baseline is now global)
|
||
"""
|
||
|
||
import sqlite3
|
||
import os
|
||
from datetime import datetime
|
||
from werkzeug.security import generate_password_hash
|
||
|
||
DB_PATH = os.path.join(os.path.dirname(__file__), 'scanlook.db')
|
||
|
||
|
||
def init_database():
|
||
"""Initialize the database with all tables and indexes"""
|
||
conn = sqlite3.connect(DB_PATH)
|
||
cursor = conn.cursor()
|
||
|
||
# Users Table
|
||
cursor.execute('''
|
||
CREATE TABLE IF NOT EXISTS Users (
|
||
user_id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
username TEXT UNIQUE NOT NULL,
|
||
password TEXT NOT NULL,
|
||
full_name TEXT NOT NULL,
|
||
email TEXT,
|
||
role TEXT NOT NULL CHECK(role IN ('owner', 'admin', 'staff')),
|
||
is_active INTEGER DEFAULT 1,
|
||
branch TEXT DEFAULT 'Main',
|
||
created_timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
|
||
)
|
||
''')
|
||
|
||
# CountSessions Table
|
||
# NOTE: current_baseline_version removed - CURRENT is now global
|
||
cursor.execute('''
|
||
CREATE TABLE IF NOT EXISTS CountSessions (
|
||
session_id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
session_name TEXT NOT NULL,
|
||
session_type TEXT NOT NULL CHECK(session_type IN ('cycle_count', 'full_physical')),
|
||
created_by INTEGER NOT NULL,
|
||
created_timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
master_baseline_timestamp DATETIME,
|
||
current_baseline_timestamp DATETIME,
|
||
status TEXT DEFAULT 'active' CHECK(status IN ('active', 'completed', 'archived')),
|
||
branch TEXT DEFAULT 'Main',
|
||
FOREIGN KEY (created_by) REFERENCES Users(user_id)
|
||
)
|
||
''')
|
||
|
||
# BaselineInventory_Master Table (Session-specific, immutable)
|
||
cursor.execute('''
|
||
CREATE TABLE IF NOT EXISTS BaselineInventory_Master (
|
||
baseline_id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
session_id INTEGER NOT NULL,
|
||
lot_number TEXT NOT NULL,
|
||
item TEXT NOT NULL,
|
||
description TEXT,
|
||
system_location TEXT NOT NULL,
|
||
system_bin TEXT NOT NULL,
|
||
system_quantity REAL NOT NULL,
|
||
uploaded_timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (session_id) REFERENCES CountSessions(session_id)
|
||
)
|
||
''')
|
||
|
||
# BaselineInventory_Current Table (GLOBAL - shared across all sessions)
|
||
# MIGRATION CHANGE: No session_id, no baseline_version, no is_deleted
|
||
# This table is replaced entirely on each upload
|
||
cursor.execute('''
|
||
CREATE TABLE IF NOT EXISTS BaselineInventory_Current (
|
||
current_id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
lot_number TEXT NOT NULL,
|
||
item TEXT NOT NULL,
|
||
description TEXT,
|
||
system_location TEXT,
|
||
system_bin TEXT NOT NULL,
|
||
system_quantity REAL NOT NULL,
|
||
upload_timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
UNIQUE(lot_number, system_bin)
|
||
)
|
||
''')
|
||
|
||
# LocationCounts Table
|
||
cursor.execute('''
|
||
CREATE TABLE IF NOT EXISTS LocationCounts (
|
||
location_count_id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
session_id INTEGER NOT NULL,
|
||
location_name TEXT NOT NULL,
|
||
counted_by INTEGER NOT NULL,
|
||
start_timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
end_timestamp DATETIME,
|
||
status TEXT DEFAULT 'not_started' CHECK(status IN ('not_started', 'in_progress', 'completed')),
|
||
expected_lots_master INTEGER DEFAULT 0,
|
||
lots_found INTEGER DEFAULT 0,
|
||
lots_missing INTEGER DEFAULT 0,
|
||
FOREIGN KEY (session_id) REFERENCES CountSessions(session_id),
|
||
FOREIGN KEY (counted_by) REFERENCES Users(user_id)
|
||
)
|
||
''')
|
||
|
||
# ScanEntries Table
|
||
# MIGRATION CHANGE: Removed current_* columns - now fetched via JOIN
|
||
cursor.execute('''
|
||
CREATE TABLE IF NOT EXISTS ScanEntries (
|
||
entry_id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
session_id INTEGER NOT NULL,
|
||
location_count_id INTEGER NOT NULL,
|
||
lot_number TEXT NOT NULL,
|
||
item TEXT,
|
||
description TEXT,
|
||
scanned_location TEXT NOT NULL,
|
||
actual_weight REAL NOT NULL,
|
||
scanned_by INTEGER NOT NULL,
|
||
scan_timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
|
||
-- MASTER baseline comparison (immutable, set at scan time)
|
||
master_status TEXT CHECK(master_status IN ('match', 'wrong_location', 'ghost_lot', 'missing')),
|
||
master_expected_location TEXT,
|
||
master_expected_weight REAL,
|
||
master_variance_lbs REAL,
|
||
master_variance_pct REAL,
|
||
|
||
-- Duplicate detection
|
||
duplicate_status TEXT DEFAULT '00' CHECK(duplicate_status IN ('00', '01', '03', '04')),
|
||
duplicate_info TEXT,
|
||
|
||
-- CURRENT baseline comparison removed - now via JOIN in queries
|
||
-- Removed: current_status, current_system_location, current_system_weight,
|
||
-- current_variance_lbs, current_variance_pct, current_baseline_version
|
||
|
||
-- Metadata
|
||
comment TEXT,
|
||
is_deleted INTEGER DEFAULT 0,
|
||
deleted_by INTEGER,
|
||
deleted_timestamp DATETIME,
|
||
modified_timestamp DATETIME,
|
||
|
||
FOREIGN KEY (session_id) REFERENCES CountSessions(session_id),
|
||
FOREIGN KEY (location_count_id) REFERENCES LocationCounts(location_count_id),
|
||
FOREIGN KEY (scanned_by) REFERENCES Users(user_id),
|
||
FOREIGN KEY (deleted_by) REFERENCES Users(user_id)
|
||
)
|
||
''')
|
||
|
||
# MissingLots Table
|
||
cursor.execute('''
|
||
CREATE TABLE IF NOT EXISTS MissingLots (
|
||
missing_id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
session_id INTEGER NOT NULL,
|
||
location_count_id INTEGER,
|
||
lot_number TEXT NOT NULL,
|
||
item TEXT,
|
||
master_expected_location TEXT NOT NULL,
|
||
master_expected_quantity REAL NOT NULL,
|
||
current_system_location TEXT,
|
||
current_system_quantity REAL,
|
||
marked_by INTEGER NOT NULL,
|
||
marked_timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
found_later TEXT DEFAULT 'N' CHECK(found_later IN ('Y', 'N')),
|
||
found_location TEXT,
|
||
FOREIGN KEY (session_id) REFERENCES CountSessions(session_id),
|
||
FOREIGN KEY (location_count_id) REFERENCES LocationCounts(location_count_id),
|
||
FOREIGN KEY (marked_by) REFERENCES Users(user_id)
|
||
)
|
||
''')
|
||
|
||
# ============================================
|
||
# MODULE SYSTEM TABLES
|
||
# ============================================
|
||
|
||
# Modules Table - Available feature modules
|
||
cursor.execute('''
|
||
CREATE TABLE IF NOT EXISTS Modules (
|
||
module_id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
module_name TEXT NOT NULL,
|
||
module_key TEXT UNIQUE NOT NULL,
|
||
description TEXT,
|
||
icon TEXT,
|
||
is_active INTEGER DEFAULT 1,
|
||
display_order INTEGER DEFAULT 0
|
||
)
|
||
''')
|
||
|
||
# UserModules Table - Module access per user
|
||
cursor.execute('''
|
||
CREATE TABLE IF NOT EXISTS UserModules (
|
||
user_module_id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
user_id INTEGER NOT NULL,
|
||
module_id INTEGER NOT NULL,
|
||
granted_by INTEGER,
|
||
granted_timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (user_id) REFERENCES Users(user_id),
|
||
FOREIGN KEY (module_id) REFERENCES Modules(module_id),
|
||
FOREIGN KEY (granted_by) REFERENCES Users(user_id),
|
||
UNIQUE(user_id, module_id)
|
||
)
|
||
''')
|
||
|
||
# ============================================
|
||
# CONSUMPTION SHEETS MODULE TABLES
|
||
# ============================================
|
||
|
||
# cons_processes - Master list of consumption sheet process types
|
||
cursor.execute('''
|
||
CREATE TABLE IF NOT EXISTS cons_processes (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
process_key TEXT UNIQUE NOT NULL,
|
||
process_name TEXT NOT NULL,
|
||
template_file BLOB,
|
||
template_filename TEXT,
|
||
rows_per_page INTEGER DEFAULT 30,
|
||
detail_start_row INTEGER DEFAULT 10,
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
created_by INTEGER NOT NULL,
|
||
is_active INTEGER DEFAULT 1,
|
||
FOREIGN KEY (created_by) REFERENCES Users(user_id)
|
||
)
|
||
''')
|
||
|
||
# cons_process_fields - Custom field definitions for each process
|
||
cursor.execute('''
|
||
CREATE TABLE IF NOT EXISTS cons_process_fields (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
process_id INTEGER NOT NULL,
|
||
table_type TEXT NOT NULL CHECK(table_type IN ('header', 'detail')),
|
||
field_name TEXT NOT NULL,
|
||
field_label TEXT NOT NULL,
|
||
field_type TEXT NOT NULL CHECK(field_type IN ('TEXT', 'INTEGER', 'REAL', 'DATE', 'DATETIME')),
|
||
max_length INTEGER,
|
||
is_required INTEGER DEFAULT 0,
|
||
is_duplicate_key INTEGER DEFAULT 0,
|
||
is_active INTEGER DEFAULT 1,
|
||
sort_order INTEGER DEFAULT 0,
|
||
excel_cell TEXT,
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (process_id) REFERENCES cons_processes(id)
|
||
)
|
||
''')
|
||
|
||
# cons_sessions - Staff scanning sessions
|
||
cursor.execute('''
|
||
CREATE TABLE IF NOT EXISTS cons_sessions (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
process_id INTEGER NOT NULL,
|
||
created_by INTEGER NOT NULL,
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
status TEXT DEFAULT 'active' CHECK(status IN ('active', 'archived')),
|
||
FOREIGN KEY (process_id) REFERENCES cons_processes(id),
|
||
FOREIGN KEY (created_by) REFERENCES Users(user_id)
|
||
)
|
||
''')
|
||
|
||
# Note: Header values still use flexible key-value storage
|
||
# cons_session_header_values - Flexible storage for header field values
|
||
cursor.execute('''
|
||
CREATE TABLE IF NOT EXISTS cons_session_header_values (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
session_id INTEGER NOT NULL,
|
||
field_id INTEGER NOT NULL,
|
||
field_value TEXT,
|
||
FOREIGN KEY (session_id) REFERENCES cons_sessions(id),
|
||
FOREIGN KEY (field_id) REFERENCES cons_process_fields(id)
|
||
)
|
||
''')
|
||
|
||
# Note: Detail tables are created dynamically per process as cons_proc_{process_key}_details
|
||
# They include system columns (id, session_id, scanned_by, scanned_at, duplicate_status,
|
||
# duplicate_info, comment, is_deleted) plus custom fields defined in cons_process_fields
|
||
|
||
# Create Indexes
|
||
# MASTER baseline indexes
|
||
cursor.execute('CREATE INDEX IF NOT EXISTS idx_baseline_master_lot ON BaselineInventory_Master(session_id, lot_number)')
|
||
cursor.execute('CREATE INDEX IF NOT EXISTS idx_baseline_master_loc ON BaselineInventory_Master(session_id, system_location)')
|
||
|
||
# ScanEntries indexes
|
||
cursor.execute('CREATE INDEX IF NOT EXISTS idx_scanentries_session ON ScanEntries(session_id)')
|
||
cursor.execute('CREATE INDEX IF NOT EXISTS idx_scanentries_location ON ScanEntries(location_count_id)')
|
||
cursor.execute('CREATE INDEX IF NOT EXISTS idx_scanentries_lot ON ScanEntries(lot_number)')
|
||
cursor.execute('CREATE INDEX IF NOT EXISTS idx_scanentries_deleted ON ScanEntries(is_deleted)')
|
||
|
||
# LocationCounts indexes
|
||
cursor.execute('CREATE INDEX IF NOT EXISTS idx_location_counts ON LocationCounts(session_id, status)')
|
||
|
||
# Note: No indexes on BaselineInventory_Current needed - UNIQUE constraint handles lookups
|
||
|
||
# Consumption Sheets indexes
|
||
cursor.execute('CREATE INDEX IF NOT EXISTS idx_cons_process_fields_process ON cons_process_fields(process_id, table_type)')
|
||
cursor.execute('CREATE INDEX IF NOT EXISTS idx_cons_process_fields_active ON cons_process_fields(process_id, is_active)')
|
||
cursor.execute('CREATE INDEX IF NOT EXISTS idx_cons_sessions_process ON cons_sessions(process_id, status)')
|
||
cursor.execute('CREATE INDEX IF NOT EXISTS idx_cons_sessions_user ON cons_sessions(created_by, status)')
|
||
# Note: Detail table indexes are created dynamically when process tables are created
|
||
|
||
conn.commit()
|
||
conn.close()
|
||
print(f"✅ Database initialized at: {DB_PATH}")
|
||
print("📝 Schema version: Post-migration (CURRENT baseline is global)")
|
||
|
||
|
||
def create_default_users():
|
||
"""Create default users for testing"""
|
||
conn = sqlite3.connect(DB_PATH)
|
||
cursor = conn.cursor()
|
||
|
||
default_users = [
|
||
('owner', generate_password_hash('owner123'), 'System Owner', 'owner'),
|
||
('admin', generate_password_hash('admin123'), 'Admin User', 'admin'),
|
||
('staff1', generate_password_hash('staff123'), 'John Doe', 'staff'),
|
||
('staff2', generate_password_hash('staff123'), 'Jane Smith', 'staff'),
|
||
]
|
||
|
||
try:
|
||
cursor.executemany('''
|
||
INSERT INTO Users (username, password, full_name, role)
|
||
VALUES (?, ?, ?, ?)
|
||
''', default_users)
|
||
conn.commit()
|
||
print("✅ Default users created:")
|
||
print(" Owner: owner / owner123")
|
||
print(" Admin: admin / admin123")
|
||
print(" Staff: staff1 / staff123")
|
||
print(" Staff: staff2 / staff123")
|
||
except sqlite3.IntegrityError:
|
||
print("ℹ️ Default users already exist")
|
||
|
||
conn.close()
|
||
|
||
|
||
def create_default_modules():
|
||
"""Create default modules and assign to admin users"""
|
||
conn = sqlite3.connect(DB_PATH)
|
||
cursor = conn.cursor()
|
||
|
||
# Define default modules
|
||
default_modules = [
|
||
('Inventory Counts', 'counting', 'Cycle counts and physical inventory', 'fa-clipboard-check', 1, 1),
|
||
('Consumption Sheets', 'cons_sheets', 'Production consumption tracking', 'fa-clipboard-list', 1, 2),
|
||
]
|
||
|
||
# Insert modules (ignore if already exist)
|
||
for module in default_modules:
|
||
try:
|
||
cursor.execute('''
|
||
INSERT INTO Modules (module_name, module_key, description, icon, is_active, display_order)
|
||
VALUES (?, ?, ?, ?, ?, ?)
|
||
''', module)
|
||
except sqlite3.IntegrityError:
|
||
pass # Module already exists
|
||
|
||
conn.commit()
|
||
|
||
# Auto-assign all modules to owner and admin users
|
||
cursor.execute('SELECT user_id FROM Users WHERE role IN ("owner", "admin")')
|
||
admin_users = cursor.fetchall()
|
||
|
||
cursor.execute('SELECT module_id FROM Modules')
|
||
all_modules = cursor.fetchall()
|
||
|
||
for user in admin_users:
|
||
for module in all_modules:
|
||
try:
|
||
cursor.execute('''
|
||
INSERT INTO UserModules (user_id, module_id)
|
||
VALUES (?, ?)
|
||
''', (user[0], module[0]))
|
||
except sqlite3.IntegrityError:
|
||
pass # Assignment already exists
|
||
|
||
conn.commit()
|
||
conn.close()
|
||
print("✅ Default modules created and assigned to admin users")
|
||
|
||
|
||
if __name__ == '__main__':
|
||
init_database()
|
||
create_default_users()
|
||
create_default_modules() |