PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS app_state_kv (
  key TEXT PRIMARY KEY,
  value_json TEXT NOT NULL,
  updated_at TEXT DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS users (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  username TEXT UNIQUE NOT NULL,
  password TEXT NOT NULL,
  role TEXT NOT NULL,
  location TEXT DEFAULT 'Main Store — Branch 1',
  salary REAL DEFAULT 0,
  comm_pct REAL DEFAULT 0,
  active INTEGER DEFAULT 1,
  last_login TEXT DEFAULT 'Never',
  perms_json TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS categories (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT UNIQUE NOT NULL
);

CREATE TABLE IF NOT EXISTS products (
  id TEXT PRIMARY KEY,
  sku TEXT UNIQUE NOT NULL,
  brand TEXT DEFAULT 'Standard',
  prod_type TEXT DEFAULT 'Single',
  unit TEXT DEFAULT 'Pcs',
  name TEXT NOT NULL,
  category TEXT NOT NULL,
  price REAL NOT NULL,
  cost REAL NOT NULL,
  stock INTEGER DEFAULT 0,
  reorder INTEGER DEFAULT 5,
  manage_stock INTEGER DEFAULT 1,
  pct_code TEXT DEFAULT '99990000',
  imei_lot TEXT DEFAULT '',
  expiry TEXT DEFAULT '',
  tax_rate REAL DEFAULT 18,
  pair_with TEXT DEFAULT ''
);

CREATE TABLE IF NOT EXISTS contacts (
  id TEXT PRIMARY KEY,
  contact_type TEXT NOT NULL CHECK(contact_type IN ('customer', 'supplier', 'both')),
  name TEXT NOT NULL,
  phone TEXT DEFAULT '',
  cnic TEXT DEFAULT '',
  ntn TEXT DEFAULT '',
  due_balance REAL DEFAULT 0,
  credit_limit REAL DEFAULT 0,
  pay_terms INTEGER DEFAULT 0,
  is_walkin INTEGER DEFAULT 0
);

CREATE TABLE IF NOT EXISTS contact_ledger (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  contact_id TEXT NOT NULL,
  entry_date TEXT NOT NULL,
  entry_type TEXT NOT NULL,
  ref_no TEXT NOT NULL,
  debit REAL DEFAULT 0,
  credit REAL DEFAULT 0,
  balance REAL DEFAULT 0,
  FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS invoices (
  usin TEXT PRIMARY KEY,
  fbr_invoice_number TEXT NOT NULL,
  date_time TEXT NOT NULL,
  customer_id TEXT,
  buyer_name TEXT NOT NULL,
  buyer_phone TEXT DEFAULT '',
  buyer_cnic TEXT DEFAULT '',
  buyer_ntn TEXT DEFAULT '',
  agent_id TEXT DEFAULT '',
  payment_mode INTEGER DEFAULT 1,
  invoice_type INTEGER DEFAULT 1,
  ref_usin TEXT DEFAULT '',
  status TEXT DEFAULT 'valid',
  pos_fee REAL DEFAULT 1,
  shipping REAL DEFAULT 0,
  subtotal REAL DEFAULT 0,
  discount_amount REAL DEFAULT 0,
  net_sale_value REAL DEFAULT 0,
  total_tax REAL DEFAULT 0,
  grand_total REAL DEFAULT 0,
  previous_balance REAL DEFAULT 0,
  paid_amount REAL DEFAULT 0,
  change_return REAL DEFAULT 0,
  closing_balance REAL DEFAULT 0,
  total_qty INTEGER DEFAULT 0,
  FOREIGN KEY (customer_id) REFERENCES contacts(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS invoice_items (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  invoice_usin TEXT NOT NULL,
  product_id TEXT,
  sku TEXT NOT NULL,
  name TEXT NOT NULL,
  category TEXT DEFAULT 'General Retail',
  pct_code TEXT DEFAULT '99990000',
  qty INTEGER NOT NULL,
  price REAL NOT NULL,
  cost REAL DEFAULT 0,
  tax_rate REAL DEFAULT 18,
  line_discount REAL DEFAULT 0,
  sale_value REAL NOT NULL,
  tax_amount REAL NOT NULL,
  line_total REAL NOT NULL,
  FOREIGN KEY (invoice_usin) REFERENCES invoices(usin) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS purchases (
  id TEXT PRIMARY KEY,
  purchase_date TEXT NOT NULL,
  supplier_id TEXT,
  supplier_name TEXT NOT NULL,
  product_id TEXT,
  product_name TEXT NOT NULL,
  qty INTEGER NOT NULL,
  unit_cost REAL NOT NULL,
  shipping REAL DEFAULT 0,
  tax_rate REAL DEFAULT 18,
  tax_amount REAL DEFAULT 0,
  total REAL NOT NULL,
  paid REAL DEFAULT 0,
  due REAL DEFAULT 0,
  lot TEXT DEFAULT '',
  expiry TEXT DEFAULT '',
  doc_name TEXT DEFAULT '',
  status TEXT DEFAULT 'paid'
);

CREATE TABLE IF NOT EXISTS bills (
  id TEXT PRIMARY KEY,
  bill_date TEXT NOT NULL,
  party TEXT NOT NULL,
  description TEXT NOT NULL,
  amount REAL NOT NULL,
  tax_rate REAL DEFAULT 18,
  tax_amount REAL DEFAULT 0,
  total REAL NOT NULL,
  due_date TEXT DEFAULT '',
  status TEXT DEFAULT 'Unpaid'
);

CREATE TABLE IF NOT EXISTS quotations (
  id TEXT PRIMARY KEY,
  quote_date TEXT NOT NULL,
  customer_id TEXT,
  customer_name TEXT NOT NULL,
  valid_until TEXT DEFAULT '',
  notes TEXT DEFAULT '',
  cart_json TEXT NOT NULL,
  discount REAL DEFAULT 0,
  total REAL NOT NULL
);

CREATE TABLE IF NOT EXISTS expenses (
  id TEXT PRIMARY KEY,
  expense_date TEXT NOT NULL,
  category TEXT NOT NULL,
  amount REAL NOT NULL,
  note TEXT DEFAULT ''
);

CREATE TABLE IF NOT EXISTS stock_adjustments (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  adj_date TEXT NOT NULL,
  sku TEXT NOT NULL,
  product TEXT NOT NULL,
  delta INTEGER NOT NULL,
  reason TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS standard_template (
  id INTEGER PRIMARY KEY CHECK (id = 1),
  template_json TEXT NOT NULL,
  updated_at TEXT DEFAULT (datetime('now'))
);
