CREATE TABLE IF NOT EXISTS companies (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(200) NOT NULL,
  email VARCHAR(150) NULL,
  phone VARCHAR(50) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL DEFAULT 1,
  name VARCHAR(150) NOT NULL,
  email VARCHAR(150) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('administrator','manager','user','readonly') NOT NULL DEFAULT 'administrator',
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS clients (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL DEFAULT 1,
  company_name VARCHAR(200) NOT NULL,
  contact_name VARCHAR(150) NULL,
  phone VARCHAR(50) NULL,
  email VARCHAR(150) NULL,
  address VARCHAR(255) NULL,
  city VARCHAR(100) NULL,
  state VARCHAR(50) NULL,
  zip VARCHAR(20) NULL,
  tax_id VARCHAR(50) NULL,
  notes TEXT NULL,
  status VARCHAR(30) NOT NULL DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_clients_company (company_id),
  INDEX idx_clients_company_name (company_name(100)),
  INDEX idx_clients_contact_name (contact_name(100)),
  INDEX idx_clients_tax_id (tax_id(30))
);

CREATE TABLE IF NOT EXISTS categories (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL DEFAULT 1,
  name VARCHAR(150) NOT NULL,
  type ENUM('income','expense','transfer') NOT NULL DEFAULT 'expense',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_category (company_id, name)
);

CREATE TABLE IF NOT EXISTS category_rules (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL DEFAULT 1,
  keyword VARCHAR(200) NOT NULL,
  category_id BIGINT UNSIGNED NOT NULL,
  confidence INT NOT NULL DEFAULT 90,
  times_used INT NOT NULL DEFAULT 0,
  last_used_at DATETIME NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_rules_keyword (company_id, keyword)
);

CREATE TABLE IF NOT EXISTS statements (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL DEFAULT 1,
  client_id BIGINT UNSIGNED NOT NULL,
  bank_name VARCHAR(150) NULL,
  account_name VARCHAR(150) NULL,
  statement_month TINYINT UNSIGNED NOT NULL,
  statement_year SMALLINT UNSIGNED NOT NULL,
  original_pdf VARCHAR(500) NOT NULL,
  opening_balance DECIMAL(12,2) NULL,
  closing_balance DECIMAL(12,2) NULL,
  status ENUM('uploaded','reading_pdf','categorizing','needs_review','approved','completed') NOT NULL DEFAULT 'uploaded',
  processed_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_statement_client_period (client_id, statement_year, statement_month)
);

CREATE TABLE IF NOT EXISTS transactions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL DEFAULT 1,
  statement_id BIGINT UNSIGNED NOT NULL,
  transaction_date DATE NOT NULL,
  description VARCHAR(500) NOT NULL,
  debit DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  credit DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  balance DECIMAL(12,2) NULL,
  category_id BIGINT UNSIGNED NULL,
  status ENUM('auto_categorized','needs_review','reviewed') NOT NULL DEFAULT 'needs_review',
  confidence INT NOT NULL DEFAULT 0,
  notes TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_transactions_statement (statement_id),
  INDEX idx_transactions_date (company_id, transaction_date),
  INDEX idx_transactions_description (description(100))
);

CREATE TABLE IF NOT EXISTS activity_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL DEFAULT 1,
  user_id BIGINT UNSIGNED NULL,
  client_id BIGINT UNSIGNED NULL,
  statement_id BIGINT UNSIGNED NULL,
  action VARCHAR(150) NOT NULL,
  details TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS settings (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL DEFAULT 1,
  setting_key VARCHAR(100) NOT NULL,
  setting_value TEXT NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_settings_key (company_id, setting_key)
);