/**
 * Auto-generated by tools/extract-migrations.js on 2026-07-17T11:10:38.953Z
 *
 * Track D (P1-9) — Knex migration for the Inventory concern.
 *
 * Each CREATE TABLE statement is the original SQL from
 * server/src/database/init.js, executed via knex.schema.raw() to
 * preserve CHECK constraints, FTS5 virtual tables, triggers, and
 * other constructs the Knex DSL cannot easily express.
 *
 * Future PRs may convert individual tables to knex.schema.createTable()
 * with the schema-builder DSL.
 */

exports.up = async function(knex) {
  // item_categories
  await knex.schema.raw("-- =============================================\r\n  -- ITEM CATEGORIES\r\n  -- =============================================\r\n  CREATE TABLE IF NOT EXISTS item_categories (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    name TEXT NOT NULL,\r\n    code TEXT,\r\n    description TEXT,\r\n    parent_id INTEGER REFERENCES item_categories(id),\r\n    created_at DATETIME DEFAULT (datetime('now', 'localtime')),\r\n    updated_at DATETIME DEFAULT (datetime('now', 'localtime')),\r\n    last_synced_at DATETIME,\r\n    sync_status TEXT DEFAULT 'pending',\r\n    is_deleted INTEGER DEFAULT 0\r\n  );");

  // suppliers
  await knex.schema.raw("-- =============================================\r\n  -- SUPPLIERS\r\n  -- =============================================\r\n  CREATE TABLE IF NOT EXISTS suppliers (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    name TEXT NOT NULL,\r\n    code TEXT,\r\n    contact_person TEXT,\r\n    email TEXT,\r\n    phone TEXT,\r\n    address TEXT,\r\n    city TEXT,\r\n    country TEXT DEFAULT 'Zimbabwe',\r\n    tax_id TEXT,\r\n    bank_details TEXT,\r\n    notes TEXT,\r\n    is_active INTEGER DEFAULT 1,\r\n    created_at DATETIME DEFAULT (datetime('now', 'localtime')),\r\n    updated_at DATETIME DEFAULT (datetime('now', 'localtime')),\r\n    last_synced_at DATETIME,\r\n    sync_status TEXT DEFAULT 'pending',\r\n    is_deleted INTEGER DEFAULT 0\r\n  );");

  // store_locations
  await knex.schema.raw("-- =============================================\r\n  -- STORE LOCATIONS\r\n  -- =============================================\r\n  CREATE TABLE IF NOT EXISTS store_locations (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    name TEXT NOT NULL,\r\n    code TEXT,\r\n    description TEXT,\r\n    address TEXT,\r\n    manager_id INTEGER REFERENCES users(id),\r\n    is_warehouse INTEGER DEFAULT 0,\r\n    created_at DATETIME DEFAULT (datetime('now', 'localtime')),\r\n    updated_at DATETIME DEFAULT (datetime('now', 'localtime')),\r\n    last_synced_at DATETIME,\r\n    sync_status TEXT DEFAULT 'pending',\r\n    is_deleted INTEGER DEFAULT 0\r\n  );");

  // items
  await knex.schema.raw("-- =============================================\r\n  -- ITEMS/INVENTORY\r\n  -- =============================================\r\n  CREATE TABLE IF NOT EXISTS items (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    name TEXT NOT NULL,\r\n    code TEXT,\r\n    barcode TEXT,\r\n    description TEXT,\r\n    category_id INTEGER REFERENCES item_categories(id),\r\n    unit TEXT NOT NULL,\r\n    purchase_price REAL DEFAULT 0,\r\n    selling_price REAL DEFAULT 0,\r\n    reorder_level INTEGER DEFAULT 10,\r\n    expiry_tracking INTEGER DEFAULT 0,\r\n    serial_tracking INTEGER DEFAULT 0,\r\n    supplier_id INTEGER REFERENCES suppliers(id),\r\n    is_active INTEGER DEFAULT 1,\r\n    created_at DATETIME DEFAULT (datetime('now', 'localtime')),\r\n    updated_at DATETIME DEFAULT (datetime('now', 'localtime')),\r\n    last_synced_at DATETIME,\r\n    sync_status TEXT DEFAULT 'pending',\r\n    is_deleted INTEGER DEFAULT 0\r\n  );");

  // item_stock
  await knex.schema.raw("-- =============================================\r\n  -- ITEM STOCK\r\n  -- =============================================\r\n  CREATE TABLE IF NOT EXISTS item_stock (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    item_id INTEGER NOT NULL REFERENCES items(id),\r\n    store_id INTEGER NOT NULL REFERENCES store_locations(id),\r\n    quantity INTEGER DEFAULT 0,\r\n    min_quantity INTEGER DEFAULT 0,\r\n    max_quantity INTEGER,\r\n    last_stocktake DATE,\r\n    created_at DATETIME DEFAULT (datetime('now', 'localtime')),\r\n    updated_at DATETIME DEFAULT (datetime('now', 'localtime')),\r\n    last_synced_at DATETIME,\r\n    sync_status TEXT DEFAULT 'pending',\r\n    is_deleted INTEGER DEFAULT 0\r\n  );");

  // stock_transactions
  await knex.schema.raw("-- =============================================\r\n  -- STOCK TRANSACTIONS\r\n  -- =============================================\r\n  CREATE TABLE IF NOT EXISTS stock_transactions (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    item_id INTEGER NOT NULL REFERENCES items(id),\r\n    store_id INTEGER NOT NULL REFERENCES store_locations(id),\r\n    type TEXT NOT NULL CHECK(type IN ('purchase', 'sale', 'adjustment', 'transfer', 'damage', 'expired', 'return')),\r\n    quantity INTEGER NOT NULL,\r\n    unit_price REAL,\r\n    total_amount REAL,\r\n    reference_number TEXT,\r\n    supplier_id INTEGER REFERENCES suppliers(id),\r\n    transaction_date DATETIME DEFAULT (datetime('now', 'localtime')),\r\n    notes TEXT,\r\n    created_by INTEGER REFERENCES users(id),\r\n    created_at DATETIME DEFAULT (datetime('now', 'localtime')),\r\n    updated_at DATETIME DEFAULT (datetime('now', 'localtime')),\r\n    last_synced_at DATETIME,\r\n    sync_status TEXT DEFAULT 'pending',\r\n    is_deleted INTEGER DEFAULT 0\r\n  );");

  // item_issues
  await knex.schema.raw("-- =============================================\r\n  -- ITEM ISSUES\r\n  -- =============================================\r\n  CREATE TABLE IF NOT EXISTS item_issues (\r\n    id INTEGER PRIMARY KEY AUTOINCREMENT,\r\n    uid TEXT UNIQUE,\r\n    item_id INTEGER NOT NULL REFERENCES items(id),\r\n    issued_to_type TEXT NOT NULL CHECK(issued_to_type IN ('staff', 'department', 'student', 'class', 'hostel', 'ground', 'kitchen')),\r\n    issued_to_id INTEGER,\r\n    quantity INTEGER NOT NULL,\r\n    purpose TEXT,\r\n    issue_date DATETIME DEFAULT (datetime('now', 'localtime')),\r\n    return_due_date DATE,\r\n    returned_date DATETIME,\r\n    returned_quantity INTEGER DEFAULT 0,\r\n    condition_on_return TEXT,\r\n    issued_by INTEGER REFERENCES users(id),\r\n    status TEXT DEFAULT 'issued' CHECK(status IN ('issued', 'returned', 'partially_returned', 'damaged')),\r\n    notes TEXT,\r\n    created_at DATETIME DEFAULT (datetime('now', 'localtime')),\r\n    updated_at DATETIME DEFAULT (datetime('now', 'localtime')),\r\n    last_synced_at DATETIME,\r\n    sync_status TEXT DEFAULT 'pending',\r\n    is_deleted INTEGER DEFAULT 0\r\n  );");

};

exports.down = async function(knex) {
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "item_issues" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "stock_transactions" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "item_stock" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "items" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "store_locations" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "suppliers" + ';');
  await knex.schema.raw('DROP TABLE IF EXISTS ' + "item_categories" + ';');
};
