CREATE TABLE IF NOT EXISTS plans (
  name VARCHAR(30) PRIMARY KEY,
  price DECIMAL(10,2) NOT NULL,
  analysis_limit INT UNSIGNED NOT NULL,
  user_limit INT UNSIGNED NOT NULL
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS companies (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(200) NOT NULL,
  keywords TEXT NOT NULL,
  plan VARCHAR(30) NOT NULL DEFAULT 'Grátis',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (plan) REFERENCES plans(name)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(150) NOT NULL,
  email VARCHAR(254) NOT NULL UNIQUE,
  password_hash VARCHAR(200) NOT NULL,
  role ENUM('admin','owner','analyst') NOT NULL DEFAULT 'owner',
  active BOOLEAN NOT NULL DEFAULT TRUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (company_id) REFERENCES companies(id)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS sessions (
  token_hash CHAR(64) PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  csrf_token CHAR(64) NOT NULL,
  expires_at DATETIME NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS opportunities (
  id BIGINT UNSIGNED PRIMARY KEY,
  title VARCHAR(250) NOT NULL,
  org VARCHAR(200) NOT NULL,
  source VARCHAR(80) NOT NULL,
  category VARCHAR(100) NOT NULL,
  score INT NOT NULL,
  value DECIMAL(15,2) NOT NULL,
  deadline DATE NOT NULL,
  description TEXT NOT NULL,
  analysis JSON NOT NULL,
  is_demo BOOLEAN NOT NULL DEFAULT TRUE
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS analysis_unlocks (
  company_id BIGINT UNSIGNED NOT NULL,
  opportunity_id BIGINT UNSIGNED NOT NULL,
  period_key VARCHAR(20) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (company_id, opportunity_id),
  FOREIGN KEY (company_id) REFERENCES companies(id),
  FOREIGN KEY (opportunity_id) REFERENCES opportunities(id)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS pipeline (
  company_id BIGINT UNSIGNED NOT NULL,
  opportunity_id BIGINT UNSIGNED NOT NULL,
  stage VARCHAR(40) NOT NULL,
  checklist JSON NOT NULL,
  favorite BOOLEAN NOT NULL DEFAULT FALSE,
  PRIMARY KEY (company_id, opportunity_id),
  FOREIGN KEY (company_id) REFERENCES companies(id),
  FOREIGN KEY (opportunity_id) REFERENCES opportunities(id)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS settings (
  setting_key VARCHAR(80) PRIMARY KEY,
  value JSON NOT NULL
) ENGINE=InnoDB;