CREATE DATABASE IF NOT EXISTS comptaai_maroc
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE comptaai_maroc;

CREATE TABLE roles (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(80) NOT NULL UNIQUE,
  slug VARCHAR(80) NOT NULL UNIQUE,
  description VARCHAR(255) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE companies (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(180) NOT NULL,
  ice VARCHAR(30) NULL,
  fiscal_id VARCHAR(30) NULL,
  rc VARCHAR(30) NULL,
  address TEXT NULL,
  city VARCHAR(120) NULL,
  phone VARCHAR(40) NULL,
  email VARCHAR(180) NULL,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY companies_ice_unique (ice),
  KEY companies_status_index (status)
) ENGINE=InnoDB;

CREATE TABLE users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  role_id INT UNSIGNED NOT NULL,
  company_id BIGINT UNSIGNED NULL,
  full_name VARCHAR(160) NOT NULL,
  email VARCHAR(180) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  phone VARCHAR(40) NULL,
  avatar_path VARCHAR(255) NULL,
  status ENUM('active','inactive','invited','suspended') NOT NULL DEFAULT 'active',
  last_login_at DATETIME NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY users_email_unique (email),
  KEY users_role_index (role_id),
  KEY users_company_index (company_id),
  CONSTRAINT users_role_fk FOREIGN KEY (role_id) REFERENCES roles(id),
  CONSTRAINT users_company_fk FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE permissions (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  slug VARCHAR(120) NOT NULL UNIQUE,
  name VARCHAR(160) NOT NULL,
  module VARCHAR(80) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY permissions_module_index (module)
) ENGINE=InnoDB;

CREATE TABLE role_permissions (
  role_id INT UNSIGNED NOT NULL,
  permission_id INT UNSIGNED NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (role_id, permission_id),
  CONSTRAINT role_permissions_role_fk FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
  CONSTRAINT role_permissions_permission_fk FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE user_permissions (
  user_id BIGINT UNSIGNED NOT NULL,
  permission_id INT UNSIGNED NOT NULL,
  is_allowed TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (user_id, permission_id),
  CONSTRAINT user_permissions_user_fk FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT user_permissions_permission_fk FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE login_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NULL,
  email VARCHAR(180) NOT NULL,
  ip_address VARCHAR(45) NOT NULL,
  user_agent VARCHAR(255) NULL,
  succeeded TINYINT(1) NOT NULL DEFAULT 0,
  failure_reason VARCHAR(160) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY login_logs_user_index (user_id),
  KEY login_logs_email_index (email),
  CONSTRAINT login_logs_user_fk FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE password_resets (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  token_hash CHAR(64) NOT NULL,
  expires_at DATETIME NOT NULL,
  used_at DATETIME NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY password_resets_user_index (user_id),
  KEY password_resets_token_index (token_hash),
  CONSTRAINT password_resets_user_fk FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE login_attempts (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(180) NOT NULL,
  ip_address VARCHAR(45) NOT NULL,
  succeeded TINYINT(1) NOT NULL DEFAULT 0,
  attempted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY login_attempts_email_ip_index (email, ip_address, attempted_at)
) ENGINE=InnoDB;

CREATE TABLE audit_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NULL,
  action VARCHAR(120) NOT NULL,
  entity_type VARCHAR(120) NULL,
  entity_id BIGINT UNSIGNED NULL,
  ip_address VARCHAR(45) NULL,
  user_agent VARCHAR(255) NULL,
  metadata JSON NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY audit_logs_user_index (user_id),
  KEY audit_logs_action_index (action),
  CONSTRAINT audit_logs_user_fk FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE activity_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NULL,
  client_id BIGINT UNSIGNED NULL,
  module VARCHAR(80) NOT NULL,
  action VARCHAR(120) NOT NULL,
  description TEXT NOT NULL,
  ip_address VARCHAR(45) NULL,
  user_agent VARCHAR(255) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY activity_logs_user_index (user_id),
  KEY activity_logs_client_index (client_id),
  KEY activity_logs_module_action_index (module, action),
  KEY activity_logs_created_index (created_at),
  CONSTRAINT activity_logs_user_fk FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT activity_logs_client_fk FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE clients (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  accounting_firm_id BIGINT UNSIGNED NULL,
  assigned_accountant_id BIGINT UNSIGNED NULL,
  company_name VARCHAR(180) NOT NULL,
  ice VARCHAR(30) NULL,
  fiscal_id VARCHAR(30) NULL,
  rc VARCHAR(30) NULL,
  cnss_number VARCHAR(40) NULL,
  legal_form VARCHAR(80) NULL,
  activity_sector VARCHAR(140) NULL,
  address TEXT NULL,
  city VARCHAR(120) NULL,
  phone VARCHAR(40) NULL,
  email VARCHAR(180) NULL,
  contact_name VARCHAR(160) NULL,
  contact_email VARCHAR(180) NULL,
  contact_phone VARCHAR(40) NULL,
  status ENUM('active','inactive','archived') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  KEY clients_company_name_index (company_name),
  KEY clients_ice_index (ice),
  KEY clients_status_index (status),
  KEY clients_firm_index (accounting_firm_id),
  KEY clients_accountant_index (assigned_accountant_id),
  CONSTRAINT clients_firm_fk FOREIGN KEY (accounting_firm_id) REFERENCES companies(id) ON DELETE SET NULL,
  CONSTRAINT clients_accountant_fk FOREIGN KEY (assigned_accountant_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE user_client_access (
  user_id BIGINT UNSIGNED NOT NULL,
  client_id BIGINT UNSIGNED NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (user_id, client_id),
  CONSTRAINT user_client_access_user_fk FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT user_client_access_client_fk FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE notifications (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NULL,
  client_id BIGINT UNSIGNED NULL,
  type ENUM('new_document_uploaded','ocr_completed','extraction_needs_review','accounting_entry_needs_validation','tva_report_ready','bank_transaction_needs_matching','export_completed','ai_anomaly_detected','new_task','document_rejected','document_needs_correction','tva_report_validated','accounting_report_ready') NOT NULL,
  title VARCHAR(180) NOT NULL,
  message TEXT NULL,
  related_type VARCHAR(80) NULL,
  related_id BIGINT UNSIGNED NULL,
  read_at DATETIME NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY notifications_user_read_index (user_id, read_at),
  KEY notifications_client_index (client_id),
  KEY notifications_type_index (type),
  KEY notifications_created_index (created_at),
  CONSTRAINT notifications_user_fk FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT notifications_client_fk FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE subscription_plans (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL UNIQUE,
  slug VARCHAR(120) NOT NULL UNIQUE,
  monthly_price DECIMAL(12,2) NOT NULL DEFAULT 0,
  yearly_price DECIMAL(12,2) NOT NULL DEFAULT 0,
  max_clients INT UNSIGNED NOT NULL DEFAULT 0,
  max_users INT UNSIGNED NOT NULL DEFAULT 0,
  max_documents_per_month INT UNSIGNED NOT NULL DEFAULT 0,
  ai_features_enabled TINYINT(1) NOT NULL DEFAULT 0,
  ocr_limit_per_month INT UNSIGNED NOT NULL DEFAULT 0,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  KEY subscription_plans_status_index (status)
) ENGINE=InnoDB;

CREATE TABLE subscriptions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  plan_id BIGINT UNSIGNED NOT NULL,
  owner_type ENUM('company','client') NOT NULL DEFAULT 'company',
  owner_id BIGINT UNSIGNED NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE NULL,
  billing_cycle ENUM('monthly','yearly') NOT NULL DEFAULT 'monthly',
  status ENUM('active','trial','expired','cancelled') NOT NULL DEFAULT 'trial',
  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  KEY subscriptions_plan_index (plan_id),
  KEY subscriptions_owner_index (owner_type, owner_id),
  KEY subscriptions_status_index (status),
  KEY subscriptions_end_date_index (end_date),
  CONSTRAINT subscriptions_plan_fk FOREIGN KEY (plan_id) REFERENCES subscription_plans(id),
  CONSTRAINT subscriptions_created_by_fk FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE subscription_usage (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  subscription_id BIGINT UNSIGNED NOT NULL,
  usage_month TINYINT UNSIGNED NOT NULL,
  usage_year SMALLINT UNSIGNED NOT NULL,
  documents_uploaded INT UNSIGNED NOT NULL DEFAULT 0,
  ocr_processed INT UNSIGNED NOT NULL DEFAULT 0,
  ai_requests INT UNSIGNED NOT NULL DEFAULT 0,
  users_count INT UNSIGNED NOT NULL DEFAULT 0,
  clients_count INT UNSIGNED NOT NULL DEFAULT 0,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY subscription_usage_period_unique (subscription_id, usage_year, usage_month),
  CONSTRAINT subscription_usage_subscription_fk FOREIGN KEY (subscription_id) REFERENCES subscriptions(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE saas_invoices (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  subscription_id BIGINT UNSIGNED NOT NULL,
  invoice_number VARCHAR(60) NOT NULL UNIQUE,
  billing_period_start DATE NOT NULL,
  billing_period_end DATE NOT NULL,
  amount_ht DECIMAL(12,2) NOT NULL DEFAULT 0,
  tva_rate DECIMAL(5,2) NOT NULL DEFAULT 20.00,
  tva_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  amount_ttc DECIMAL(12,2) NOT NULL DEFAULT 0,
  due_date DATE NOT NULL,
  status ENUM('draft','unpaid','paid','overdue','cancelled') NOT NULL DEFAULT 'draft',
  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  KEY saas_invoices_subscription_index (subscription_id),
  KEY saas_invoices_status_index (status),
  KEY saas_invoices_due_date_index (due_date),
  KEY saas_invoices_period_index (billing_period_start, billing_period_end),
  CONSTRAINT saas_invoices_subscription_fk FOREIGN KEY (subscription_id) REFERENCES subscriptions(id) ON DELETE CASCADE,
  CONSTRAINT saas_invoices_created_by_fk FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE saas_payments (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  invoice_id BIGINT UNSIGNED NOT NULL,
  payment_date DATE NOT NULL,
  amount_paid DECIMAL(12,2) NOT NULL DEFAULT 0,
  payment_method ENUM('cash','bank_transfer','card','cheque','other') NOT NULL DEFAULT 'bank_transfer',
  reference VARCHAR(120) NULL,
  notes TEXT NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY saas_payments_invoice_index (invoice_id),
  KEY saas_payments_date_index (payment_date),
  CONSTRAINT saas_payments_invoice_fk FOREIGN KEY (invoice_id) REFERENCES saas_invoices(id) ON DELETE CASCADE,
  CONSTRAINT saas_payments_created_by_fk FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE system_backups (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  backup_type ENUM('database','uploads') NOT NULL DEFAULT 'database',
  file_name VARCHAR(255) NULL,
  file_path VARCHAR(500) NULL,
  file_size BIGINT UNSIGNED NOT NULL DEFAULT 0,
  status ENUM('created','failed','placeholder') NOT NULL DEFAULT 'created',
  message TEXT NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY system_backups_type_index (backup_type),
  KEY system_backups_status_index (status),
  KEY system_backups_created_index (created_at),
  CONSTRAINT system_backups_created_by_fk FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE system_settings (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  setting_key VARCHAR(120) NOT NULL UNIQUE,
  setting_value TEXT NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE cron_jobs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  job_key VARCHAR(120) NOT NULL UNIQUE,
  name VARCHAR(180) NOT NULL,
  description TEXT NULL,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  schedule_label VARCHAR(120) NULL,
  last_run_at DATETIME NULL,
  next_run_placeholder VARCHAR(120) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  KEY cron_jobs_status_index (status),
  KEY cron_jobs_last_run_index (last_run_at)
) ENGINE=InnoDB;

CREATE TABLE cron_job_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  cron_job_id BIGINT UNSIGNED NULL,
  job_key VARCHAR(120) NOT NULL,
  status ENUM('success','failed','skipped') NOT NULL DEFAULT 'success',
  message TEXT NULL,
  started_at DATETIME NOT NULL,
  finished_at DATETIME NULL,
  duration_ms INT UNSIGNED NOT NULL DEFAULT 0,
  triggered_by ENUM('manual','cron','system') NOT NULL DEFAULT 'cron',
  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY cron_job_logs_job_index (cron_job_id),
  KEY cron_job_logs_key_index (job_key),
  KEY cron_job_logs_started_index (started_at),
  CONSTRAINT cron_job_logs_job_fk FOREIGN KEY (cron_job_id) REFERENCES cron_jobs(id) ON DELETE SET NULL,
  CONSTRAINT cron_job_logs_user_fk FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE email_templates (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  template_code VARCHAR(120) NOT NULL UNIQUE,
  name VARCHAR(180) NOT NULL,
  subject VARCHAR(255) NOT NULL,
  body LONGTEXT NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  KEY email_templates_active_index (is_active)
) ENGINE=InnoDB;

CREATE TABLE email_queue (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  recipient_email VARCHAR(180) NOT NULL,
  recipient_name VARCHAR(180) NULL,
  subject VARCHAR(255) NOT NULL,
  body LONGTEXT NOT NULL,
  template_code VARCHAR(120) NULL,
  variables_json JSON NULL,
  status ENUM('pending','sent','failed') NOT NULL DEFAULT 'pending',
  retry_count TINYINT UNSIGNED NOT NULL DEFAULT 0,
  error_message TEXT NULL,
  queued_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  sent_at DATETIME NULL,
  KEY email_queue_status_index (status, queued_at),
  KEY email_queue_template_index (template_code)
) ENGINE=InnoDB;

CREATE TABLE email_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email_queue_id BIGINT UNSIGNED NULL,
  recipient_email VARCHAR(180) NOT NULL,
  subject VARCHAR(255) NOT NULL,
  status ENUM('sent','failed','skipped') NOT NULL,
  message TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY email_logs_queue_index (email_queue_id),
  KEY email_logs_status_index (status),
  KEY email_logs_created_index (created_at),
  CONSTRAINT email_logs_queue_fk FOREIGN KEY (email_queue_id) REFERENCES email_queue(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE tasks (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  client_id BIGINT UNSIGNED NULL,
  assigned_user_id BIGINT UNSIGNED NULL,
  title VARCHAR(180) NOT NULL,
  description TEXT NULL,
  priority ENUM('low','medium','high','urgent') NOT NULL DEFAULT 'medium',
  status ENUM('pending','in_progress','completed','cancelled') NOT NULL DEFAULT 'pending',
  due_date DATE NULL,
  related_type VARCHAR(80) NULL,
  related_id BIGINT UNSIGNED NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY tasks_related_unique (related_type, related_id),
  KEY tasks_client_index (client_id),
  KEY tasks_assigned_user_index (assigned_user_id),
  KEY tasks_status_index (status),
  KEY tasks_priority_index (priority),
  KEY tasks_due_date_index (due_date),
  CONSTRAINT tasks_client_fk FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE SET NULL,
  CONSTRAINT tasks_assigned_user_fk FOREIGN KEY (assigned_user_id) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT tasks_created_by_fk FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE task_comments (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  task_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NULL,
  comment TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY task_comments_task_index (task_id),
  KEY task_comments_user_index (user_id),
  CONSTRAINT task_comments_task_fk FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE,
  CONSTRAINT task_comments_user_fk FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE documents (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  client_id BIGINT UNSIGNED NOT NULL,
  uploaded_by BIGINT UNSIGNED NULL,
  document_type ENUM('invoice_purchase','invoice_sale','receipt','bank_statement','tax_document','other') NOT NULL DEFAULT 'invoice_purchase',
  accounting_month TINYINT UNSIGNED NOT NULL,
  accounting_year SMALLINT UNSIGNED NOT NULL,
  notes TEXT NULL,
  original_name VARCHAR(255) NOT NULL,
  stored_name VARCHAR(255) NOT NULL,
  stored_path VARCHAR(255) NOT NULL,
  mime_type VARCHAR(120) NOT NULL,
  file_size BIGINT UNSIGNED NOT NULL,
  status ENUM('uploaded','waiting_ocr','ocr_processed','needs_review','validated','exported','rejected') NOT NULL DEFAULT 'uploaded',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  KEY documents_client_index (client_id),
  KEY documents_type_index (document_type),
  KEY documents_period_index (accounting_year, accounting_month),
  KEY documents_status_index (status),
  KEY documents_uploaded_by_index (uploaded_by),
  CONSTRAINT documents_client_fk FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
  CONSTRAINT documents_uploaded_by_fk FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE document_status_history (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  document_id BIGINT UNSIGNED NOT NULL,
  old_status ENUM('uploaded','waiting_ocr','ocr_processed','needs_review','validated','exported','rejected') NULL,
  new_status ENUM('uploaded','waiting_ocr','ocr_processed','needs_review','validated','exported','rejected') NOT NULL,
  changed_by BIGINT UNSIGNED NULL,
  note VARCHAR(255) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY document_status_history_document_index (document_id),
  CONSTRAINT document_status_history_document_fk FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE CASCADE,
  CONSTRAINT document_status_history_changed_by_fk FOREIGN KEY (changed_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE document_extractions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  document_id BIGINT UNSIGNED NOT NULL,
  supplier_name VARCHAR(180) NULL,
  customer_name VARCHAR(180) NULL,
  invoice_number VARCHAR(80) NULL,
  invoice_date DATE NULL,
  due_date DATE NULL,
  ht_amount DECIMAL(14,2) NULL,
  tva_amount DECIMAL(14,2) NULL,
  ttc_amount DECIMAL(14,2) NULL,
  vat_rate DECIMAL(5,2) NULL,
  payment_method VARCHAR(80) NULL,
  currency VARCHAR(10) NOT NULL DEFAULT 'MAD',
  confidence_score DECIMAL(5,2) NULL,
  raw_ocr_text LONGTEXT NULL,
  ai_json_result JSON NULL,
  status ENUM('draft','needs_review','confirmed') NOT NULL DEFAULT 'draft',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY document_extractions_document_unique (document_id),
  KEY document_extractions_status_index (status),
  CONSTRAINT document_extractions_document_fk FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE accounting_entries (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  client_id BIGINT UNSIGNED NOT NULL,
  document_id BIGINT UNSIGNED NOT NULL,
  entry_date DATE NOT NULL,
  journal_code VARCHAR(20) NOT NULL,
  entry_label VARCHAR(255) NOT NULL,
  status ENUM('draft','reviewed','validated','exported') NOT NULL DEFAULT 'draft',
  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY accounting_entries_document_unique (document_id),
  KEY accounting_entries_client_index (client_id),
  KEY accounting_entries_status_index (status),
  KEY accounting_entries_period_index (entry_date),
  CONSTRAINT accounting_entries_client_fk FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
  CONSTRAINT accounting_entries_document_fk FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE CASCADE,
  CONSTRAINT accounting_entries_created_by_fk FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE accounting_entry_lines (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  entry_id BIGINT UNSIGNED NOT NULL,
  account_number VARCHAR(20) NOT NULL,
  account_label VARCHAR(180) NOT NULL,
  debit DECIMAL(14,2) NOT NULL DEFAULT 0,
  credit DECIMAL(14,2) NOT NULL DEFAULT 0,
  line_order SMALLINT UNSIGNED NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  KEY accounting_entry_lines_entry_index (entry_id),
  CONSTRAINT accounting_entry_lines_entry_fk FOREIGN KEY (entry_id) REFERENCES accounting_entries(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE vat_reports (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  client_id BIGINT UNSIGNED NOT NULL,
  month TINYINT UNSIGNED NOT NULL,
  year SMALLINT UNSIGNED NOT NULL,
  collected_vat DECIMAL(14,2) NOT NULL DEFAULT 0,
  deductible_vat DECIMAL(14,2) NOT NULL DEFAULT 0,
  payable_vat DECIMAL(14,2) NOT NULL DEFAULT 0,
  status ENUM('draft','validated') NOT NULL DEFAULT 'draft',
  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY vat_reports_client_period_unique (client_id, year, month),
  KEY vat_reports_status_index (status),
  CONSTRAINT vat_reports_client_fk FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
  CONSTRAINT vat_reports_created_by_fk FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE accounting_categories (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(20) NOT NULL,
  label VARCHAR(180) NOT NULL,
  type ENUM('expense','revenue','asset','liability','tax') NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY accounting_categories_code_unique (code)
) ENGINE=InnoDB;

CREATE TABLE vat_rates (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  label VARCHAR(80) NOT NULL,
  rate DECIMAL(5,2) NOT NULL,
  is_default TINYINT(1) NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE journal_entries (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  document_id BIGINT UNSIGNED NULL,
  category_id BIGINT UNSIGNED NULL,
  entry_date DATE NOT NULL,
  account_code VARCHAR(20) NOT NULL,
  label VARCHAR(255) NOT NULL,
  debit DECIMAL(14,2) NOT NULL DEFAULT 0,
  credit DECIMAL(14,2) NOT NULL DEFAULT 0,
  status ENUM('suggested','validated','exported') NOT NULL DEFAULT 'suggested',
  validated_by BIGINT UNSIGNED NULL,
  validated_at DATETIME NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY journal_entries_document_index (document_id),
  CONSTRAINT journal_entries_document_fk FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE SET NULL,
  CONSTRAINT journal_entries_category_fk FOREIGN KEY (category_id) REFERENCES accounting_categories(id) ON DELETE SET NULL,
  CONSTRAINT journal_entries_validated_by_fk FOREIGN KEY (validated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE bank_statements (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  client_id BIGINT UNSIGNED NOT NULL,
  uploaded_by BIGINT UNSIGNED NULL,
  bank_name VARCHAR(160) NOT NULL,
  account_number VARCHAR(80) NOT NULL,
  statement_month TINYINT UNSIGNED NOT NULL,
  statement_year SMALLINT UNSIGNED NOT NULL,
  notes TEXT NULL,
  original_name VARCHAR(255) NOT NULL,
  stored_name VARCHAR(255) NOT NULL,
  stored_path VARCHAR(255) NOT NULL,
  mime_type VARCHAR(120) NOT NULL,
  file_size BIGINT UNSIGNED NOT NULL,
  status ENUM('uploaded','imported') NOT NULL DEFAULT 'uploaded',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  KEY bank_statements_client_index (client_id),
  KEY bank_statements_period_index (statement_year, statement_month),
  KEY bank_statements_status_index (status),
  CONSTRAINT bank_statements_client_fk FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
  CONSTRAINT bank_statements_uploaded_by_fk FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE bank_transactions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  statement_id BIGINT UNSIGNED NOT NULL,
  client_id BIGINT UNSIGNED NOT NULL,
  transaction_date DATE NOT NULL,
  description VARCHAR(255) NOT NULL,
  debit_amount DECIMAL(14,2) NOT NULL DEFAULT 0,
  credit_amount DECIMAL(14,2) NOT NULL DEFAULT 0,
  balance DECIMAL(14,2) NULL,
  category ENUM('rent','supplier_payment','client_payment','bank_fees','salary','tax','other') NOT NULL DEFAULT 'other',
  matched_document_id BIGINT UNSIGNED NULL,
  status ENUM('imported','categorized','matched','ignored') NOT NULL DEFAULT 'imported',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY bank_transactions_statement_index (statement_id),
  KEY bank_transactions_client_index (client_id),
  KEY bank_transactions_status_index (status),
  KEY bank_transactions_date_index (transaction_date),
  CONSTRAINT bank_transactions_statement_fk FOREIGN KEY (statement_id) REFERENCES bank_statements(id) ON DELETE CASCADE,
  CONSTRAINT bank_transactions_client_fk FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
  CONSTRAINT bank_transactions_matched_document_fk FOREIGN KEY (matched_document_id) REFERENCES documents(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE app_settings (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  setting_key VARCHAR(120) NOT NULL UNIQUE,
  setting_value TEXT NULL,
  is_secret TINYINT(1) NOT NULL DEFAULT 0,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE ai_settings (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  setting_key VARCHAR(120) NOT NULL UNIQUE,
  setting_value TEXT NULL,
  is_secret TINYINT(1) NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE ai_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  task_type VARCHAR(120) NOT NULL,
  related_type VARCHAR(120) NULL,
  related_id BIGINT UNSIGNED NULL,
  provider VARCHAR(80) NULL,
  model VARCHAR(120) NULL,
  request_payload JSON NULL,
  response_payload JSON NULL,
  status ENUM('demo','success','error') NOT NULL DEFAULT 'demo',
  error_message TEXT NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY ai_logs_task_type_index (task_type),
  KEY ai_logs_related_index (related_type, related_id),
  KEY ai_logs_created_by_index (created_by),
  CONSTRAINT ai_logs_created_by_fk FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

INSERT INTO roles (name, slug, description) VALUES
('Super Admin', 'super-admin', 'Acces complet a la plateforme'),
('Cabinet comptable', 'accounting-firm', 'Gestion des dossiers clients du cabinet'),
('Admin Cabinet', 'admin-cabinet', 'Administrateur du cabinet comptable'),
('Entreprise', 'company', 'Compte entreprise cliente'),
('Comptable legacy', 'accountant', 'Traitement et validation comptable'),
('Comptable', 'comptable', 'Comptable responsable de dossiers clients'),
('Assistant Comptable', 'assistant-comptable', 'Assistant avec acces operationnel limite'),
('Client', 'client', 'Depot et suivi des documents')
ON DUPLICATE KEY UPDATE name = VALUES(name), description = VALUES(description);

INSERT INTO permissions (slug, name, module) VALUES
('clients.view', 'Voir les clients', 'Clients'),
('clients.create', 'Creer des clients', 'Clients'),
('clients.edit', 'Modifier les clients', 'Clients'),
('clients.delete', 'Supprimer les clients', 'Clients'),
('documents.view', 'Voir les documents', 'Documents'),
('documents.upload', 'Uploader des documents', 'Documents'),
('documents.delete', 'Supprimer les documents', 'Documents'),
('ocr.process', 'Lancer OCR', 'OCR'),
('accounting.validate', 'Valider les ecritures', 'Comptabilite'),
('tva.validate', 'Valider la TVA', 'TVA'),
('bank.manage', 'Gerer la banque', 'Banque'),
('exports.generate', 'Generer les exports', 'Exports'),
('reports.view', 'Voir les rapports', 'Rapports'),
('users.manage', 'Gerer les utilisateurs', 'Utilisateurs'),
('settings.manage', 'Gerer les parametres', 'Parametres')
ON DUPLICATE KEY UPDATE name = VALUES(name), module = VALUES(module);

INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT roles.id, permissions.id FROM roles CROSS JOIN permissions WHERE roles.slug = 'super-admin';

INSERT INTO vat_rates (label, rate, is_default) VALUES
('TVA 20%', 20.00, 1),
('TVA 14%', 14.00, 0),
('TVA 10%', 10.00, 0),
('TVA 7%', 7.00, 0),
('Exonere', 0.00, 0);

INSERT INTO subscription_plans
  (name, slug, monthly_price, yearly_price, max_clients, max_users, max_documents_per_month, ai_features_enabled, ocr_limit_per_month, status)
VALUES
  ('Free Trial', 'free-trial', 0.00, 0.00, 2, 2, 50, 0, 25, 'active'),
  ('Starter', 'starter', 199.00, 1990.00, 5, 3, 300, 0, 100, 'active'),
  ('Pro', 'pro', 499.00, 4990.00, 20, 10, 1500, 1, 600, 'active'),
  ('Cabinet', 'cabinet', 999.00, 9990.00, 100, 30, 6000, 1, 2500, 'active'),
  ('Enterprise', 'enterprise', 0.00, 0.00, 0, 0, 0, 1, 0, 'active')
ON DUPLICATE KEY UPDATE
  monthly_price = VALUES(monthly_price),
  yearly_price = VALUES(yearly_price),
  max_clients = VALUES(max_clients),
  max_users = VALUES(max_users),
  max_documents_per_month = VALUES(max_documents_per_month),
  ai_features_enabled = VALUES(ai_features_enabled),
  ocr_limit_per_month = VALUES(ocr_limit_per_month),
  status = VALUES(status);

INSERT INTO system_settings (setting_key, setting_value) VALUES
('maintenance.enabled', '0'),
('maintenance.message', 'La plateforme est temporairement en maintenance. Merci de revenir plus tard.')
ON DUPLICATE KEY UPDATE setting_value = VALUES(setting_value);

INSERT INTO cron_jobs (job_key, name, description, status, schedule_label, next_run_placeholder) VALUES
('check_subscriptions', 'Verifier les abonnements expires', 'Passe les abonnements termines en statut expire.', 'active', 'Quotidien', 'Configure plus tard via cPanel'),
('overdue_invoices', 'Marquer les factures SaaS en retard', 'Passe les factures impayees arrivees a echeance en retard.', 'active', 'Quotidien', 'Configure plus tard via cPanel'),
('document_reminders', 'Rappels documents en attente', 'Cree des rappels pour les documents non traites.', 'active', 'Quotidien', 'Configure plus tard via cPanel'),
('tva_reminders', 'Rappels validation TVA', 'Cree des rappels pour les rapports TVA brouillon.', 'active', 'Hebdomadaire', 'Configure plus tard via cPanel'),
('cleanup_temp', 'Nettoyage fichiers temporaires', 'Supprime les fichiers temporaires anciens.', 'active', 'Quotidien', 'Configure plus tard via cPanel'),
('monthly_usage', 'Statistiques usage mensuel', 'Actualise les statistiques mensuelles des abonnements.', 'active', 'Mensuel', 'Configure plus tard via cPanel'),
('database_backup_placeholder', 'Backup base de donnees placeholder', 'Journalise le backup automatique futur sans creer de fichier.', 'inactive', 'Mensuel', 'Configure plus tard via cPanel'),
('send_emails', 'Envoyer les emails en file', 'Traite les emails pending si SMTP est configure et test mode desactive.', 'active', 'Toutes les 5 minutes', 'Configure plus tard via cPanel')
ON DUPLICATE KEY UPDATE
  name = VALUES(name),
  description = VALUES(description),
  schedule_label = VALUES(schedule_label),
  next_run_placeholder = VALUES(next_run_placeholder);

INSERT INTO system_settings (setting_key, setting_value)
VALUES ('cron.secret_token', SHA2(UUID(), 256))
ON DUPLICATE KEY UPDATE setting_value = setting_value;

INSERT INTO email_templates (template_code, name, subject, body, is_active) VALUES
('welcome_user', 'Bienvenue utilisateur', 'Bienvenue sur {{platform_name}}', 'Bonjour {{user_name}},\n\nVotre compte {{platform_name}} est pret.\nEmail: {{user_email}}\n\nCordialement,\n{{sender_name}}', 1),
('password_reset', 'Reinitialisation mot de passe', 'Reinitialisation de votre mot de passe', 'Bonjour {{user_name}},\n\nUtilisez ce lien pour reinitialiser votre mot de passe:\n{{reset_link}}\n\nSi vous n etes pas a l origine de cette demande, ignorez cet email.', 1),
('new_document_uploaded', 'Nouveau document depose', 'Nouveau document depose pour {{client_name}}', 'Bonjour,\n\nUn nouveau document a ete depose: {{document_name}}.\nClient: {{client_name}}\nStatut: {{status}}\n\nMerci de le traiter.', 1),
('document_needs_review', 'Document a reviser', 'Document a reviser - {{client_name}}', 'Bonjour,\n\nLe document {{document_name}} necessite une revision.\nMotif: {{reason}}\n\nMerci.', 1),
('ocr_completed', 'OCR termine', 'OCR termine pour {{document_name}}', 'Bonjour,\n\nLe traitement OCR est termine pour {{document_name}}.\nVous pouvez verifier les champs extraits.', 1),
('accounting_entry_validated', 'Ecriture comptable validee', 'Ecriture comptable validee', 'Bonjour,\n\nL ecriture {{entry_label}} a ete validee pour {{client_name}}.', 1),
('tva_report_ready', 'Rapport TVA pret', 'Rapport TVA pret - {{client_name}}', 'Bonjour,\n\nLe rapport TVA {{period}} est pret pour validation.', 1),
('subscription_expiring', 'Abonnement bientot expire', 'Votre abonnement expire bientot', 'Bonjour,\n\nVotre abonnement {{plan_name}} expire le {{end_date}}.\nMerci de prevoir le renouvellement.', 1),
('saas_invoice_unpaid', 'Facture SaaS impayee', 'Facture SaaS impayee {{invoice_number}}', 'Bonjour,\n\nLa facture {{invoice_number}} d un montant de {{amount_ttc}} MAD est en attente de paiement.\nEcheance: {{due_date}}.', 1),
('task_assigned', 'Tache assignee', 'Nouvelle tache: {{task_title}}', 'Bonjour {{user_name}},\n\nUne tache vous a ete assignee: {{task_title}}\nPriorite: {{priority}}\nEcheance: {{due_date}}\n\n{{task_description}}', 1)
ON DUPLICATE KEY UPDATE
  name = VALUES(name),
  subject = VALUES(subject),
  body = VALUES(body);

INSERT INTO app_settings (setting_key, setting_value, is_secret) VALUES
('email.test_mode', '1', 0)
ON DUPLICATE KEY UPDATE setting_value = setting_value;

INSERT INTO accounting_categories (code, label, type) VALUES
('6111', 'Achats de marchandises', 'expense'),
('6122', 'Achats consommables', 'expense'),
('6131', 'Locations et charges locatives', 'expense'),
('6145', 'Frais postaux et telecom', 'expense'),
('6167', 'Impots et taxes', 'tax'),
('7111', 'Ventes de marchandises', 'revenue'),
('7124', 'Prestations de services', 'revenue'),
('3455', 'Etat - TVA recuperable', 'tax'),
('4455', 'Etat - TVA facturee', 'tax');

INSERT INTO ai_settings (setting_key, setting_value, is_secret) VALUES
('provider', 'openai', 0),
('model', 'gpt-4.1-mini', 0),
('api_key', '', 1),
('enabled', '0', 0)
ON DUPLICATE KEY UPDATE setting_value = VALUES(setting_value), is_secret = VALUES(is_secret);