-- Modernized Stock Management Schema
-- Multi-branch, audit-trailed, hashed passwords

SET FOREIGN_KEY_CHECKS=0;

CREATE TABLE branches (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  location VARCHAR(255) DEFAULT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  branch_id INT DEFAULT NULL,               -- NULL = sees all branches
  username VARCHAR(100) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  fullname VARCHAR(150) NOT NULL,
  is_admin TINYINT(1) NOT NULL DEFAULT 0,   -- admins always have full access, not permission-gated
  phone VARCHAR(30) DEFAULT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- The fixed catalogue of things a user could be allowed to do/see.
-- This drives both the menu (frontend hides what you can't use) and the
-- API (backend rejects requests for anything you're not granted).
CREATE TABLE permissions (
  `key` VARCHAR(50) PRIMARY KEY,
  label VARCHAR(100) NOT NULL,
  menu_order INT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO permissions (`key`, label, menu_order) VALUES
  ('dashboard', 'Dashboard', 1),
  ('new_sale', 'New Sale', 2),
  ('sales_report', 'Sales Report', 3),
  ('payments_received', 'Payments Received', 4),
  ('inventory', 'Inventory', 5),
  ('profit_loss', 'Profit & Loss', 6),
  ('expenses', 'Expenses', 7),
  ('payroll', 'Payroll', 8),
  ('customers', 'Customers', 9),
  ('suppliers', 'Suppliers', 10),
  ('branches', 'Branches', 11),
  ('staff', 'Staff', 12);

-- Which permissions a specific (non-admin) user has been granted.
CREATE TABLE user_permissions (
  user_id INT NOT NULL,
  permission_key VARCHAR(50) NOT NULL,
  PRIMARY KEY (user_id, permission_key),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (permission_key) REFERENCES permissions(`key`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE suppliers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  phone VARCHAR(30) DEFAULT NULL,
  address VARCHAR(255) DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  product_code VARCHAR(100) NOT NULL UNIQUE,
  name VARCHAR(200) NOT NULL,
  category_id INT DEFAULT NULL,
  supplier_id INT DEFAULT NULL,
  cost_price DECIMAL(12,2) NOT NULL DEFAULT 0,
  selling_price DECIMAL(12,2) NOT NULL DEFAULT 0,
  low_stock_threshold INT NOT NULL DEFAULT 5,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL,
  FOREIGN KEY (supplier_id) REFERENCES suppliers(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Stock is per-branch, not shared
CREATE TABLE branch_stock (
  id INT AUTO_INCREMENT PRIMARY KEY,
  branch_id INT NOT NULL,
  product_id INT NOT NULL,
  quantity INT NOT NULL DEFAULT 0,
  UNIQUE KEY uniq_branch_product (branch_id, product_id),
  FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Every stock change is logged here (audit trail) rather than just overwriting a number
CREATE TABLE stock_movements (
  id INT AUTO_INCREMENT PRIMARY KEY,
  branch_id INT NOT NULL,
  product_id INT NOT NULL,
  user_id INT DEFAULT NULL,
  change_qty INT NOT NULL,                  -- positive = stock in, negative = stock out
  reason ENUM('purchase','sale','transfer_in','transfer_out','adjustment') NOT NULL,
  reference VARCHAR(100) DEFAULT NULL,       -- e.g. invoice number, transfer id
  client_uuid VARCHAR(64) DEFAULT NULL,      -- for offline dedup (see sync notes)
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
  UNIQUE KEY uniq_client_uuid (client_uuid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE customers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  branch_id INT DEFAULT NULL,
  fullname VARCHAR(150) NOT NULL,
  phone VARCHAR(30) DEFAULT NULL,
  address VARCHAR(255) DEFAULT NULL,
  balance DECIMAL(12,2) NOT NULL DEFAULT 0,  -- negative = customer owes you
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE sales (
  id INT AUTO_INCREMENT PRIMARY KEY,
  branch_id INT NOT NULL,
  customer_id INT DEFAULT NULL,
  user_id INT DEFAULT NULL,
  invoice_number VARCHAR(100) NOT NULL,
  total_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  amount_paid DECIMAL(12,2) NOT NULL DEFAULT 0,
  payment_type ENUM('cash','pos','transfer','credit') NOT NULL DEFAULT 'cash',
  client_uuid VARCHAR(64) DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE CASCADE,
  FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
  UNIQUE KEY uniq_client_uuid (client_uuid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE sale_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  sale_id INT NOT NULL,
  product_id INT NOT NULL,
  qty DECIMAL(10,2) NOT NULL,
  unit_price DECIMAL(12,2) NOT NULL,
  line_total DECIMAL(12,2) NOT NULL,
  FOREIGN KEY (sale_id) REFERENCES sales(id) ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS=1;

-- Seed: default branches carried over from the old data (South West region reps)
INSERT INTO branches (name, location) VALUES ('Main Branch', 'Osogbo, Osun State');

-- No default admin user is seeded here on purpose (a hard-coded password hash
-- in a public schema file is exactly the kind of thing that got the old app
-- compromised). Run api/install.php once after import to create the first
-- owner account with a properly generated password hash, then delete that file.