-- ERP modulaire / SaaS / exercices (SQLite) — additive
CREATE TABLE IF NOT EXISTS company_modules (
  id VARCHAR(32) PRIMARY KEY,
  company_id VARCHAR(255) NOT NULL,
  module_key VARCHAR(255) NOT NULL,
  enabled INT DEFAULT 1,
  updated_at TEXT,
  UNIQUE (company_id, module_key)
);

CREATE TABLE IF NOT EXISTS saas_plans (
  id VARCHAR(32) PRIMARY KEY,
  code VARCHAR(64) NOT NULL UNIQUE,
  name VARCHAR(255) NOT NULL,
  max_users INT DEFAULT 5,
  max_companies INT DEFAULT 1,
  max_storage_mb INT DEFAULT 1024,
  modules_json TEXT,
  price_monthly DOUBLE DEFAULT 0,
  active INT DEFAULT 1,
  created_at VARCHAR(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS company_subscriptions (
  id VARCHAR(32) PRIMARY KEY,
  company_id VARCHAR(255) NOT NULL,
  plan_id VARCHAR(255) NOT NULL,
  status VARCHAR(100) DEFAULT 'ACTIVE',
  starts_at TEXT,
  ends_at TEXT,
  created_at VARCHAR(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS user_branches (
  user_id VARCHAR(255) NOT NULL,
  branch_id VARCHAR(255) NOT NULL,
  company_id VARCHAR(255) NOT NULL,
  PRIMARY KEY (user_id, branch_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS fiscal_years (
  id VARCHAR(32) PRIMARY KEY,
  company_id VARCHAR(255) NOT NULL,
  label VARCHAR(255) NOT NULL,
  year INTEGER NOT NULL,
  starts_on VARCHAR(255) NOT NULL,
  ends_on VARCHAR(255) NOT NULL,
  is_current INT DEFAULT 0,
  closed INT DEFAULT 0,
  UNIQUE (company_id, year)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS numbering_rules (
  id VARCHAR(32) PRIMARY KEY,
  company_id VARCHAR(255) NOT NULL,
  doc_type VARCHAR(255) NOT NULL,
  prefix VARCHAR(255) NOT NULL,
  pad INT DEFAULT 5,
  include_year INT DEFAULT 1,
  UNIQUE (company_id, doc_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS contracts (
  id VARCHAR(32) PRIMARY KEY,
  company_id VARCHAR(255) NOT NULL,
  number VARCHAR(255) NOT NULL,
  contract_type VARCHAR(100) DEFAULT 'AUTRE',
  title TEXT,
  party_type VARCHAR(100) DEFAULT 'CLIENT',
  customer_id TEXT,
  supplier_id TEXT,
  driver_id TEXT,
  vehicle_id TEXT,
  start_date TEXT,
  end_date TEXT,
  amount DOUBLE DEFAULT 0,
  frequency TEXT,
  auto_renew INT DEFAULT 0,
  status VARCHAR(100) DEFAULT 'ACTIF',
  document_path TEXT,
  notes TEXT,
  created_by TEXT,
  created_at VARCHAR(255) NOT NULL,
  UNIQUE (company_id, number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ai_queries (
  id VARCHAR(32) PRIMARY KEY,
  company_id VARCHAR(255) NOT NULL,
  user_id VARCHAR(255) NOT NULL,
  question VARCHAR(255) NOT NULL,
  answer TEXT,
  created_at VARCHAR(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS gps_integrations (
  id VARCHAR(32) PRIMARY KEY,
  company_id VARCHAR(64) NOT NULL UNIQUE,
  provider VARCHAR(100) DEFAULT 'NONE',
  api_base_url TEXT,
  api_key TEXT,
  enabled INT DEFAULT 0,
  notes TEXT,
  updated_at TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE INDEX IF NOT EXISTS idx_company_modules_co ON company_modules(company_id);

CREATE INDEX IF NOT EXISTS idx_contracts_co ON contracts(company_id);

CREATE INDEX IF NOT EXISTS idx_fiscal_years_co ON fiscal_years(company_id);