-- sql/schema.sql
CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('admin','operador') NOT NULL DEFAULT 'admin',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS employees (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  doc_id VARCHAR(30),
  phone VARCHAR(30),
  start_date DATE,
  end_date DATE NULL,
  position VARCHAR(80),
  base_salary DECIMAL(12,2) NOT NULL DEFAULT 0,
  commission_type VARCHAR(50) DEFAULT '0.0',
  pay_type VARCHAR(50),
  bank VARCHAR(80),
  bank_doc VARCHAR(30),
  bank_phone VARCHAR(30),
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS payrolls (
  id INT AUTO_INCREMENT PRIMARY KEY,
  period_start DATE NOT NULL,
  period_end DATE NOT NULL,
  fx_rate DECIMAL(12,4) NOT NULL DEFAULT 1.0000,
  notes TEXT,
  total_usd DECIMAL(14,2) DEFAULT 0,
  total_cash DECIMAL(14,2) DEFAULT 0,
  total_transfer DECIMAL(14,2) DEFAULT 0,
  total_payroll DECIMAL(14,2) DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS payroll_lines (
  id INT AUTO_INCREMENT PRIMARY KEY,
  payroll_id INT NOT NULL,
  employee_id INT NOT NULL,
  salary DECIMAL(12,2) NOT NULL DEFAULT 0,
  commission DECIMAL(12,2) NOT NULL DEFAULT 0,
  bonus DECIMAL(12,2) NOT NULL DEFAULT 0,
  discount DECIMAL(12,2) NOT NULL DEFAULT 0,
  pay_usd DECIMAL(12,2) NOT NULL DEFAULT 0,
  pay_cash DECIMAL(12,2) NOT NULL DEFAULT 0,
  pay_transfer DECIMAL(12,2) NOT NULL DEFAULT 0,
  line_total DECIMAL(12,2) NOT NULL DEFAULT 0,
  note VARCHAR(255),
  FOREIGN KEY (payroll_id) REFERENCES payrolls(id) ON DELETE CASCADE,
  FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS cash_counts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  employees_base INT DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS cash_count_rows (
  id INT AUTO_INCREMENT PRIMARY KEY,
  cash_id INT NOT NULL,
  bill INT NOT NULL,
  qty INT NOT NULL DEFAULT 0,
  division DECIMAL(12,2) NOT NULL DEFAULT 0,
  total DECIMAL(12,2) NOT NULL DEFAULT 0,
  FOREIGN KEY (cash_id) REFERENCES cash_counts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
