-- StockHub v3 SQL (MySQL)
-- Create database: stockhub

CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  full_name VARCHAR(120) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role VARCHAR(50) NOT NULL DEFAULT 'Staff/Requester',
  is_active TINYINT NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS audit_logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NULL,
  action VARCHAR(60) NOT NULL,
  details VARCHAR(255) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX(user_id)
);

CREATE TABLE IF NOT EXISTS categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL UNIQUE,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS units (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL UNIQUE,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS vendors (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(160) NOT NULL,
  gstin VARCHAR(20) NULL,
  contact VARCHAR(50) NULL,
  address TEXT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS vendor_item_rates (
  id INT AUTO_INCREMENT PRIMARY KEY,
  vendor_id INT NOT NULL,
  item_id INT NOT NULL,
  rate DECIMAL(12,2) NOT NULL DEFAULT 0,
  effective_date DATE NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX(vendor_id),
  INDEX(item_id),
  INDEX(effective_date)
);

CREATE TABLE IF NOT EXISTS locations (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(160) NOT NULL,
  location_type VARCHAR(50) NOT NULL DEFAULT 'Store',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(200) NOT NULL,
  category_id INT NOT NULL,
  unit_id INT NOT NULL,
  item_type VARCHAR(20) NOT NULL DEFAULT 'Consumable',
  track_serial TINYINT NOT NULL DEFAULT 0,
  track_batch TINYINT NOT NULL DEFAULT 0,
  expiry_required TINYINT NOT NULL DEFAULT 0,
  warranty_months INT NOT NULL DEFAULT 0,
  amc_required TINYINT NOT NULL DEFAULT 0,
  brand VARCHAR(120) NULL,
  model VARCHAR(120) NULL,
  barcode VARCHAR(120) NULL,
  reorder_level DECIMAL(12,2) NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX(category_id),
  INDEX(unit_id)
);

CREATE TABLE IF NOT EXISTS tx_headers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tx_type VARCHAR(20) NOT NULL, -- GRN, ISSUE, RETURN, TRANSFER, ADJUST, RTV
  vendor_id INT NULL,
  from_location_id INT NULL,
  to_location_id INT NULL,
  ref_no VARCHAR(80) NULL,
  tx_date DATE NOT NULL,
  created_by INT NOT NULL,
  remarks VARCHAR(255) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX(tx_type),
  INDEX(vendor_id),
  INDEX(from_location_id),
  INDEX(to_location_id)
);

CREATE TABLE IF NOT EXISTS tx_lines (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tx_id INT NOT NULL,
  item_id INT NOT NULL,
  qty DECIMAL(12,2) NOT NULL,
  rate DECIMAL(12,2) NOT NULL DEFAULT 0,
  batch_no VARCHAR(80) NULL,
  expiry_date DATE NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX(tx_id),
  INDEX(item_id)
);

CREATE TABLE IF NOT EXISTS tx_line_serials (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tx_line_id INT NOT NULL,
  serial_no VARCHAR(120) NOT NULL,
  warranty_end DATE NULL,
  amc_end DATE NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_serial(serial_no),
  INDEX(tx_line_id)
);

CREATE TABLE IF NOT EXISTS stock_ledger (
  id INT AUTO_INCREMENT PRIMARY KEY,
  item_id INT NOT NULL,
  location_id INT NOT NULL,
  tx_type VARCHAR(20) NOT NULL,
  tx_id INT NOT NULL,
  qty_in DECIMAL(12,2) NOT NULL DEFAULT 0,
  qty_out DECIMAL(12,2) NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX(item_id),
  INDEX(location_id),
  INDEX(tx_type),
  INDEX(tx_id)
);

-- Indent workflow
CREATE TABLE IF NOT EXISTS indents (
  id INT AUTO_INCREMENT PRIMARY KEY,
  requester_id INT NOT NULL,
  from_location_id INT NOT NULL,
  to_location_id INT NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
  remarks VARCHAR(255) NULL,
  approved_by INT NULL,
  approved_at DATETIME NULL,
  issued_tx_id INT NULL,
  issued_at DATETIME NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX(requester_id),
  INDEX(status)
);

CREATE TABLE IF NOT EXISTS indent_lines (
  id INT AUTO_INCREMENT PRIMARY KEY,
  indent_id INT NOT NULL,
  item_id INT NOT NULL,
  qty DECIMAL(12,2) NOT NULL,
  INDEX(indent_id),
  INDEX(item_id)
);

CREATE TABLE IF NOT EXISTS indent_actions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  indent_id INT NOT NULL,
  action VARCHAR(30) NOT NULL,
  action_by INT NULL,
  note VARCHAR(255) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX(indent_id)
);

-- Asset assignments
CREATE TABLE IF NOT EXISTS asset_assignments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  serial_no VARCHAR(120) NOT NULL,
  assigned_to VARCHAR(160) NOT NULL,
  room VARCHAR(160) NULL,
  location_id INT NOT NULL,
  note VARCHAR(255) NULL,
  assigned_by INT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  end_at DATETIME NULL,
  INDEX(serial_no),
  INDEX(location_id),
  INDEX(assigned_by)
);


CREATE TABLE IF NOT EXISTS assets (
  id INT AUTO_INCREMENT PRIMARY KEY,
  asset_tag VARCHAR(80) NOT NULL UNIQUE,
  item_id INT NOT NULL,
  serial_no VARCHAR(120) NULL,
  purchase_tx_id INT NULL,
  purchase_date DATE NULL,
  purchase_value DECIMAL(12,2) NOT NULL DEFAULT 0,
  vendor_id INT NULL,

  depreciation_method VARCHAR(30) NOT NULL DEFAULT 'SL',
  useful_life_years INT NOT NULL DEFAULT 5,
  salvage_value DECIMAL(12,2) NOT NULL DEFAULT 0,

  current_location_id INT NULL,
  current_assigned_to VARCHAR(160) NULL,
  current_room VARCHAR(160) NULL,

  status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
  disposal_mode VARCHAR(30) NULL,
  disposal_date DATE NULL,
  disposal_value DECIMAL(12,2) NULL,
  disposal_note VARCHAR(255) NULL,
  disposal_tx_id INT NULL,

  notes VARCHAR(255) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX(item_id),
  INDEX(serial_no),
  INDEX(purchase_tx_id),
  INDEX(disposal_tx_id),
  INDEX(status)
);


CREATE TABLE IF NOT EXISTS asset_transfers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  asset_id INT NOT NULL,
  from_location_id INT NULL,
  to_location_id INT NULL,
  from_assigned_to VARCHAR(160) NULL,
  to_assigned_to VARCHAR(160) NULL,
  from_room VARCHAR(160) NULL,
  to_room VARCHAR(160) NULL,
  transfer_date DATE NOT NULL,
  note VARCHAR(255) NULL,
  transferred_by INT NOT NULL,
  stock_tx_id INT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX(asset_id),
  INDEX(stock_tx_id),
  INDEX(transfer_date)
);


CREATE TABLE IF NOT EXISTS asset_disposals (
  id INT AUTO_INCREMENT PRIMARY KEY,
  asset_id INT NOT NULL,
  request_date DATE NOT NULL,
  disposal_mode VARCHAR(30) NOT NULL,
  disposal_date DATE NOT NULL,
  disposal_value DECIMAL(12,2) NULL,
  disposal_note VARCHAR(255) NULL,
  committee_file VARCHAR(255) NULL,
  post_adjust TINYINT NOT NULL DEFAULT 1,
  status VARCHAR(20) NOT NULL DEFAULT 'PENDING', -- PENDING/APPROVED/REJECTED
  requested_by INT NOT NULL,
  approved_by INT NULL,
  approved_at DATETIME NULL,
  approval_note VARCHAR(255) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX(asset_id),
  INDEX(status),
  INDEX(request_date)
);


-- Seeds
INSERT INTO units(name) VALUES ('pcs'),('kg'),('ltr') ON DUPLICATE KEY UPDATE name=name;

INSERT INTO categories(name) VALUES
('Office & Stationery'),
('IT & Computer'),
('Lab Equipment'),
('Sports'),
('Furniture'),
('Electrical'),
('Plumbing'),
('Medical/First Aid')
ON DUPLICATE KEY UPDATE name=name;

INSERT INTO vendors(name, gstin, contact, address) VALUES
('Default Vendor', NULL, NULL, NULL)
ON DUPLICATE KEY UPDATE name=name;

INSERT INTO locations(name, location_type) VALUES
('Main Store', 'Store'),
('Computer Lab', 'Lab'),
('Physics Lab', 'Lab'),
('Admin Office', 'Department')
ON DUPLICATE KEY UPDATE name=name;

-- Default password: Admin@123 (hash may be auto-repaired on login)
INSERT INTO users(full_name,email,password_hash,role,is_active) VALUES
('StockHub Admin','admin@stockhub.local','$2y$10$wGROUQtnkmk2yJQG6xCGxOJf9fD90B/9.0xw2gN7k6Ff6uVQW9e5G','Admin',1),
('Store Keeper','store@stockhub.local','$2y$10$wGROUQtnkmk2yJQG6xCGxOJf9fD90B/9.0xw2gN7k6Ff6uVQW9e5G','Storekeeper',1),
('Department Incharge','hod@stockhub.local','$2y$10$wGROUQtnkmk2yJQG6xCGxOJf9fD90B/9.0xw2gN7k6Ff6uVQW9e5G','Department Incharge',1),
('Staff Requester','staff@stockhub.local','$2y$10$wGROUQtnkmk2yJQG6xCGxOJf9fD90B/9.0xw2gN7k6Ff6uVQW9e5G','Staff/Requester',1),
('Auditor','auditor@stockhub.local','$2y$10$wGROUQtnkmk2yJQG6xCGxOJf9fD90B/9.0xw2gN7k6Ff6uVQW9e5G','Auditor',1)
ON DUPLICATE KEY UPDATE email=email;