45 lines
2.2 KiB
SQL
45 lines
2.2 KiB
SQL
CREATE TABLE IF NOT EXISTS inventory_products (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
category TEXT NOT NULL CHECK(category IN ('medication','dewormer','vaccine')),
|
|
reference_id INTEGER,
|
|
name TEXT NOT NULL,
|
|
unit TEXT NOT NULL DEFAULT 'unité',
|
|
minimum_quantity REAL NOT NULL DEFAULT 0 CHECK(minimum_quantity >= 0),
|
|
active INTEGER NOT NULL DEFAULT 1 CHECK(active IN (0,1)),
|
|
created_at TEXT NOT NULL DEFAULT(datetime('now')),
|
|
updated_at TEXT NOT NULL DEFAULT(datetime('now')),
|
|
UNIQUE(category,reference_id),
|
|
UNIQUE(category,name)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS inventory_batches (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
product_id INTEGER NOT NULL REFERENCES inventory_products(id) ON DELETE CASCADE,
|
|
batch_number TEXT,
|
|
expires_on TEXT,
|
|
supplier_contact_id INTEGER REFERENCES directory_contacts(id) ON DELETE SET NULL,
|
|
unit_cost_cents INTEGER CHECK(unit_cost_cents IS NULL OR unit_cost_cents >= 0),
|
|
quantity REAL NOT NULL DEFAULT 0,
|
|
opened_on TEXT,
|
|
notes TEXT,
|
|
created_at TEXT NOT NULL DEFAULT(datetime('now'))
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS inventory_movements (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
product_id INTEGER NOT NULL REFERENCES inventory_products(id) ON DELETE CASCADE,
|
|
batch_id INTEGER REFERENCES inventory_batches(id) ON DELETE SET NULL,
|
|
movement_type TEXT NOT NULL CHECK(movement_type IN ('entry','administration','loss','correction')),
|
|
quantity_delta REAL NOT NULL CHECK(quantity_delta <> 0),
|
|
animal_id INTEGER REFERENCES animals(id) ON DELETE SET NULL,
|
|
occurred_at TEXT NOT NULL DEFAULT(datetime('now')),
|
|
reason TEXT,
|
|
created_by INTEGER REFERENCES users(id) ON DELETE SET NULL,
|
|
created_at TEXT NOT NULL DEFAULT(datetime('now'))
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_inventory_batches_product ON inventory_batches(product_id,expires_on);
|
|
CREATE INDEX IF NOT EXISTS idx_inventory_movements_product ON inventory_movements(product_id,occurred_at DESC);
|
|
|
|
INSERT OR IGNORE INTO app_settings(setting_key,setting_value) VALUES
|
|
('backup_schedule_enabled','0'),('backup_schedule_frequency','daily'),('backup_last_run_at',''),('backup_last_verified_at',''),
|
|
('privacy_retention_adopter_years','5'),('privacy_retention_foster_years','5'),('privacy_retention_volunteer_years','5'),('privacy_access_log_months','24');
|