-- TIJARA MySQL schema (Namecheap)
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS companies (
  id VARCHAR(32) PRIMARY KEY,
  trade_name VARCHAR(190) NOT NULL,
  legal_name VARCHAR(190) NOT NULL,
  ice VARCHAR(64) NULL,
  if_number VARCHAR(64) NULL,
  rc VARCHAR(64) NULL,
  address TEXT NULL,
  city VARCHAR(100) NULL,
  phone VARCHAR(50) NULL,
  email VARCHAR(190) NULL,
  bank_name VARCHAR(190) NULL,
  rib VARCHAR(100) NULL,
  payment_terms VARCHAR(100) DEFAULT '30 jours',
  default_vat_rate DECIMAL(5,2) DEFAULT 20,
  currency VARCHAR(10) DEFAULT 'MAD',
  legal_mentions TEXT NULL,
  created_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS users (
  id VARCHAR(32) PRIMARY KEY,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  first_name VARCHAR(100) NOT NULL,
  last_name VARCHAR(100) NOT NULL,
  role VARCHAR(30) DEFAULT 'ADMIN',
  active TINYINT(1) DEFAULT 1,
  company_id VARCHAR(32) NOT NULL,
  created_at DATETIME NOT NULL,
  FOREIGN KEY (company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS customers (
  id VARCHAR(32) PRIMARY KEY,
  type VARCHAR(30) DEFAULT 'ENTREPRISE',
  first_name VARCHAR(100) NULL,
  last_name VARCHAR(100) NULL,
  company_name VARCHAR(190) NULL,
  ice VARCHAR(64) NULL,
  address TEXT NULL,
  city VARCHAR(100) NULL,
  phone VARCHAR(50) NULL,
  email VARCHAR(190) NULL,
  payment_terms VARCHAR(100) DEFAULT '30 jours',
  archived TINYINT(1) DEFAULT 0,
  company_id VARCHAR(32) NOT NULL,
  created_at DATETIME NOT NULL,
  FOREIGN KEY (company_id) REFERENCES companies(id),
  INDEX (company_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS suppliers (
  id VARCHAR(32) PRIMARY KEY,
  company_name VARCHAR(190) NOT NULL,
  ice VARCHAR(64) NULL,
  city VARCHAR(100) NULL,
  phone VARCHAR(50) NULL,
  email VARCHAR(190) NULL,
  archived TINYINT(1) DEFAULT 0,
  company_id VARCHAR(32) NOT NULL,
  created_at DATETIME NOT NULL,
  FOREIGN KEY (company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS categories (
  id VARCHAR(32) PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  company_id VARCHAR(32) NOT NULL,
  UNIQUE KEY uq_cat (company_id, name),
  FOREIGN KEY (company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS products (
  id VARCHAR(32) PRIMARY KEY,
  reference VARCHAR(64) NOT NULL,
  name VARCHAR(190) NOT NULL,
  unit VARCHAR(30) DEFAULT 'unité',
  purchase_price DECIMAL(12,2) DEFAULT 0,
  sale_price DECIMAL(12,2) NOT NULL,
  vat_rate DECIMAL(5,2) DEFAULT 20,
  stock DECIMAL(12,2) DEFAULT 0,
  alert_threshold DECIMAL(12,2) NULL,
  track_stock TINYINT(1) DEFAULT 1,
  is_service TINYINT(1) DEFAULT 0,
  active TINYINT(1) DEFAULT 1,
  category_id VARCHAR(32) NULL,
  company_id VARCHAR(32) NOT NULL,
  created_at DATETIME NOT NULL,
  UNIQUE KEY uq_ref (company_id, reference),
  FOREIGN KEY (company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS quotes (
  id VARCHAR(32) PRIMARY KEY,
  number VARCHAR(40) NOT NULL,
  date DATE NOT NULL,
  valid_until DATE NULL,
  object VARCHAR(255) NULL,
  status VARCHAR(30) DEFAULT 'BROUILLON',
  payment_terms VARCHAR(100) NULL,
  total_ht DECIMAL(12,2) DEFAULT 0,
  total_vat DECIMAL(12,2) DEFAULT 0,
  total_ttc DECIMAL(12,2) DEFAULT 0,
  notes TEXT NULL,
  signature_data MEDIUMTEXT NULL,
  signed_at DATETIME NULL,
  signed_by VARCHAR(32) NULL,
  signed_ip VARCHAR(64) NULL,
  customer_id VARCHAR(32) NOT NULL,
  company_id VARCHAR(32) NOT NULL,
  created_at DATETIME NOT NULL,
  UNIQUE KEY uq_q (company_id, number),
  FOREIGN KEY (customer_id) REFERENCES customers(id),
  FOREIGN KEY (company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS quote_items (
  id VARCHAR(32) PRIMARY KEY,
  quote_id VARCHAR(32) NOT NULL,
  product_id VARCHAR(32) NULL,
  reference VARCHAR(64) NULL,
  designation VARCHAR(255) NOT NULL,
  quantity DECIMAL(12,2) DEFAULT 1,
  unit_price DECIMAL(12,2) NOT NULL,
  discount DECIMAL(12,2) DEFAULT 0,
  vat_rate DECIMAL(5,2) DEFAULT 20,
  total_ht DECIMAL(12,2) NOT NULL,
  sort_order INT DEFAULT 0,
  FOREIGN KEY (quote_id) REFERENCES quotes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS orders (
  id VARCHAR(32) PRIMARY KEY,
  number VARCHAR(40) NOT NULL,
  date DATE NOT NULL,
  status VARCHAR(30) DEFAULT 'BROUILLON',
  total_ht DECIMAL(12,2) DEFAULT 0,
  total_vat DECIMAL(12,2) DEFAULT 0,
  total_ttc DECIMAL(12,2) DEFAULT 0,
  customer_id VARCHAR(32) NOT NULL,
  quote_id VARCHAR(32) NULL,
  company_id VARCHAR(32) NOT NULL,
  created_at DATETIME NOT NULL,
  UNIQUE KEY uq_o (company_id, number),
  FOREIGN KEY (customer_id) REFERENCES customers(id),
  FOREIGN KEY (company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS order_items (
  id VARCHAR(32) PRIMARY KEY,
  order_id VARCHAR(32) NOT NULL,
  product_id VARCHAR(32) NULL,
  reference VARCHAR(64) NULL,
  designation VARCHAR(255) NOT NULL,
  quantity DECIMAL(12,2) DEFAULT 1,
  unit_price DECIMAL(12,2) NOT NULL,
  discount DECIMAL(12,2) DEFAULT 0,
  vat_rate DECIMAL(5,2) DEFAULT 20,
  total_ht DECIMAL(12,2) NOT NULL,
  sort_order INT DEFAULT 0,
  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS delivery_notes (
  id VARCHAR(32) PRIMARY KEY,
  number VARCHAR(40) NOT NULL,
  date DATE NOT NULL,
  status VARCHAR(30) DEFAULT 'PREPARE',
  delivery_address TEXT NULL,
  receiver_name VARCHAR(190) NULL,
  signature_data MEDIUMTEXT NULL,
  signed_at DATETIME NULL,
  signed_by VARCHAR(32) NULL,
  signed_ip VARCHAR(64) NULL,
  customer_id VARCHAR(32) NOT NULL,
  order_id VARCHAR(32) NULL,
  company_id VARCHAR(32) NOT NULL,
  created_at DATETIME NOT NULL,
  UNIQUE KEY uq_bl (company_id, number),
  FOREIGN KEY (customer_id) REFERENCES customers(id),
  FOREIGN KEY (company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS delivery_note_items (
  id VARCHAR(32) PRIMARY KEY,
  delivery_note_id VARCHAR(32) NOT NULL,
  product_id VARCHAR(32) NULL,
  designation VARCHAR(255) NOT NULL,
  quantity_ordered DECIMAL(12,2) DEFAULT 0,
  quantity_delivered DECIMAL(12,2) DEFAULT 0,
  FOREIGN KEY (delivery_note_id) REFERENCES delivery_notes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS invoices (
  id VARCHAR(32) PRIMARY KEY,
  number VARCHAR(40) NOT NULL,
  date DATE NOT NULL,
  due_date DATE NULL,
  status VARCHAR(30) DEFAULT 'EMISE',
  payment_terms VARCHAR(100) NULL,
  total_ht DECIMAL(12,2) DEFAULT 0,
  total_vat DECIMAL(12,2) DEFAULT 0,
  total_ttc DECIMAL(12,2) DEFAULT 0,
  amount_paid DECIMAL(12,2) DEFAULT 0,
  amount_due DECIMAL(12,2) DEFAULT 0,
  notes TEXT NULL,
  last_reminder_at DATETIME NULL,
  customer_id VARCHAR(32) NOT NULL,
  quote_id VARCHAR(32) NULL,
  order_id VARCHAR(32) NULL,
  company_id VARCHAR(32) NOT NULL,
  created_at DATETIME NOT NULL,
  UNIQUE KEY uq_i (company_id, number),
  FOREIGN KEY (customer_id) REFERENCES customers(id),
  FOREIGN KEY (company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS invoice_items (
  id VARCHAR(32) PRIMARY KEY,
  invoice_id VARCHAR(32) NOT NULL,
  product_id VARCHAR(32) NULL,
  reference VARCHAR(64) NULL,
  designation VARCHAR(255) NOT NULL,
  quantity DECIMAL(12,2) DEFAULT 1,
  unit_price DECIMAL(12,2) NOT NULL,
  discount DECIMAL(12,2) DEFAULT 0,
  vat_rate DECIMAL(5,2) DEFAULT 20,
  total_ht DECIMAL(12,2) NOT NULL,
  sort_order INT DEFAULT 0,
  FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS credit_notes (
  id VARCHAR(32) PRIMARY KEY,
  number VARCHAR(40) NOT NULL,
  date DATE NOT NULL,
  reason VARCHAR(255) NULL,
  total_ht DECIMAL(12,2) DEFAULT 0,
  total_vat DECIMAL(12,2) DEFAULT 0,
  total_ttc DECIMAL(12,2) DEFAULT 0,
  status VARCHAR(30) DEFAULT 'EMIS',
  invoice_id VARCHAR(32) NULL,
  customer_id VARCHAR(32) NOT NULL,
  company_id VARCHAR(32) NOT NULL,
  created_at DATETIME NOT NULL,
  UNIQUE KEY uq_av (company_id, number),
  FOREIGN KEY (company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS payments (
  id VARCHAR(32) PRIMARY KEY,
  number VARCHAR(40) NOT NULL,
  date DATE NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  method VARCHAR(30) NOT NULL,
  reference VARCHAR(100) NULL,
  invoice_id VARCHAR(32) NOT NULL,
  customer_id VARCHAR(32) NOT NULL,
  company_id VARCHAR(32) NOT NULL,
  created_at DATETIME NOT NULL,
  UNIQUE KEY uq_p (company_id, number),
  FOREIGN KEY (invoice_id) REFERENCES invoices(id),
  FOREIGN KEY (company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS expenses (
  id VARCHAR(32) PRIMARY KEY,
  number VARCHAR(40) NOT NULL,
  date DATE NOT NULL,
  description VARCHAR(255) NOT NULL,
  amount_ht DECIMAL(12,2) NOT NULL,
  vat_rate DECIMAL(5,2) DEFAULT 20,
  amount_vat DECIMAL(12,2) NOT NULL,
  amount_ttc DECIMAL(12,2) NOT NULL,
  method VARCHAR(30) NULL,
  category VARCHAR(100) NULL,
  company_id VARCHAR(32) NOT NULL,
  created_at DATETIME NOT NULL,
  UNIQUE KEY uq_e (company_id, number),
  FOREIGN KEY (company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS stock_movements (
  id VARCHAR(32) PRIMARY KEY,
  type VARCHAR(30) NOT NULL,
  quantity DECIMAL(12,2) NOT NULL,
  reason VARCHAR(255) NULL,
  product_id VARCHAR(32) NOT NULL,
  company_id VARCHAR(32) NOT NULL,
  created_at DATETIME NOT NULL,
  FOREIGN KEY (product_id) REFERENCES products(id),
  FOREIGN KEY (company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS cash_accounts (
  id VARCHAR(32) PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  initial_balance DECIMAL(12,2) DEFAULT 0,
  company_id VARCHAR(32) NOT NULL,
  created_at DATETIME NOT NULL,
  FOREIGN KEY (company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS cash_transactions (
  id VARCHAR(32) PRIMARY KEY,
  type VARCHAR(20) NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  label VARCHAR(255) NOT NULL,
  date DATE NOT NULL,
  cash_account_id VARCHAR(32) NOT NULL,
  created_at DATETIME NOT NULL,
  FOREIGN KEY (cash_account_id) REFERENCES cash_accounts(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS bank_accounts (
  id VARCHAR(32) PRIMARY KEY,
  bank_name VARCHAR(100) NOT NULL,
  rib VARCHAR(100) NULL,
  initial_balance DECIMAL(12,2) DEFAULT 0,
  company_id VARCHAR(32) NOT NULL,
  created_at DATETIME NOT NULL,
  FOREIGN KEY (company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS bank_transactions (
  id VARCHAR(32) PRIMARY KEY,
  type VARCHAR(30) NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  label VARCHAR(255) NOT NULL,
  date DATE NOT NULL,
  bank_account_id VARCHAR(32) NOT NULL,
  created_at DATETIME NOT NULL,
  FOREIGN KEY (bank_account_id) REFERENCES bank_accounts(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS branches (
  id VARCHAR(32) PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  city VARCHAR(100) NULL,
  company_id VARCHAR(32) NOT NULL,
  active TINYINT(1) DEFAULT 1,
  created_at DATETIME NOT NULL,
  FOREIGN KEY (company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS subscriptions (
  id VARCHAR(32) PRIMARY KEY,
  name VARCHAR(190) NOT NULL,
  frequency VARCHAR(30) NOT NULL,
  amount_ht DECIMAL(12,2) NOT NULL,
  vat_rate DECIMAL(5,2) DEFAULT 20,
  start_date DATE NOT NULL,
  next_billing DATE NOT NULL,
  active TINYINT(1) DEFAULT 1,
  customer_id VARCHAR(32) NOT NULL,
  company_id VARCHAR(32) NOT NULL,
  created_at DATETIME NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(id),
  FOREIGN KEY (company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS notifications (
  id VARCHAR(32) PRIMARY KEY,
  title VARCHAR(190) NOT NULL,
  message TEXT NOT NULL,
  type VARCHAR(30) DEFAULT 'INFO',
  is_read TINYINT(1) DEFAULT 0,
  link VARCHAR(255) NULL,
  company_id VARCHAR(32) NOT NULL,
  created_at DATETIME NOT NULL,
  FOREIGN KEY (company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS document_counters (
  id VARCHAR(32) PRIMARY KEY,
  company_id VARCHAR(32) NOT NULL,
  type VARCHAR(30) NOT NULL,
  year INT NOT NULL,
  value INT DEFAULT 0,
  UNIQUE KEY uq_c (company_id, type, year),
  FOREIGN KEY (company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS activity_logs (
  id VARCHAR(32) PRIMARY KEY,
  company_id VARCHAR(32) NOT NULL,
  user_id VARCHAR(32) NULL,
  action VARCHAR(255) NOT NULL,
  entity VARCHAR(50) NULL,
  entity_id VARCHAR(32) NULL,
  created_at DATETIME NOT NULL,
  FOREIGN KEY (company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


CREATE TABLE IF NOT EXISTS attachments (
  id VARCHAR(32) PRIMARY KEY,
  company_id VARCHAR(32) NOT NULL,
  entity_type VARCHAR(50) NOT NULL,
  entity_id VARCHAR(32) NOT NULL,
  filename VARCHAR(255) NOT NULL,
  path VARCHAR(255) NOT NULL,
  mime VARCHAR(100) NULL,
  size INT DEFAULT 0,
  created_at DATETIME NOT NULL,
  FOREIGN KEY (company_id) REFERENCES companies(id),
  INDEX (company_id, entity_type, entity_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS user_companies (
  user_id VARCHAR(32) NOT NULL,
  company_id VARCHAR(32) NOT NULL,
  role VARCHAR(30) DEFAULT 'ADMIN',
  PRIMARY KEY (user_id, company_id),
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS credit_note_items (
  id VARCHAR(32) PRIMARY KEY,
  credit_note_id VARCHAR(32) NOT NULL,
  product_id VARCHAR(32) NULL,
  reference VARCHAR(64) NULL,
  designation VARCHAR(255) NOT NULL,
  quantity DECIMAL(12,2) DEFAULT 1,
  unit_price DECIMAL(12,2) NOT NULL,
  discount DECIMAL(12,2) DEFAULT 0,
  vat_rate DECIMAL(5,2) DEFAULT 20,
  total_ht DECIMAL(12,2) NOT NULL,
  sort_order INT DEFAULT 0,
  FOREIGN KEY (credit_note_id) REFERENCES credit_notes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;