SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE DATABASE IF NOT EXISTS cashflow_app
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;
USE cashflow_app;

DROP TABLE IF EXISTS audit_logs;
DROP TABLE IF EXISTS approval_requests;
DROP TABLE IF EXISTS email_queue;
DROP TABLE IF EXISTS notifications;
DROP TABLE IF EXISTS import_batch_rows;
DROP TABLE IF EXISTS import_batches;
DROP TABLE IF EXISTS import_profiles;
DROP TABLE IF EXISTS forecast_variances;
DROP TABLE IF EXISTS forecast_manual_adjustments;
DROP TABLE IF EXISTS forecast_lines;
DROP TABLE IF EXISTS forecast_scenarios;
DROP TABLE IF EXISTS recurring_generated_items;
DROP TABLE IF EXISTS recurring_rules;
DROP TABLE IF EXISTS internal_transfers;
DROP TABLE IF EXISTS transaction_allocations;
DROP TABLE IF EXISTS cash_transactions;
DROP TABLE IF EXISTS supplier_invoice_status_history;
DROP TABLE IF EXISTS customer_invoice_status_history;
DROP TABLE IF EXISTS supplier_invoices;
DROP TABLE IF EXISTS customer_invoices;
DROP TABLE IF EXISTS categories;
DROP TABLE IF EXISTS category_groups;
DROP TABLE IF EXISTS suppliers;
DROP TABLE IF EXISTS clients;
DROP TABLE IF EXISTS cash_accounts;
DROP TABLE IF EXISTS user_company_access;
DROP TABLE IF EXISTS company_locations;
DROP TABLE IF EXISTS companies;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS roles;

CREATE TABLE roles (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(50) NOT NULL UNIQUE,
  name VARCHAR(100) NOT NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  role_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(150) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  last_login_at DATETIME NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  deleted_at DATETIME NULL,
  CONSTRAINT fk_users_role FOREIGN KEY (role_id) REFERENCES roles(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE companies (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(190) NOT NULL,
  code VARCHAR(50) NOT NULL UNIQUE,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  deleted_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE company_locations (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(190) NOT NULL,
  code VARCHAR(50) NULL,
  city VARCHAR(120) NULL,
  address VARCHAR(255) NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  deleted_at DATETIME NULL,
  CONSTRAINT fk_locations_company FOREIGN KEY (company_id) REFERENCES companies(id),
  INDEX idx_locations_company (company_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE user_company_access (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  company_id BIGINT UNSIGNED NOT NULL,
  created_at DATETIME NULL,
  UNIQUE KEY uq_user_company (user_id, company_id),
  CONSTRAINT fk_uca_user FOREIGN KEY (user_id) REFERENCES users(id),
  CONSTRAINT fk_uca_company FOREIGN KEY (company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE cash_accounts (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NULL,
  name VARCHAR(190) NOT NULL,
  type VARCHAR(30) NOT NULL,
  bank_name VARCHAR(190) NULL,
  iban VARCHAR(64) NULL,
  currency CHAR(3) NOT NULL DEFAULT 'RON',
  opening_balance DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  current_balance_cache DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  deleted_at DATETIME NULL,
  CONSTRAINT fk_cash_accounts_company FOREIGN KEY (company_id) REFERENCES companies(id),
  CONSTRAINT fk_cash_accounts_location FOREIGN KEY (location_id) REFERENCES company_locations(id),
  INDEX idx_cash_accounts_company (company_id),
  INDEX idx_cash_accounts_location (location_id),
  INDEX idx_cash_accounts_type (type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE clients (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(190) NOT NULL,
  code VARCHAR(50) NULL,
  email VARCHAR(190) NULL,
  phone VARCHAR(50) NULL,
  contact_person VARCHAR(150) NULL,
  payment_term_days INT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  notes TEXT NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  deleted_at DATETIME NULL,
  CONSTRAINT fk_clients_company FOREIGN KEY (company_id) REFERENCES companies(id),
  INDEX idx_clients_company (company_id),
  INDEX idx_clients_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE suppliers (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(190) NOT NULL,
  code VARCHAR(50) NULL,
  email VARCHAR(190) NULL,
  phone VARCHAR(50) NULL,
  contact_person VARCHAR(150) NULL,
  payment_term_days INT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  notes TEXT NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  deleted_at DATETIME NULL,
  CONSTRAINT fk_suppliers_company FOREIGN KEY (company_id) REFERENCES companies(id),
  INDEX idx_suppliers_company (company_id),
  INDEX idx_suppliers_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE category_groups (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NULL,
  name VARCHAR(120) NOT NULL,
  type VARCHAR(20) NOT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  is_system TINYINT(1) NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  CONSTRAINT fk_category_groups_company FOREIGN KEY (company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE categories (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NULL,
  category_group_id BIGINT UNSIGNED NULL,
  name VARCHAR(150) NOT NULL,
  type VARCHAR(20) NOT NULL,
  is_system TINYINT(1) NOT NULL DEFAULT 0,
  is_editable TINYINT(1) NOT NULL DEFAULT 1,
  sort_order INT NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  CONSTRAINT fk_categories_company FOREIGN KEY (company_id) REFERENCES companies(id),
  CONSTRAINT fk_categories_group FOREIGN KEY (category_group_id) REFERENCES category_groups(id),
  INDEX idx_categories_company (company_id),
  INDEX idx_categories_type (type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE customer_invoices (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NULL,
  client_id BIGINT UNSIGNED NOT NULL,
  category_id BIGINT UNSIGNED NULL,
  invoice_number_internal VARCHAR(100) NOT NULL,
  issue_date DATE NOT NULL,
  due_date DATE NOT NULL,
  amount_total DECIMAL(15,2) NOT NULL,
  amount_paid DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  amount_open DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  status VARCHAR(30) NOT NULL,
  description TEXT NULL,
  created_by BIGINT UNSIGNED NOT NULL,
  confirmed_by BIGINT UNSIGNED NULL,
  confirmed_at DATETIME NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  deleted_at DATETIME NULL,
  CONSTRAINT fk_ci_company FOREIGN KEY (company_id) REFERENCES companies(id),
  CONSTRAINT fk_ci_location FOREIGN KEY (location_id) REFERENCES company_locations(id),
  CONSTRAINT fk_ci_client FOREIGN KEY (client_id) REFERENCES clients(id),
  CONSTRAINT fk_ci_category FOREIGN KEY (category_id) REFERENCES categories(id),
  CONSTRAINT fk_ci_created_by FOREIGN KEY (created_by) REFERENCES users(id),
  CONSTRAINT fk_ci_confirmed_by FOREIGN KEY (confirmed_by) REFERENCES users(id),
  INDEX idx_ci_company (company_id),
  INDEX idx_ci_due_date (due_date),
  INDEX idx_ci_status (status),
  INDEX idx_ci_client (client_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE customer_invoice_status_history (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  invoice_id BIGINT UNSIGNED NOT NULL,
  old_status VARCHAR(30) NULL,
  new_status VARCHAR(30) NOT NULL,
  changed_by BIGINT UNSIGNED NOT NULL,
  changed_at DATETIME NOT NULL,
  note TEXT NULL,
  CONSTRAINT fk_cish_invoice FOREIGN KEY (invoice_id) REFERENCES customer_invoices(id),
  CONSTRAINT fk_cish_user FOREIGN KEY (changed_by) REFERENCES users(id),
  INDEX idx_cish_invoice (invoice_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE supplier_invoices (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NULL,
  supplier_id BIGINT UNSIGNED NOT NULL,
  category_id BIGINT UNSIGNED NULL,
  invoice_number_internal VARCHAR(100) NOT NULL,
  issue_date DATE NOT NULL,
  due_date DATE NOT NULL,
  amount_total DECIMAL(15,2) NOT NULL,
  amount_paid DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  amount_open DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  status VARCHAR(30) NOT NULL,
  description TEXT NULL,
  created_by BIGINT UNSIGNED NOT NULL,
  confirmed_by BIGINT UNSIGNED NULL,
  confirmed_at DATETIME NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  deleted_at DATETIME NULL,
  CONSTRAINT fk_si_company FOREIGN KEY (company_id) REFERENCES companies(id),
  CONSTRAINT fk_si_location FOREIGN KEY (location_id) REFERENCES company_locations(id),
  CONSTRAINT fk_si_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers(id),
  CONSTRAINT fk_si_category FOREIGN KEY (category_id) REFERENCES categories(id),
  CONSTRAINT fk_si_created_by FOREIGN KEY (created_by) REFERENCES users(id),
  CONSTRAINT fk_si_confirmed_by FOREIGN KEY (confirmed_by) REFERENCES users(id),
  INDEX idx_si_company (company_id),
  INDEX idx_si_due_date (due_date),
  INDEX idx_si_status (status),
  INDEX idx_si_supplier (supplier_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE supplier_invoice_status_history (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  invoice_id BIGINT UNSIGNED NOT NULL,
  old_status VARCHAR(30) NULL,
  new_status VARCHAR(30) NOT NULL,
  changed_by BIGINT UNSIGNED NOT NULL,
  changed_at DATETIME NOT NULL,
  note TEXT NULL,
  CONSTRAINT fk_sish_invoice FOREIGN KEY (invoice_id) REFERENCES supplier_invoices(id),
  CONSTRAINT fk_sish_user FOREIGN KEY (changed_by) REFERENCES users(id),
  INDEX idx_sish_invoice (invoice_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE cash_transactions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NULL,
  cash_account_id BIGINT UNSIGNED NOT NULL,
  category_id BIGINT UNSIGNED NULL,
  direction VARCHAR(10) NOT NULL,
  transaction_date DATE NOT NULL,
  value_date DATE NULL,
  amount DECIMAL(15,2) NOT NULL,
  source_type VARCHAR(30) NOT NULL,
  source_id BIGINT UNSIGNED NULL,
  counterparty_type VARCHAR(30) NULL,
  counterparty_id BIGINT UNSIGNED NULL,
  status VARCHAR(30) NOT NULL DEFAULT 'draft',
  description TEXT NULL,
  created_by BIGINT UNSIGNED NOT NULL,
  confirmed_by BIGINT UNSIGNED NULL,
  confirmed_at DATETIME NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  deleted_at DATETIME NULL,
  CONSTRAINT fk_ct_company FOREIGN KEY (company_id) REFERENCES companies(id),
  CONSTRAINT fk_ct_location FOREIGN KEY (location_id) REFERENCES company_locations(id),
  CONSTRAINT fk_ct_account FOREIGN KEY (cash_account_id) REFERENCES cash_accounts(id),
  CONSTRAINT fk_ct_category FOREIGN KEY (category_id) REFERENCES categories(id),
  CONSTRAINT fk_ct_created_by FOREIGN KEY (created_by) REFERENCES users(id),
  CONSTRAINT fk_ct_confirmed_by FOREIGN KEY (confirmed_by) REFERENCES users(id),
  INDEX idx_ct_company (company_id),
  INDEX idx_ct_account (cash_account_id),
  INDEX idx_ct_date (transaction_date),
  INDEX idx_ct_status (status),
  INDEX idx_ct_direction (direction)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE transaction_allocations (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  transaction_id BIGINT UNSIGNED NOT NULL,
  document_type VARCHAR(30) NOT NULL,
  document_id BIGINT UNSIGNED NOT NULL,
  allocated_amount DECIMAL(15,2) NOT NULL,
  allocated_at DATETIME NOT NULL,
  created_by BIGINT UNSIGNED NOT NULL,
  CONSTRAINT fk_ta_transaction FOREIGN KEY (transaction_id) REFERENCES cash_transactions(id),
  CONSTRAINT fk_ta_user FOREIGN KEY (created_by) REFERENCES users(id),
  INDEX idx_ta_transaction (transaction_id),
  INDEX idx_ta_document (document_type, document_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE internal_transfers (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  from_company_id BIGINT UNSIGNED NOT NULL,
  to_company_id BIGINT UNSIGNED NOT NULL,
  from_account_id BIGINT UNSIGNED NOT NULL,
  to_account_id BIGINT UNSIGNED NOT NULL,
  transfer_date DATE NOT NULL,
  amount DECIMAL(15,2) NOT NULL,
  status VARCHAR(30) NOT NULL DEFAULT 'draft',
  out_transaction_id BIGINT UNSIGNED NULL,
  in_transaction_id BIGINT UNSIGNED NULL,
  note TEXT NULL,
  created_by BIGINT UNSIGNED NOT NULL,
  approved_by BIGINT UNSIGNED NULL,
  approved_at DATETIME NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  CONSTRAINT fk_it_from_company FOREIGN KEY (from_company_id) REFERENCES companies(id),
  CONSTRAINT fk_it_to_company FOREIGN KEY (to_company_id) REFERENCES companies(id),
  CONSTRAINT fk_it_from_account FOREIGN KEY (from_account_id) REFERENCES cash_accounts(id),
  CONSTRAINT fk_it_to_account FOREIGN KEY (to_account_id) REFERENCES cash_accounts(id),
  CONSTRAINT fk_it_out_tx FOREIGN KEY (out_transaction_id) REFERENCES cash_transactions(id),
  CONSTRAINT fk_it_in_tx FOREIGN KEY (in_transaction_id) REFERENCES cash_transactions(id),
  CONSTRAINT fk_it_created_by FOREIGN KEY (created_by) REFERENCES users(id),
  CONSTRAINT fk_it_approved_by FOREIGN KEY (approved_by) REFERENCES users(id),
  INDEX idx_it_transfer_date (transfer_date),
  INDEX idx_it_from_company (from_company_id),
  INDEX idx_it_to_company (to_company_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE recurring_rules (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NULL,
  cash_account_id BIGINT UNSIGNED NULL,
  category_id BIGINT UNSIGNED NULL,
  counterparty_type VARCHAR(30) NULL,
  counterparty_id BIGINT UNSIGNED NULL,
  name VARCHAR(190) NOT NULL,
  direction VARCHAR(10) NOT NULL,
  frequency VARCHAR(30) NOT NULL,
  interval_value INT NOT NULL DEFAULT 1,
  day_of_month INT NULL,
  start_date DATE NOT NULL,
  end_date DATE NULL,
  default_amount DECIMAL(15,2) NOT NULL,
  auto_generate TINYINT(1) NOT NULL DEFAULT 1,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  description TEXT NULL,
  created_by BIGINT UNSIGNED NOT NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  CONSTRAINT fk_rr_company FOREIGN KEY (company_id) REFERENCES companies(id),
  CONSTRAINT fk_rr_location FOREIGN KEY (location_id) REFERENCES company_locations(id),
  CONSTRAINT fk_rr_account FOREIGN KEY (cash_account_id) REFERENCES cash_accounts(id),
  CONSTRAINT fk_rr_category FOREIGN KEY (category_id) REFERENCES categories(id),
  CONSTRAINT fk_rr_user FOREIGN KEY (created_by) REFERENCES users(id),
  INDEX idx_rr_company (company_id),
  INDEX idx_rr_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE recurring_generated_items (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  recurring_rule_id BIGINT UNSIGNED NOT NULL,
  company_id BIGINT UNSIGNED NOT NULL,
  due_date DATE NOT NULL,
  amount DECIMAL(15,2) NOT NULL,
  generated_type VARCHAR(30) NOT NULL,
  generated_id BIGINT UNSIGNED NULL,
  status VARCHAR(30) NOT NULL DEFAULT 'generated',
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  CONSTRAINT fk_rgi_rule FOREIGN KEY (recurring_rule_id) REFERENCES recurring_rules(id),
  CONSTRAINT fk_rgi_company FOREIGN KEY (company_id) REFERENCES companies(id),
  INDEX idx_rgi_rule (recurring_rule_id),
  INDEX idx_rgi_due_date (due_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE forecast_scenarios (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(50) NOT NULL UNIQUE,
  name VARCHAR(100) NOT NULL,
  delay_days_inflow INT NOT NULL DEFAULT 0,
  delay_days_outflow INT NOT NULL DEFAULT 0,
  inflow_multiplier DECIMAL(8,4) NOT NULL DEFAULT 1.0000,
  outflow_multiplier DECIMAL(8,4) NOT NULL DEFAULT 1.0000,
  is_default TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NULL,
  updated_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE forecast_lines (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NULL,
  cash_account_id BIGINT UNSIGNED NULL,
  scenario_id BIGINT UNSIGNED NOT NULL,
  source_type VARCHAR(30) NOT NULL,
  source_id BIGINT UNSIGNED NULL,
  flow_direction VARCHAR(10) NOT NULL,
  forecast_date DATE NOT NULL,
  amount DECIMAL(15,2) NOT NULL,
  status VARCHAR(30) NOT NULL DEFAULT 'open',
  note TEXT NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  CONSTRAINT fk_fl_company FOREIGN KEY (company_id) REFERENCES companies(id),
  CONSTRAINT fk_fl_location FOREIGN KEY (location_id) REFERENCES company_locations(id),
  CONSTRAINT fk_fl_account FOREIGN KEY (cash_account_id) REFERENCES cash_accounts(id),
  CONSTRAINT fk_fl_scenario FOREIGN KEY (scenario_id) REFERENCES forecast_scenarios(id),
  INDEX idx_fl_company (company_id),
  INDEX idx_fl_scenario (scenario_id),
  INDEX idx_fl_date (forecast_date),
  INDEX idx_fl_source (source_type, source_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE forecast_manual_adjustments (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NULL,
  cash_account_id BIGINT UNSIGNED NULL,
  scenario_id BIGINT UNSIGNED NOT NULL,
  flow_direction VARCHAR(10) NOT NULL,
  forecast_date DATE NOT NULL,
  amount DECIMAL(15,2) NOT NULL,
  reason VARCHAR(255) NULL,
  created_by BIGINT UNSIGNED NOT NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  CONSTRAINT fk_fma_company FOREIGN KEY (company_id) REFERENCES companies(id),
  CONSTRAINT fk_fma_location FOREIGN KEY (location_id) REFERENCES company_locations(id),
  CONSTRAINT fk_fma_account FOREIGN KEY (cash_account_id) REFERENCES cash_accounts(id),
  CONSTRAINT fk_fma_scenario FOREIGN KEY (scenario_id) REFERENCES forecast_scenarios(id),
  CONSTRAINT fk_fma_user FOREIGN KEY (created_by) REFERENCES users(id),
  INDEX idx_fma_company (company_id),
  INDEX idx_fma_date (forecast_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE forecast_variances (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  scenario_id BIGINT UNSIGNED NOT NULL,
  period_type VARCHAR(20) NOT NULL,
  period_start DATE NOT NULL,
  period_end DATE NOT NULL,
  forecast_in DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  forecast_out DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  actual_in DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  actual_out DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  variance_net DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  calculated_at DATETIME NOT NULL,
  CONSTRAINT fk_fv_company FOREIGN KEY (company_id) REFERENCES companies(id),
  CONSTRAINT fk_fv_scenario FOREIGN KEY (scenario_id) REFERENCES forecast_scenarios(id),
  INDEX idx_fv_company (company_id),
  INDEX idx_fv_period (period_type, period_start, period_end)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE import_profiles (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NULL,
  name VARCHAR(150) NOT NULL,
  profile_type VARCHAR(30) NOT NULL,
  delimiter VARCHAR(5) NULL,
  enclosure VARCHAR(5) NULL,
  date_format VARCHAR(30) NULL,
  amount_column VARCHAR(100) NULL,
  mapping_json JSON NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_by BIGINT UNSIGNED NOT NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  CONSTRAINT fk_ip_company FOREIGN KEY (company_id) REFERENCES companies(id),
  CONSTRAINT fk_ip_user FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE import_batches (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  cash_account_id BIGINT UNSIGNED NULL,
  import_profile_id BIGINT UNSIGNED NULL,
  file_name VARCHAR(255) NOT NULL,
  status VARCHAR(30) NOT NULL,
  total_rows INT NOT NULL DEFAULT 0,
  valid_rows INT NOT NULL DEFAULT 0,
  invalid_rows INT NOT NULL DEFAULT 0,
  imported_rows INT NOT NULL DEFAULT 0,
  created_by BIGINT UNSIGNED NOT NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  CONSTRAINT fk_ib_company FOREIGN KEY (company_id) REFERENCES companies(id),
  CONSTRAINT fk_ib_account FOREIGN KEY (cash_account_id) REFERENCES cash_accounts(id),
  CONSTRAINT fk_ib_profile FOREIGN KEY (import_profile_id) REFERENCES import_profiles(id),
  CONSTRAINT fk_ib_user FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE import_batch_rows (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  batch_id BIGINT UNSIGNED NOT NULL,
  row_no INT NOT NULL,
  raw_data_json JSON NOT NULL,
  mapped_data_json JSON NULL,
  validation_status VARCHAR(30) NOT NULL,
  validation_message TEXT NULL,
  created_transaction_id BIGINT UNSIGNED NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  CONSTRAINT fk_ibr_batch FOREIGN KEY (batch_id) REFERENCES import_batches(id),
  CONSTRAINT fk_ibr_transaction FOREIGN KEY (created_transaction_id) REFERENCES cash_transactions(id),
  INDEX idx_ibr_batch (batch_id),
  INDEX idx_ibr_status (validation_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE notifications (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  company_id BIGINT UNSIGNED NULL,
  type VARCHAR(50) NOT NULL,
  title VARCHAR(190) NOT NULL,
  message TEXT NOT NULL,
  is_read TINYINT(1) NOT NULL DEFAULT 0,
  read_at DATETIME NULL,
  created_at DATETIME NULL,
  CONSTRAINT fk_notifications_user FOREIGN KEY (user_id) REFERENCES users(id),
  CONSTRAINT fk_notifications_company FOREIGN KEY (company_id) REFERENCES companies(id),
  INDEX idx_notifications_user (user_id),
  INDEX idx_notifications_read (is_read)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE email_queue (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NULL,
  to_email VARCHAR(190) NOT NULL,
  subject VARCHAR(255) NOT NULL,
  body_html LONGTEXT NOT NULL,
  status VARCHAR(30) NOT NULL DEFAULT 'pending',
  attempts INT NOT NULL DEFAULT 0,
  last_error TEXT NULL,
  scheduled_at DATETIME NULL,
  sent_at DATETIME NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  CONSTRAINT fk_email_queue_user FOREIGN KEY (user_id) REFERENCES users(id),
  INDEX idx_email_queue_status (status),
  INDEX idx_email_queue_scheduled (scheduled_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE approval_requests (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  request_type VARCHAR(50) NOT NULL,
  entity_type VARCHAR(50) NOT NULL,
  entity_id BIGINT UNSIGNED NOT NULL,
  requested_by BIGINT UNSIGNED NOT NULL,
  approved_by BIGINT UNSIGNED NULL,
  status VARCHAR(30) NOT NULL DEFAULT 'pending',
  reason TEXT NULL,
  requested_at DATETIME NOT NULL,
  decided_at DATETIME NULL,
  CONSTRAINT fk_ar_company FOREIGN KEY (company_id) REFERENCES companies(id),
  CONSTRAINT fk_ar_requested_by FOREIGN KEY (requested_by) REFERENCES users(id),
  CONSTRAINT fk_ar_approved_by FOREIGN KEY (approved_by) REFERENCES users(id),
  INDEX idx_ar_company (company_id),
  INDEX idx_ar_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE audit_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NULL,
  company_id BIGINT UNSIGNED NULL,
  entity_type VARCHAR(50) NOT NULL,
  entity_id BIGINT UNSIGNED NOT NULL,
  action VARCHAR(50) NOT NULL,
  old_values_json JSON NULL,
  new_values_json JSON NULL,
  ip_address VARCHAR(64) NULL,
  user_agent VARCHAR(255) NULL,
  created_at DATETIME NOT NULL,
  CONSTRAINT fk_audit_user FOREIGN KEY (user_id) REFERENCES users(id),
  CONSTRAINT fk_audit_company FOREIGN KEY (company_id) REFERENCES companies(id),
  INDEX idx_audit_entity (entity_type, entity_id),
  INDEX idx_audit_company (company_id),
  INDEX idx_audit_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO roles (code, name, created_at, updated_at) VALUES
('super_admin', 'Super Admin', NOW(), NOW()),
('company_admin', 'Admin Firmă', NOW(), NOW()),
('operator', 'Operator', NOW(), NOW());

INSERT INTO forecast_scenarios (code, name, delay_days_inflow, delay_days_outflow, inflow_multiplier, outflow_multiplier, is_default, created_at, updated_at) VALUES
('optimist', 'Optimist', -2, 2, 1.0000, 0.9800, 0, NOW(), NOW()),
('realist', 'Realist', 0, 0, 1.0000, 1.0000, 1, NOW(), NOW()),
('pesimist', 'Pesimist', 7, -1, 0.9500, 1.0200, 0, NOW(), NOW());

INSERT INTO category_groups (company_id, name, type, sort_order, is_system, is_active, created_at, updated_at) VALUES
(NULL, 'Încasări operaționale', 'inflow', 10, 1, 1, NOW(), NOW()),
(NULL, 'Cheltuieli operaționale', 'outflow', 20, 1, 1, NOW(), NOW()),
(NULL, 'Transferuri interne', 'transfer', 30, 1, 1, NOW(), NOW());

INSERT INTO categories (company_id, category_group_id, name, type, is_system, is_editable, sort_order, is_active, created_at, updated_at) VALUES
(NULL, 1, 'Facturi clienți', 'inflow', 1, 0, 10, 1, NOW(), NOW()),
(NULL, 1, 'Abonamente recurente încasate', 'inflow', 1, 0, 20, 1, NOW(), NOW()),
(NULL, 1, 'Încasări diverse', 'inflow', 1, 0, 30, 1, NOW(), NOW()),
(NULL, 2, 'Furnizori', 'outflow', 1, 0, 10, 1, NOW(), NOW()),
(NULL, 2, 'Salarii', 'outflow', 1, 0, 20, 1, NOW(), NOW()),
(NULL, 2, 'Taxe și impozite', 'outflow', 1, 0, 30, 1, NOW(), NOW()),
(NULL, 2, 'Chirii', 'outflow', 1, 0, 40, 1, NOW(), NOW()),
(NULL, 2, 'Utilități', 'outflow', 1, 0, 50, 1, NOW(), NOW()),
(NULL, 2, 'Rate / leasing', 'outflow', 1, 0, 60, 1, NOW(), NOW()),
(NULL, 2, 'Dividende', 'outflow', 1, 0, 70, 1, NOW(), NOW()),
(NULL, 2, 'Abonamente recurente plătite', 'outflow', 1, 0, 80, 1, NOW(), NOW()),
(NULL, 2, 'Plăți diverse', 'outflow', 1, 0, 90, 1, NOW(), NOW()),
(NULL, 3, 'Transfer între conturi', 'transfer', 1, 0, 10, 1, NOW(), NOW()),
(NULL, 3, 'Transfer între firme', 'transfer', 1, 0, 20, 1, NOW(), NOW());

SET FOREIGN_KEY_CHECKS = 1;