CREATE TABLE IF NOT EXISTS companies (
  id TEXT PRIMARY KEY,
  trade_name TEXT NOT NULL,
  legal_name TEXT NOT NULL,
  ice TEXT,
  if_number TEXT,
  rc TEXT,
  address TEXT,
  city TEXT,
  phone TEXT,
  email TEXT,
  bank_name TEXT,
  rib TEXT,
  payment_terms TEXT DEFAULT '30 jours',
  default_vat_rate REAL DEFAULT 20,
  currency TEXT DEFAULT 'MAD',
  legal_mentions TEXT,
  created_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS users (
  id TEXT PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  password_hash TEXT NOT NULL,
  first_name TEXT NOT NULL,
  last_name TEXT NOT NULL,
  role TEXT DEFAULT 'ADMIN',
  active INTEGER DEFAULT 1,
  company_id TEXT NOT NULL,
  created_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS customers (
  id TEXT PRIMARY KEY,
  type TEXT DEFAULT 'ENTREPRISE',
  first_name TEXT,
  last_name TEXT,
  company_name TEXT,
  ice TEXT,
  address TEXT,
  city TEXT,
  phone TEXT,
  email TEXT,
  payment_terms TEXT DEFAULT '30 jours',
  archived INTEGER DEFAULT 0,
  company_id TEXT NOT NULL,
  created_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS suppliers (
  id TEXT PRIMARY KEY,
  company_name TEXT NOT NULL,
  ice TEXT,
  city TEXT,
  phone TEXT,
  email TEXT,
  archived INTEGER DEFAULT 0,
  company_id TEXT NOT NULL,
  created_at TEXT NOT NULL
);

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

CREATE TABLE IF NOT EXISTS products (
  id TEXT PRIMARY KEY,
  reference TEXT NOT NULL,
  name TEXT NOT NULL,
  unit TEXT DEFAULT 'unité',
  purchase_price REAL DEFAULT 0,
  sale_price REAL NOT NULL,
  vat_rate REAL DEFAULT 20,
  stock REAL DEFAULT 0,
  alert_threshold REAL,
  track_stock INTEGER DEFAULT 1,
  is_service INTEGER DEFAULT 0,
  active INTEGER DEFAULT 1,
  category_id TEXT,
  company_id TEXT NOT NULL,
  created_at TEXT NOT NULL,
  UNIQUE (company_id, reference)
);

CREATE TABLE IF NOT EXISTS quotes (
  id TEXT PRIMARY KEY,
  number TEXT NOT NULL,
  date TEXT NOT NULL,
  valid_until TEXT,
  object TEXT,
  status TEXT DEFAULT 'BROUILLON',
  payment_terms TEXT,
  total_ht REAL DEFAULT 0,
  total_vat REAL DEFAULT 0,
  total_ttc REAL DEFAULT 0,
  notes TEXT,
  signature_data TEXT,
  signed_at TEXT,
  signed_by TEXT,
  signed_ip TEXT,
  customer_id TEXT NOT NULL,
  company_id TEXT NOT NULL,
  created_at TEXT NOT NULL,
  UNIQUE (company_id, number)
);

CREATE TABLE IF NOT EXISTS quote_items (
  id TEXT PRIMARY KEY,
  quote_id TEXT NOT NULL,
  product_id TEXT,
  reference TEXT,
  designation TEXT NOT NULL,
  quantity REAL DEFAULT 1,
  unit_price REAL NOT NULL,
  discount REAL DEFAULT 0,
  vat_rate REAL DEFAULT 20,
  total_ht REAL NOT NULL,
  sort_order INTEGER DEFAULT 0
);

CREATE TABLE IF NOT EXISTS orders (
  id TEXT PRIMARY KEY,
  number TEXT NOT NULL,
  date TEXT NOT NULL,
  status TEXT DEFAULT 'BROUILLON',
  total_ht REAL DEFAULT 0,
  total_vat REAL DEFAULT 0,
  total_ttc REAL DEFAULT 0,
  customer_id TEXT NOT NULL,
  quote_id TEXT,
  company_id TEXT NOT NULL,
  created_at TEXT NOT NULL,
  UNIQUE (company_id, number)
);

CREATE TABLE IF NOT EXISTS order_items (
  id TEXT PRIMARY KEY,
  order_id TEXT NOT NULL,
  product_id TEXT,
  reference TEXT,
  designation TEXT NOT NULL,
  quantity REAL DEFAULT 1,
  unit_price REAL NOT NULL,
  discount REAL DEFAULT 0,
  vat_rate REAL DEFAULT 20,
  total_ht REAL NOT NULL,
  sort_order INTEGER DEFAULT 0
);

CREATE TABLE IF NOT EXISTS delivery_notes (
  id TEXT PRIMARY KEY,
  number TEXT NOT NULL,
  date TEXT NOT NULL,
  status TEXT DEFAULT 'PREPARE',
  delivery_address TEXT,
  receiver_name TEXT,
  signature_data TEXT,
  signed_at TEXT,
  signed_by TEXT,
  signed_ip TEXT,
  customer_id TEXT NOT NULL,
  order_id TEXT,
  company_id TEXT NOT NULL,
  created_at TEXT NOT NULL,
  UNIQUE (company_id, number)
);

CREATE TABLE IF NOT EXISTS delivery_note_items (
  id TEXT PRIMARY KEY,
  delivery_note_id TEXT NOT NULL,
  product_id TEXT,
  designation TEXT NOT NULL,
  quantity_ordered REAL DEFAULT 0,
  quantity_delivered REAL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS invoices (
  id TEXT PRIMARY KEY,
  number TEXT NOT NULL,
  date TEXT NOT NULL,
  due_date TEXT,
  status TEXT DEFAULT 'EMISE',
  payment_terms TEXT,
  total_ht REAL DEFAULT 0,
  total_vat REAL DEFAULT 0,
  total_ttc REAL DEFAULT 0,
  amount_paid REAL DEFAULT 0,
  amount_due REAL DEFAULT 0,
  notes TEXT,
  last_reminder_at TEXT,
  customer_id TEXT NOT NULL,
  quote_id TEXT,
  order_id TEXT,
  company_id TEXT NOT NULL,
  created_at TEXT NOT NULL,
  UNIQUE (company_id, number)
);

CREATE TABLE IF NOT EXISTS invoice_items (
  id TEXT PRIMARY KEY,
  invoice_id TEXT NOT NULL,
  product_id TEXT,
  reference TEXT,
  designation TEXT NOT NULL,
  quantity REAL DEFAULT 1,
  unit_price REAL NOT NULL,
  discount REAL DEFAULT 0,
  vat_rate REAL DEFAULT 20,
  total_ht REAL NOT NULL,
  sort_order INTEGER DEFAULT 0
);

CREATE TABLE IF NOT EXISTS credit_notes (
  id TEXT PRIMARY KEY,
  number TEXT NOT NULL,
  date TEXT NOT NULL,
  reason TEXT,
  total_ht REAL DEFAULT 0,
  total_vat REAL DEFAULT 0,
  total_ttc REAL DEFAULT 0,
  status TEXT DEFAULT 'EMIS',
  invoice_id TEXT,
  customer_id TEXT NOT NULL,
  company_id TEXT NOT NULL,
  created_at TEXT NOT NULL,
  UNIQUE (company_id, number)
);

CREATE TABLE IF NOT EXISTS payments (
  id TEXT PRIMARY KEY,
  number TEXT NOT NULL,
  date TEXT NOT NULL,
  amount REAL NOT NULL,
  method TEXT NOT NULL,
  reference TEXT,
  invoice_id TEXT NOT NULL,
  customer_id TEXT NOT NULL,
  company_id TEXT NOT NULL,
  created_at TEXT NOT NULL,
  UNIQUE (company_id, number)
);

CREATE TABLE IF NOT EXISTS expenses (
  id TEXT PRIMARY KEY,
  number TEXT NOT NULL,
  date TEXT NOT NULL,
  description TEXT NOT NULL,
  amount_ht REAL NOT NULL,
  vat_rate REAL DEFAULT 20,
  amount_vat REAL NOT NULL,
  amount_ttc REAL NOT NULL,
  method TEXT,
  category TEXT,
  company_id TEXT NOT NULL,
  created_at TEXT NOT NULL,
  UNIQUE (company_id, number)
);

CREATE TABLE IF NOT EXISTS stock_movements (
  id TEXT PRIMARY KEY,
  type TEXT NOT NULL,
  quantity REAL NOT NULL,
  reason TEXT,
  product_id TEXT NOT NULL,
  company_id TEXT NOT NULL,
  created_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS cash_accounts (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  initial_balance REAL DEFAULT 0,
  company_id TEXT NOT NULL,
  created_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS cash_transactions (
  id TEXT PRIMARY KEY,
  type TEXT NOT NULL,
  amount REAL NOT NULL,
  label TEXT NOT NULL,
  date TEXT NOT NULL,
  cash_account_id TEXT NOT NULL,
  created_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS bank_accounts (
  id TEXT PRIMARY KEY,
  bank_name TEXT NOT NULL,
  rib TEXT,
  initial_balance REAL DEFAULT 0,
  company_id TEXT NOT NULL,
  created_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS bank_transactions (
  id TEXT PRIMARY KEY,
  type TEXT NOT NULL,
  amount REAL NOT NULL,
  label TEXT NOT NULL,
  date TEXT NOT NULL,
  bank_account_id TEXT NOT NULL,
  created_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS branches (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  company_id TEXT NOT NULL,
  active INTEGER DEFAULT 1,
  created_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS subscriptions (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  frequency TEXT NOT NULL,
  amount_ht REAL NOT NULL,
  vat_rate REAL DEFAULT 20,
  start_date TEXT NOT NULL,
  next_billing TEXT NOT NULL,
  active INTEGER DEFAULT 1,
  customer_id TEXT NOT NULL,
  company_id TEXT NOT NULL,
  created_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS notifications (
  id TEXT PRIMARY KEY,
  title TEXT NOT NULL,
  message TEXT NOT NULL,
  type TEXT DEFAULT 'INFO',
  is_read INTEGER DEFAULT 0,
  link TEXT,
  company_id TEXT NOT NULL,
  created_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS document_counters (
  id TEXT PRIMARY KEY,
  company_id TEXT NOT NULL,
  type TEXT NOT NULL,
  year INTEGER NOT NULL,
  value INTEGER DEFAULT 0,
  UNIQUE (company_id, type, year)
);

CREATE TABLE IF NOT EXISTS activity_logs (
  id TEXT PRIMARY KEY,
  company_id TEXT NOT NULL,
  user_id TEXT,
  action TEXT NOT NULL,
  entity TEXT,
  entity_id TEXT,
  created_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS attachments (
  id TEXT PRIMARY KEY,
  company_id TEXT NOT NULL,
  entity_type TEXT NOT NULL,
  entity_id TEXT NOT NULL,
  filename TEXT NOT NULL,
  path TEXT NOT NULL,
  mime TEXT,
  size INTEGER DEFAULT 0,
  created_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS user_companies (
  user_id TEXT NOT NULL,
  company_id TEXT NOT NULL,
  role TEXT DEFAULT 'ADMIN',
  PRIMARY KEY (user_id, company_id)
);

CREATE TABLE IF NOT EXISTS credit_note_items (
  id TEXT PRIMARY KEY,
  credit_note_id TEXT NOT NULL,
  product_id TEXT,
  reference TEXT,
  designation TEXT NOT NULL,
  quantity REAL DEFAULT 1,
  unit_price REAL NOT NULL,
  discount REAL DEFAULT 0,
  vat_rate REAL DEFAULT 20,
  total_ht REAL NOT NULL,
  sort_order INTEGER DEFAULT 0
);