-- ============================================================================
--  OutreachPilot - pelny schemat bazy (MySQL 8.0+ / MariaDB 10.6+, InnoDB)
--  Kodowanie utf8mb4. Wszystkie znaczniki czasu w UTC.
--  Uruchomienie: mysql -u op_user -p outreachpilot < database/schema.sql
-- ============================================================================
SET NAMES utf8mb4;
SET time_zone = '+00:00';
SET FOREIGN_KEY_CHECKS = 0;

-- ---------------------------------------------------------------------------
-- 1. PLANY, ZESPOLY, UZYTKOWNICY
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS plans (
  id                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  code              VARCHAR(32)  NOT NULL,
  name              VARCHAR(64)  NOT NULL,
  price_monthly     DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  price_yearly      DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  currency          CHAR(3)      NOT NULL DEFAULT 'PLN',
  limit_leads_month     INT UNSIGNED NOT NULL DEFAULT 100,
  limit_emails_day      INT UNSIGNED NOT NULL DEFAULT 50,
  limit_emails_month    INT UNSIGNED NOT NULL DEFAULT 1000,
  limit_email_accounts  SMALLINT UNSIGNED NOT NULL DEFAULT 1,
  limit_campaigns       SMALLINT UNSIGNED NOT NULL DEFAULT 1,
  limit_team_members    SMALLINT UNSIGNED NOT NULL DEFAULT 1,
  limit_ai_credits      INT UNSIGNED NOT NULL DEFAULT 0,
  features          JSON NULL,
  is_public         TINYINT(1) NOT NULL DEFAULT 1,
  sort_order        SMALLINT NOT NULL DEFAULT 0,
  created_at        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_plans_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS teams (
  id                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid              CHAR(36) NOT NULL,
  name              VARCHAR(160) NOT NULL,
  slug              VARCHAR(160) NOT NULL,
  owner_user_id     BIGINT UNSIGNED NULL,
  plan_id           BIGINT UNSIGNED NULL,
  company_name      VARCHAR(200) NULL,
  nip               VARCHAR(20)  NULL,
  address_street    VARCHAR(200) NULL,
  address_zip       VARCHAR(12)  NULL,
  address_city      VARCHAR(120) NULL,
  country           CHAR(2) NOT NULL DEFAULT 'PL',
  default_signature TEXT NULL,
  default_footer    TEXT NULL,
  timezone          VARCHAR(64) NOT NULL DEFAULT 'Europe/Warsaw',
  data_retention_days SMALLINT UNSIGNED NOT NULL DEFAULT 730,
  trial_ends_at     DATETIME NULL,
  created_at        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at        DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_teams_uuid (uuid),
  UNIQUE KEY uq_teams_slug (slug),
  KEY idx_teams_plan (plan_id),
  CONSTRAINT fk_teams_plan FOREIGN KEY (plan_id) REFERENCES plans (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS users (
  id                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid              CHAR(36) NOT NULL,
  team_id           BIGINT UNSIGNED NULL,
  email             VARCHAR(190) NOT NULL,
  email_verified_at DATETIME NULL,
  password_hash     VARCHAR(255) NOT NULL,
  first_name        VARCHAR(80) NULL,
  last_name         VARCHAR(80) NULL,
  phone             VARCHAR(32) NULL,
  avatar_path       VARCHAR(255) NULL,
  locale            VARCHAR(8) NOT NULL DEFAULT 'pl',
  role              ENUM('owner','admin','member','viewer','superadmin') NOT NULL DEFAULT 'owner',
  status            ENUM('active','invited','suspended','deleted') NOT NULL DEFAULT 'active',
  twofa_secret      VARBINARY(255) NULL,
  twofa_enabled_at  DATETIME NULL,
  referral_code     VARCHAR(24) NULL,
  referred_by_user_id BIGINT UNSIGNED NULL,
  last_login_at     DATETIME NULL,
  last_login_ip     VARBINARY(16) NULL,
  failed_logins     SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  locked_until      DATETIME NULL,
  notification_prefs JSON NULL,
  created_at        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at        DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_users_email (email),
  UNIQUE KEY uq_users_uuid (uuid),
  UNIQUE KEY uq_users_referral (referral_code),
  KEY idx_users_team (team_id),
  KEY idx_users_status (status),
  CONSTRAINT fk_users_team FOREIGN KEY (team_id) REFERENCES teams (id) ON DELETE SET NULL,
  CONSTRAINT fk_users_referrer FOREIGN KEY (referred_by_user_id) REFERENCES users (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE teams ADD CONSTRAINT fk_teams_owner FOREIGN KEY (owner_user_id) REFERENCES users (id) ON DELETE SET NULL;

CREATE TABLE IF NOT EXISTS team_members (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  team_id       BIGINT UNSIGNED NOT NULL,
  user_id       BIGINT UNSIGNED NOT NULL,
  role          ENUM('owner','admin','member','viewer') NOT NULL DEFAULT 'member',
  invited_by    BIGINT UNSIGNED NULL,
  invite_token  VARCHAR(64) NULL,
  invite_expires_at DATETIME NULL,
  accepted_at   DATETIME NULL,
  created_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_team_user (team_id, user_id),
  KEY idx_tm_user (user_id),
  CONSTRAINT fk_tm_team FOREIGN KEY (team_id) REFERENCES teams (id) ON DELETE CASCADE,
  CONSTRAINT fk_tm_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS sessions (
  id            CHAR(64) NOT NULL,
  user_id       BIGINT UNSIGNED NULL,
  ip_address    VARBINARY(16) NULL,
  user_agent    VARCHAR(255) NULL,
  payload       MEDIUMTEXT NULL,
  last_activity INT UNSIGNED NOT NULL,
  PRIMARY KEY (id),
  KEY idx_sessions_user (user_id),
  KEY idx_sessions_activity (last_activity)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS password_resets (
  email      VARCHAR(190) NOT NULL,
  token      VARCHAR(255) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (email),
  KEY idx_pr_token (token)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS api_tokens (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  team_id     BIGINT UNSIGNED NOT NULL,
  user_id     BIGINT UNSIGNED NOT NULL,
  name        VARCHAR(120) NOT NULL,
  token_hash  CHAR(64) NOT NULL,
  abilities   JSON NULL,
  last_used_at DATETIME NULL,
  expires_at  DATETIME NULL,
  revoked_at  DATETIME NULL,
  created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_api_token (token_hash),
  KEY idx_api_team (team_id),
  CONSTRAINT fk_api_team FOREIGN KEY (team_id) REFERENCES teams (id) ON DELETE CASCADE,
  CONSTRAINT fk_api_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------------
-- 2. PLATNOSCI I LIMITY
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS subscriptions (
  id                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  team_id           BIGINT UNSIGNED NOT NULL,
  plan_id           BIGINT UNSIGNED NOT NULL,
  status            ENUM('trialing','active','past_due','canceled','expired') NOT NULL DEFAULT 'trialing',
  billing_period    ENUM('monthly','yearly') NOT NULL DEFAULT 'monthly',
  provider          ENUM('przelewy24','stripe','paynow','manual') NOT NULL DEFAULT 'przelewy24',
  provider_customer_id VARCHAR(120) NULL,
  provider_subscription_id VARCHAR(120) NULL,
  current_period_start DATETIME NULL,
  current_period_end   DATETIME NULL,
  cancel_at_period_end TINYINT(1) NOT NULL DEFAULT 0,
  canceled_at       DATETIME NULL,
  created_at        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_sub_team_status (team_id, status),
  KEY idx_sub_period_end (current_period_end),
  CONSTRAINT fk_sub_team FOREIGN KEY (team_id) REFERENCES teams (id) ON DELETE CASCADE,
  CONSTRAINT fk_sub_plan FOREIGN KEY (plan_id) REFERENCES plans (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS invoices (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  team_id       BIGINT UNSIGNED NOT NULL,
  subscription_id BIGINT UNSIGNED NULL,
  number        VARCHAR(40) NOT NULL,
  status        ENUM('draft','issued','paid','failed','refunded','canceled') NOT NULL DEFAULT 'draft',
  net_amount    DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  vat_rate      DECIMAL(5,2)  NOT NULL DEFAULT 23.00,
  vat_amount    DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  gross_amount  DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  currency      CHAR(3) NOT NULL DEFAULT 'PLN',
  buyer_snapshot JSON NULL,
  issued_at     DATE NULL,
  due_at        DATE NULL,
  paid_at       DATETIME NULL,
  pdf_path      VARCHAR(255) NULL,
  created_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_invoice_number (number),
  KEY idx_inv_team (team_id, status),
  CONSTRAINT fk_inv_team FOREIGN KEY (team_id) REFERENCES teams (id) ON DELETE CASCADE,
  CONSTRAINT fk_inv_sub FOREIGN KEY (subscription_id) REFERENCES subscriptions (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS payments (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  team_id       BIGINT UNSIGNED NOT NULL,
  invoice_id    BIGINT UNSIGNED NULL,
  provider      VARCHAR(32) NOT NULL,
  provider_payment_id VARCHAR(120) NULL,
  amount        DECIMAL(10,2) NOT NULL,
  currency      CHAR(3) NOT NULL DEFAULT 'PLN',
  status        ENUM('pending','succeeded','failed','refunded') NOT NULL DEFAULT 'pending',
  method        VARCHAR(40) NULL,
  payload       JSON NULL,
  created_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_pay_team (team_id, status),
  KEY idx_pay_provider (provider, provider_payment_id),
  CONSTRAINT fk_pay_team FOREIGN KEY (team_id) REFERENCES teams (id) ON DELETE CASCADE,
  CONSTRAINT fk_pay_invoice FOREIGN KEY (invoice_id) REFERENCES invoices (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS usage_counters (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  team_id       BIGINT UNSIGNED NOT NULL,
  period        CHAR(7) NOT NULL,
  metric        ENUM('leads','emails','ai_credits','exports','searches') NOT NULL,
  used          INT UNSIGNED NOT NULL DEFAULT 0,
  quota         INT UNSIGNED NOT NULL DEFAULT 0,
  updated_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_usage (team_id, period, metric),
  CONSTRAINT fk_usage_team FOREIGN KEY (team_id) REFERENCES teams (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------------
-- 3. BAZA FIRM
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS companies (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  source          ENUM('ceidg','krs','google_maps','pkt','panorama','website','manual','import') NOT NULL,
  source_ref      VARCHAR(120) NULL,
  legal_form      ENUM('jdg','sp_zoo','sa','sp_k','sp_j','sp_p','spolka_cywilna','fundacja','stowarzyszenie','inne') NULL,
  name            VARCHAR(255) NOT NULL,
  name_normalized VARCHAR(255) NULL,
  nip             VARCHAR(20) NULL,
  regon           VARCHAR(20) NULL,
  krs             VARCHAR(20) NULL,
  pkd_main        VARCHAR(10) NULL,
  pkd_all         JSON NULL,
  industry        VARCHAR(120) NULL,
  owner_first_name VARCHAR(80) NULL,
  owner_last_name  VARCHAR(80) NULL,
  email           VARCHAR(190) NULL,
  email_status    ENUM('unknown','found','verified','invalid','risky') NOT NULL DEFAULT 'unknown',
  email_source    VARCHAR(64) NULL,
  phone           VARCHAR(32) NULL,
  website         VARCHAR(255) NULL,
  has_website     TINYINT(1) NOT NULL DEFAULT 0,
  website_checked_at DATETIME NULL,
  website_quality ENUM('none','parked','outdated','ok','modern') NULL,
  street          VARCHAR(200) NULL,
  zip             VARCHAR(12) NULL,
  city            VARCHAR(120) NULL,
  county          VARCHAR(120) NULL,
  voivodeship     VARCHAR(60) NULL,
  latitude        DECIMAL(10,7) NULL,
  longitude       DECIMAL(10,7) NULL,
  registered_at   DATE NULL,
  status_registry ENUM('active','suspended','closed','unknown') NOT NULL DEFAULT 'unknown',
  employees_range VARCHAR(20) NULL,
  rating          DECIMAL(3,2) NULL,
  reviews_count   INT UNSIGNED NULL,
  raw_payload     JSON NULL,
  verified_at     DATETIME NULL,
  created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_companies_source (source, source_ref),
  KEY idx_companies_nip (nip),
  KEY idx_companies_regon (regon),
  KEY idx_companies_geo (voivodeship, city),
  KEY idx_companies_pkd (pkd_main),
  KEY idx_companies_registered (registered_at),
  KEY idx_companies_filter (has_website, email_status, voivodeship),
  KEY idx_companies_email (email),
  FULLTEXT KEY ft_companies (name, industry, city)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS company_search_queries (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  team_id       BIGINT UNSIGNED NOT NULL,
  user_id       BIGINT UNSIGNED NULL,
  name          VARCHAR(160) NULL,
  filters       JSON NOT NULL,
  results_count INT UNSIGNED NOT NULL DEFAULT 0,
  is_saved      TINYINT(1) NOT NULL DEFAULT 0,
  created_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_csq_team (team_id, is_saved),
  CONSTRAINT fk_csq_team FOREIGN KEY (team_id) REFERENCES teams (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS monitors (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  team_id       BIGINT UNSIGNED NOT NULL,
  user_id       BIGINT UNSIGNED NOT NULL,
  name          VARCHAR(160) NOT NULL,
  filters       JSON NOT NULL,
  frequency     ENUM('hourly','daily','weekly') NOT NULL DEFAULT 'daily',
  target_lead_list_id BIGINT UNSIGNED NULL,
  auto_add      TINYINT(1) NOT NULL DEFAULT 0,
  notify_email  TINYINT(1) NOT NULL DEFAULT 1,
  is_active     TINYINT(1) NOT NULL DEFAULT 1,
  last_run_at   DATETIME NULL,
  next_run_at   DATETIME NULL,
  found_total   INT UNSIGNED NOT NULL DEFAULT 0,
  created_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_mon_run (is_active, next_run_at),
  CONSTRAINT fk_mon_team FOREIGN KEY (team_id) REFERENCES teams (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS monitor_results (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  monitor_id  BIGINT UNSIGNED NOT NULL,
  company_id  BIGINT UNSIGNED NOT NULL,
  found_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  added_to_list TINYINT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  UNIQUE KEY uq_monitor_company (monitor_id, company_id),
  CONSTRAINT fk_mr_monitor FOREIGN KEY (monitor_id) REFERENCES monitors (id) ON DELETE CASCADE,
  CONSTRAINT fk_mr_company FOREIGN KEY (company_id) REFERENCES companies (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------------
-- 4. LEADY
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS lead_lists (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  team_id       BIGINT UNSIGNED NOT NULL,
  user_id       BIGINT UNSIGNED NULL,
  name          VARCHAR(160) NOT NULL,
  description   VARCHAR(500) NULL,
  color         CHAR(7) NULL,
  leads_count   INT UNSIGNED NOT NULL DEFAULT 0,
  created_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at    DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_ll_team (team_id),
  CONSTRAINT fk_ll_team FOREIGN KEY (team_id) REFERENCES teams (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS leads (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  team_id         BIGINT UNSIGNED NOT NULL,
  company_id      BIGINT UNSIGNED NULL,
  owner_user_id   BIGINT UNSIGNED NULL,
  company_name    VARCHAR(255) NOT NULL,
  first_name      VARCHAR(80) NULL,
  last_name       VARCHAR(80) NULL,
  position        VARCHAR(120) NULL,
  email           VARCHAR(190) NULL,
  email_hash      CHAR(64) NULL,
  email_status    ENUM('unknown','valid','invalid','risky','bounced') NOT NULL DEFAULT 'unknown',
  phone           VARCHAR(32) NULL,
  website         VARCHAR(255) NULL,
  city            VARCHAR(120) NULL,
  voivodeship     VARCHAR(60) NULL,
  industry        VARCHAR(120) NULL,
  pkd             VARCHAR(10) NULL,
  nip             VARCHAR(20) NULL,
  source          VARCHAR(40) NOT NULL DEFAULT 'manual',
  status          ENUM('new','queued','contacted','opened','replied','interested','not_interested','bounced','unsubscribed','customer','disqualified') NOT NULL DEFAULT 'new',
  score           TINYINT UNSIGNED NULL,
  ai_summary      TEXT NULL,
  custom_fields   JSON NULL,
  last_contacted_at DATETIME NULL,
  last_reply_at   DATETIME NULL,
  consent_basis   ENUM('none','legitimate_interest','consent','existing_client') NOT NULL DEFAULT 'legitimate_interest',
  created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at      DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_lead_team_email (team_id, email_hash),
  KEY idx_leads_team_status (team_id, status),
  KEY idx_leads_company (company_id),
  KEY idx_leads_last_contact (team_id, last_contacted_at),
  FULLTEXT KEY ft_leads (company_name, first_name, last_name, email),
  CONSTRAINT fk_leads_team FOREIGN KEY (team_id) REFERENCES teams (id) ON DELETE CASCADE,
  CONSTRAINT fk_leads_company FOREIGN KEY (company_id) REFERENCES companies (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS lead_list_items (
  lead_list_id  BIGINT UNSIGNED NOT NULL,
  lead_id       BIGINT UNSIGNED NOT NULL,
  added_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (lead_list_id, lead_id),
  KEY idx_lli_lead (lead_id),
  CONSTRAINT fk_lli_list FOREIGN KEY (lead_list_id) REFERENCES lead_lists (id) ON DELETE CASCADE,
  CONSTRAINT fk_lli_lead FOREIGN KEY (lead_id) REFERENCES leads (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS tags (
  id        BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  team_id   BIGINT UNSIGNED NOT NULL,
  name      VARCHAR(60) NOT NULL,
  color     CHAR(7) NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_tag (team_id, name),
  CONSTRAINT fk_tags_team FOREIGN KEY (team_id) REFERENCES teams (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS lead_tags (
  lead_id BIGINT UNSIGNED NOT NULL,
  tag_id  BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (lead_id, tag_id),
  KEY idx_lt_tag (tag_id),
  CONSTRAINT fk_lt_lead FOREIGN KEY (lead_id) REFERENCES leads (id) ON DELETE CASCADE,
  CONSTRAINT fk_lt_tag FOREIGN KEY (tag_id) REFERENCES tags (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS lead_notes (
  id        BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  lead_id   BIGINT UNSIGNED NOT NULL,
  user_id   BIGINT UNSIGNED NULL,
  body      TEXT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_ln_lead (lead_id),
  CONSTRAINT fk_ln_lead FOREIGN KEY (lead_id) REFERENCES leads (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS imports (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  team_id       BIGINT UNSIGNED NOT NULL,
  user_id       BIGINT UNSIGNED NULL,
  type          ENUM('leads_csv','companies_csv','suppression_csv') NOT NULL DEFAULT 'leads_csv',
  file_path     VARCHAR(255) NOT NULL,
  original_name VARCHAR(255) NULL,
  mapping       JSON NULL,
  target_lead_list_id BIGINT UNSIGNED NULL,
  status        ENUM('pending','mapping','processing','done','failed') NOT NULL DEFAULT 'pending',
  rows_total    INT UNSIGNED NOT NULL DEFAULT 0,
  rows_imported INT UNSIGNED NOT NULL DEFAULT 0,
  rows_duplicate INT UNSIGNED NOT NULL DEFAULT 0,
  rows_invalid  INT UNSIGNED NOT NULL DEFAULT 0,
  error_log     TEXT NULL,
  created_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  finished_at   DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_imports_team (team_id, status),
  CONSTRAINT fk_imports_team FOREIGN KEY (team_id) REFERENCES teams (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------------
-- 5. KONTA E-MAIL I DOSTARCZALNOSC
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS email_accounts (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  team_id         BIGINT UNSIGNED NOT NULL,
  user_id         BIGINT UNSIGNED NULL,
  provider        ENUM('gmail_oauth','smtp','microsoft_oauth') NOT NULL DEFAULT 'smtp',
  from_name       VARCHAR(120) NOT NULL,
  from_email      VARCHAR(190) NOT NULL,
  reply_to        VARCHAR(190) NULL,
  smtp_host       VARCHAR(190) NULL,
  smtp_port       SMALLINT UNSIGNED NULL,
  smtp_encryption ENUM('none','ssl','tls') NULL DEFAULT 'tls',
  smtp_username   VARCHAR(190) NULL,
  smtp_password   VARBINARY(512) NULL,
  imap_host       VARCHAR(190) NULL,
  imap_port       SMALLINT UNSIGNED NULL,
  imap_encryption ENUM('none','ssl','tls') NULL DEFAULT 'ssl',
  imap_username   VARCHAR(190) NULL,
  imap_password   VARBINARY(512) NULL,
  imap_folder     VARCHAR(120) NOT NULL DEFAULT 'INBOX',
  imap_last_uid   INT UNSIGNED NULL,
  imap_last_check_at DATETIME NULL,
  oauth_access_token  VARBINARY(2048) NULL,
  oauth_refresh_token VARBINARY(2048) NULL,
  oauth_expires_at    DATETIME NULL,
  daily_limit     SMALLINT UNSIGNED NOT NULL DEFAULT 50,
  sent_today      SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  hourly_limit    SMALLINT UNSIGNED NOT NULL DEFAULT 12,
  send_from_hour  TINYINT UNSIGNED NOT NULL DEFAULT 8,
  send_to_hour    TINYINT UNSIGNED NOT NULL DEFAULT 17,
  send_days       VARCHAR(20) NOT NULL DEFAULT '1,2,3,4,5',
  min_delay_sec   SMALLINT UNSIGNED NOT NULL DEFAULT 90,
  max_delay_sec   SMALLINT UNSIGNED NOT NULL DEFAULT 480,
  warmup_enabled  TINYINT(1) NOT NULL DEFAULT 1,
  warmup_start_at DATE NULL,
  warmup_daily_increment SMALLINT UNSIGNED NOT NULL DEFAULT 5,
  signature       TEXT NULL,
  footer          TEXT NULL,
  status          ENUM('connected','error','paused','disconnected','verifying') NOT NULL DEFAULT 'verifying',
  last_error      VARCHAR(500) NULL,
  health_score    TINYINT UNSIGNED NOT NULL DEFAULT 100,
  bounce_rate_7d  DECIMAL(5,2) NOT NULL DEFAULT 0.00,
  spf_status      ENUM('unknown','pass','fail','missing') NOT NULL DEFAULT 'unknown',
  dkim_status     ENUM('unknown','pass','fail','missing') NOT NULL DEFAULT 'unknown',
  dmarc_status    ENUM('unknown','pass','fail','missing') NOT NULL DEFAULT 'unknown',
  dns_checked_at  DATETIME NULL,
  created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at      DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_ea_team_email (team_id, from_email),
  KEY idx_ea_status (status),
  KEY idx_ea_imap_check (imap_last_check_at),
  CONSTRAINT fk_ea_team FOREIGN KEY (team_id) REFERENCES teams (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS dns_checks (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  email_account_id BIGINT UNSIGNED NOT NULL,
  domain        VARCHAR(190) NOT NULL,
  record_type   ENUM('spf','dkim','dmarc','mx','ptr','blacklist') NOT NULL,
  result        ENUM('pass','fail','missing','warning') NOT NULL,
  raw_value     TEXT NULL,
  recommendation VARCHAR(500) NULL,
  checked_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_dns_account (email_account_id, record_type, checked_at),
  CONSTRAINT fk_dns_account FOREIGN KEY (email_account_id) REFERENCES email_accounts (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------------
-- 6. KAMPANIE, SEKWENCJE, SZABLONY
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS templates (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  team_id     BIGINT UNSIGNED NULL,
  name        VARCHAR(160) NOT NULL,
  industry    VARCHAR(120) NULL,
  subject     VARCHAR(255) NOT NULL,
  body_text   MEDIUMTEXT NOT NULL,
  body_html   MEDIUMTEXT NULL,
  variables   JSON NULL,
  is_system   TINYINT(1) NOT NULL DEFAULT 0,
  used_count  INT UNSIGNED NOT NULL DEFAULT 0,
  created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_tpl_team (team_id),
  CONSTRAINT fk_tpl_team FOREIGN KEY (team_id) REFERENCES teams (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS sequences (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  team_id     BIGINT UNSIGNED NOT NULL,
  name        VARCHAR(160) NOT NULL,
  description VARCHAR(500) NULL,
  is_template TINYINT(1) NOT NULL DEFAULT 0,
  created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_seq_team (team_id),
  CONSTRAINT fk_seq_team FOREIGN KEY (team_id) REFERENCES teams (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS campaigns (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  team_id         BIGINT UNSIGNED NOT NULL,
  user_id         BIGINT UNSIGNED NULL,
  sequence_id     BIGINT UNSIGNED NULL,
  name            VARCHAR(160) NOT NULL,
  goal            VARCHAR(255) NULL,
  status          ENUM('draft','scheduled','running','paused','completed','archived') NOT NULL DEFAULT 'draft',
  send_days       VARCHAR(20) NOT NULL DEFAULT '1,2,3,4,5',
  send_from_hour  TINYINT UNSIGNED NOT NULL DEFAULT 8,
  send_to_hour    TINYINT UNSIGNED NOT NULL DEFAULT 16,
  timezone        VARCHAR(64) NOT NULL DEFAULT 'Europe/Warsaw',
  daily_cap       SMALLINT UNSIGNED NOT NULL DEFAULT 50,
  random_delay_min SMALLINT UNSIGNED NOT NULL DEFAULT 120,
  random_delay_max SMALLINT UNSIGNED NOT NULL DEFAULT 600,
  stop_on_reply   TINYINT(1) NOT NULL DEFAULT 1,
  stop_on_click   TINYINT(1) NOT NULL DEFAULT 0,
  track_opens     TINYINT(1) NOT NULL DEFAULT 1,
  track_clicks    TINYINT(1) NOT NULL DEFAULT 1,
  ab_test_enabled TINYINT(1) NOT NULL DEFAULT 0,
  ab_split        TINYINT UNSIGNED NOT NULL DEFAULT 50,
  leads_total     INT UNSIGNED NOT NULL DEFAULT 0,
  sent_count      INT UNSIGNED NOT NULL DEFAULT 0,
  delivered_count INT UNSIGNED NOT NULL DEFAULT 0,
  open_count      INT UNSIGNED NOT NULL DEFAULT 0,
  click_count     INT UNSIGNED NOT NULL DEFAULT 0,
  reply_count     INT UNSIGNED NOT NULL DEFAULT 0,
  interested_count INT UNSIGNED NOT NULL DEFAULT 0,
  bounce_count    INT UNSIGNED NOT NULL DEFAULT 0,
  unsub_count     INT UNSIGNED NOT NULL DEFAULT 0,
  started_at      DATETIME NULL,
  finished_at     DATETIME NULL,
  created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at      DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_camp_team_status (team_id, status),
  KEY idx_camp_seq (sequence_id),
  CONSTRAINT fk_camp_team FOREIGN KEY (team_id) REFERENCES teams (id) ON DELETE CASCADE,
  CONSTRAINT fk_camp_seq FOREIGN KEY (sequence_id) REFERENCES sequences (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS campaign_email_accounts (
  campaign_id      BIGINT UNSIGNED NOT NULL,
  email_account_id BIGINT UNSIGNED NOT NULL,
  weight           TINYINT UNSIGNED NOT NULL DEFAULT 1,
  PRIMARY KEY (campaign_id, email_account_id),
  CONSTRAINT fk_cea_camp FOREIGN KEY (campaign_id) REFERENCES campaigns (id) ON DELETE CASCADE,
  CONSTRAINT fk_cea_account FOREIGN KEY (email_account_id) REFERENCES email_accounts (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS sequence_steps (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  sequence_id   BIGINT UNSIGNED NOT NULL,
  step_number   TINYINT UNSIGNED NOT NULL,
  variant       CHAR(1) NOT NULL DEFAULT 'A',
  type          ENUM('email','wait','condition','task') NOT NULL DEFAULT 'email',
  delay_days    SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  delay_hours   SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  subject       VARCHAR(255) NULL,
  body_text     MEDIUMTEXT NULL,
  body_html     MEDIUMTEXT NULL,
  ai_prompt     TEXT NULL,
  ai_generated  TINYINT(1) NOT NULL DEFAULT 0,
  attachments   JSON NULL,
  send_as_reply TINYINT(1) NOT NULL DEFAULT 1,
  is_active     TINYINT(1) NOT NULL DEFAULT 1,
  created_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_step (sequence_id, step_number, variant),
  CONSTRAINT fk_step_seq FOREIGN KEY (sequence_id) REFERENCES sequences (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS campaign_leads (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  campaign_id     BIGINT UNSIGNED NOT NULL,
  lead_id         BIGINT UNSIGNED NOT NULL,
  email_account_id BIGINT UNSIGNED NULL,
  variant         CHAR(1) NOT NULL DEFAULT 'A',
  status          ENUM('pending','scheduled','sending','sent','opened','clicked','replied','bounced','unsubscribed','stopped','failed','completed') NOT NULL DEFAULT 'pending',
  current_step    TINYINT UNSIGNED NOT NULL DEFAULT 0,
  next_send_at    DATETIME NULL,
  thread_id       VARCHAR(255) NULL,
  last_sent_at    DATETIME NULL,
  opened_at       DATETIME NULL,
  clicked_at      DATETIME NULL,
  replied_at      DATETIME NULL,
  stop_reason     VARCHAR(120) NULL,
  created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_campaign_lead (campaign_id, lead_id),
  KEY idx_cl_queue (status, next_send_at),
  KEY idx_cl_lead (lead_id),
  KEY idx_cl_campaign_status (campaign_id, status),
  CONSTRAINT fk_cl_camp FOREIGN KEY (campaign_id) REFERENCES campaigns (id) ON DELETE CASCADE,
  CONSTRAINT fk_cl_lead FOREIGN KEY (lead_id) REFERENCES leads (id) ON DELETE CASCADE,
  CONSTRAINT fk_cl_account FOREIGN KEY (email_account_id) REFERENCES email_accounts (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------------
-- 7. WIADOMOSCI, ZDARZENIA, ODPOWIEDZI
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS email_messages (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  team_id         BIGINT UNSIGNED NOT NULL,
  campaign_id     BIGINT UNSIGNED NULL,
  campaign_lead_id BIGINT UNSIGNED NULL,
  sequence_step_id BIGINT UNSIGNED NULL,
  lead_id         BIGINT UNSIGNED NULL,
  email_account_id BIGINT UNSIGNED NULL,
  direction       ENUM('outbound','inbound') NOT NULL DEFAULT 'outbound',
  to_email        VARCHAR(190) NOT NULL,
  from_email      VARCHAR(190) NOT NULL,
  subject         VARCHAR(255) NULL,
  body_text       MEDIUMTEXT NULL,
  body_html       MEDIUMTEXT NULL,
  message_id      VARCHAR(255) NULL,
  in_reply_to     VARCHAR(255) NULL,
  thread_id       VARCHAR(255) NULL,
  tracking_token  CHAR(32) NULL,
  status          ENUM('queued','sending','sent','delivered','bounced','failed','skipped') NOT NULL DEFAULT 'queued',
  error_message   VARCHAR(500) NULL,
  attempts        TINYINT UNSIGNED NOT NULL DEFAULT 0,
  scheduled_at    DATETIME NULL,
  sent_at         DATETIME NULL,
  delivered_at    DATETIME NULL,
  first_opened_at DATETIME NULL,
  open_count      SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  click_count     SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_tracking_token (tracking_token),
  KEY idx_em_team_sent (team_id, sent_at),
  KEY idx_em_campaign (campaign_id, status),
  KEY idx_em_lead (lead_id),
  KEY idx_em_thread (thread_id),
  KEY idx_em_message_id (message_id),
  KEY idx_em_queue (status, scheduled_at),
  CONSTRAINT fk_em_team FOREIGN KEY (team_id) REFERENCES teams (id) ON DELETE CASCADE,
  CONSTRAINT fk_em_camp FOREIGN KEY (campaign_id) REFERENCES campaigns (id) ON DELETE SET NULL,
  CONSTRAINT fk_em_cl FOREIGN KEY (campaign_lead_id) REFERENCES campaign_leads (id) ON DELETE CASCADE,
  CONSTRAINT fk_em_step FOREIGN KEY (sequence_step_id) REFERENCES sequence_steps (id) ON DELETE SET NULL,
  CONSTRAINT fk_em_lead FOREIGN KEY (lead_id) REFERENCES leads (id) ON DELETE SET NULL,
  CONSTRAINT fk_em_account FOREIGN KEY (email_account_id) REFERENCES email_accounts (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS email_events (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  team_id       BIGINT UNSIGNED NOT NULL,
  email_message_id BIGINT UNSIGNED NULL,
  campaign_id   BIGINT UNSIGNED NULL,
  lead_id       BIGINT UNSIGNED NULL,
  type          ENUM('queued','sent','delivered','open','click','reply','bounce_soft','bounce_hard','complaint','unsubscribe','failed') NOT NULL,
  url           VARCHAR(500) NULL,
  ip_address    VARBINARY(16) NULL,
  user_agent    VARCHAR(255) NULL,
  is_bot        TINYINT(1) NOT NULL DEFAULT 0,
  meta          JSON NULL,
  created_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_ev_message_type (email_message_id, type),
  KEY idx_ev_campaign_type_date (campaign_id, type, created_at),
  KEY idx_ev_team_date (team_id, created_at),
  KEY idx_ev_lead (lead_id),
  CONSTRAINT fk_ev_message FOREIGN KEY (email_message_id) REFERENCES email_messages (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS email_replies (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  team_id         BIGINT UNSIGNED NOT NULL,
  email_account_id BIGINT UNSIGNED NOT NULL,
  campaign_id     BIGINT UNSIGNED NULL,
  campaign_lead_id BIGINT UNSIGNED NULL,
  lead_id         BIGINT UNSIGNED NULL,
  parent_message_id BIGINT UNSIGNED NULL,
  from_email      VARCHAR(190) NOT NULL,
  from_name       VARCHAR(160) NULL,
  subject         VARCHAR(255) NULL,
  body_text       MEDIUMTEXT NULL,
  body_html       MEDIUMTEXT NULL,
  imap_uid        INT UNSIGNED NULL,
  message_id      VARCHAR(255) NULL,
  thread_id       VARCHAR(255) NULL,
  classification  ENUM('interested','question','objection','not_interested','autoresponder','out_of_office','wrong_person','delete_data','unsubscribe','manual_review') NULL,
  ai_confidence   DECIMAL(4,3) NULL,
  ai_summary      VARCHAR(500) NULL,
  ai_suggested_reply MEDIUMTEXT NULL,
  sentiment       ENUM('positive','neutral','negative') NULL,
  is_read         TINYINT(1) NOT NULL DEFAULT 0,
  is_archived     TINYINT(1) NOT NULL DEFAULT 0,
  assigned_to     BIGINT UNSIGNED NULL,
  received_at     DATETIME NOT NULL,
  created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_reply_uid (email_account_id, imap_uid),
  KEY idx_rep_team_unread (team_id, is_read, received_at),
  KEY idx_rep_class (team_id, classification),
  KEY idx_rep_lead (lead_id),
  CONSTRAINT fk_rep_team FOREIGN KEY (team_id) REFERENCES teams (id) ON DELETE CASCADE,
  CONSTRAINT fk_rep_account FOREIGN KEY (email_account_id) REFERENCES email_accounts (id) ON DELETE CASCADE,
  CONSTRAINT fk_rep_cl FOREIGN KEY (campaign_lead_id) REFERENCES campaign_leads (id) ON DELETE SET NULL,
  CONSTRAINT fk_rep_parent FOREIGN KEY (parent_message_id) REFERENCES email_messages (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS bounces (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  team_id       BIGINT UNSIGNED NOT NULL,
  email_message_id BIGINT UNSIGNED NULL,
  email         VARCHAR(190) NOT NULL,
  type          ENUM('hard','soft','block','complaint') NOT NULL,
  code          VARCHAR(20) NULL,
  diagnostic    VARCHAR(500) NULL,
  created_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_bounce_team_email (team_id, email),
  CONSTRAINT fk_bounce_team FOREIGN KEY (team_id) REFERENCES teams (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------------
-- 8. ZGODNOSC (RODO / PKE)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS unsubscribes (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  team_id       BIGINT UNSIGNED NOT NULL,
  lead_id       BIGINT UNSIGNED NULL,
  campaign_id   BIGINT UNSIGNED NULL,
  email         VARCHAR(190) NOT NULL,
  email_hash    CHAR(64) NOT NULL,
  scope         ENUM('campaign','team','global') NOT NULL DEFAULT 'team',
  reason        VARCHAR(255) NULL,
  token         CHAR(40) NULL,
  ip_address    VARBINARY(16) NULL,
  created_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_unsub (team_id, email_hash, scope),
  KEY idx_unsub_token (token),
  CONSTRAINT fk_unsub_team FOREIGN KEY (team_id) REFERENCES teams (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS suppressions (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  team_id       BIGINT UNSIGNED NULL,
  value         VARCHAR(190) NOT NULL,
  value_hash    CHAR(64) NOT NULL,
  type          ENUM('email','domain','pattern') NOT NULL DEFAULT 'email',
  reason        ENUM('unsubscribe','bounce','complaint','manual','competitor','rodo_request') NOT NULL DEFAULT 'manual',
  note          VARCHAR(255) NULL,
  created_by    BIGINT UNSIGNED NULL,
  created_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_supp (team_id, value_hash, type),
  KEY idx_supp_lookup (value_hash),
  CONSTRAINT fk_supp_team FOREIGN KEY (team_id) REFERENCES teams (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS consents (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  team_id       BIGINT UNSIGNED NOT NULL,
  lead_id       BIGINT UNSIGNED NULL,
  email         VARCHAR(190) NOT NULL,
  basis         ENUM('legitimate_interest','consent','contract','existing_client') NOT NULL,
  source        VARCHAR(160) NULL,
  evidence      JSON NULL,
  granted_at    DATETIME NULL,
  withdrawn_at  DATETIME NULL,
  created_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_consent_team_email (team_id, email),
  CONSTRAINT fk_consent_team FOREIGN KEY (team_id) REFERENCES teams (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS compliance_logs (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  team_id       BIGINT UNSIGNED NULL,
  event         VARCHAR(80) NOT NULL,
  subject_email VARCHAR(190) NULL,
  lead_id       BIGINT UNSIGNED NULL,
  actor_user_id BIGINT UNSIGNED NULL,
  details       JSON NULL,
  created_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_cl_team_event (team_id, event, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------------
-- 9. AI
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS ai_integrations (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  team_id       BIGINT UNSIGNED NOT NULL,
  provider      ENUM('openai','anthropic','google','mistral','openrouter','local') NOT NULL,
  label         VARCHAR(120) NULL,
  api_key       VARBINARY(1024) NOT NULL,
  model_default VARCHAR(80) NULL,
  model_cheap   VARCHAR(80) NULL,
  is_default    TINYINT(1) NOT NULL DEFAULT 0,
  is_active     TINYINT(1) NOT NULL DEFAULT 1,
  monthly_budget DECIMAL(10,2) NULL,
  spent_month   DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  created_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_ai_team (team_id, is_active),
  CONSTRAINT fk_ai_team FOREIGN KEY (team_id) REFERENCES teams (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ai_agents (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  team_id       BIGINT UNSIGNED NOT NULL,
  name          VARCHAR(160) NOT NULL,
  type          ENUM('company_finder','website_analyzer','lead_scorer','email_finder','message_writer','followup_writer','reply_classifier','reply_responder') NOT NULL,
  instructions  TEXT NULL,
  config        JSON NULL,
  ai_integration_id BIGINT UNSIGNED NULL,
  requires_approval TINYINT(1) NOT NULL DEFAULT 1,
  schedule_cron VARCHAR(40) NULL,
  is_active     TINYINT(1) NOT NULL DEFAULT 1,
  runs_count    INT UNSIGNED NOT NULL DEFAULT 0,
  last_run_at   DATETIME NULL,
  created_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_agent_team (team_id, type),
  CONSTRAINT fk_agent_team FOREIGN KEY (team_id) REFERENCES teams (id) ON DELETE CASCADE,
  CONSTRAINT fk_agent_ai FOREIGN KEY (ai_integration_id) REFERENCES ai_integrations (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ai_agent_runs (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  ai_agent_id   BIGINT UNSIGNED NOT NULL,
  team_id       BIGINT UNSIGNED NOT NULL,
  target_type   ENUM('lead','company','campaign','reply','list') NULL,
  target_id     BIGINT UNSIGNED NULL,
  status        ENUM('queued','running','awaiting_approval','approved','rejected','done','failed') NOT NULL DEFAULT 'queued',
  input         JSON NULL,
  output        MEDIUMTEXT NULL,
  tokens_in     INT UNSIGNED NOT NULL DEFAULT 0,
  tokens_out    INT UNSIGNED NOT NULL DEFAULT 0,
  cost          DECIMAL(10,4) NOT NULL DEFAULT 0.0000,
  duration_ms   INT UNSIGNED NULL,
  error_message VARCHAR(500) NULL,
  approved_by   BIGINT UNSIGNED NULL,
  created_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  finished_at   DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_run_agent (ai_agent_id, status),
  KEY idx_run_target (target_type, target_id),
  KEY idx_run_team_date (team_id, created_at),
  CONSTRAINT fk_run_agent FOREIGN KEY (ai_agent_id) REFERENCES ai_agents (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------------
-- 10. STRONY KAMPANII
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS landing_pages (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  team_id       BIGINT UNSIGNED NOT NULL,
  campaign_id   BIGINT UNSIGNED NULL,
  slug          VARCHAR(160) NOT NULL,
  type          ENUM('offer','contact','booking','thankyou','optout') NOT NULL DEFAULT 'offer',
  title         VARCHAR(200) NOT NULL,
  content_html  MEDIUMTEXT NULL,
  settings      JSON NULL,
  is_published  TINYINT(1) NOT NULL DEFAULT 0,
  visits_count  INT UNSIGNED NOT NULL DEFAULT 0,
  conversions_count INT UNSIGNED NOT NULL DEFAULT 0,
  created_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_lp_slug (slug),
  KEY idx_lp_team (team_id),
  CONSTRAINT fk_lp_team FOREIGN KEY (team_id) REFERENCES teams (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS page_visits (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  landing_page_id BIGINT UNSIGNED NOT NULL,
  lead_id       BIGINT UNSIGNED NULL,
  campaign_id   BIGINT UNSIGNED NULL,
  referrer      VARCHAR(500) NULL,
  ip_address    VARBINARY(16) NULL,
  user_agent    VARCHAR(255) NULL,
  converted     TINYINT(1) NOT NULL DEFAULT 0,
  form_payload  JSON NULL,
  created_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_pv_page_date (landing_page_id, created_at),
  KEY idx_pv_lead (lead_id),
  CONSTRAINT fk_pv_page FOREIGN KEY (landing_page_id) REFERENCES landing_pages (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------------
-- 11. PROGRAM PARTNERSKI
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS referrals (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  referrer_user_id BIGINT UNSIGNED NOT NULL,
  referred_user_id BIGINT UNSIGNED NULL,
  code            VARCHAR(24) NOT NULL,
  clicks          INT UNSIGNED NOT NULL DEFAULT 0,
  signup_at       DATETIME NULL,
  became_paying_at DATETIME NULL,
  status          ENUM('clicked','registered','paying','churned') NOT NULL DEFAULT 'clicked',
  created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_ref_user (referrer_user_id, status),
  KEY idx_ref_code (code),
  CONSTRAINT fk_ref_user FOREIGN KEY (referrer_user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS commissions (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  referral_id   BIGINT UNSIGNED NOT NULL,
  user_id       BIGINT UNSIGNED NOT NULL,
  payment_id    BIGINT UNSIGNED NULL,
  amount        DECIMAL(10,2) NOT NULL,
  rate          DECIMAL(5,2) NOT NULL DEFAULT 20.00,
  status        ENUM('pending','approved','paid','rejected') NOT NULL DEFAULT 'pending',
  period        CHAR(7) NULL,
  paid_at       DATETIME NULL,
  created_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_com_user (user_id, status),
  CONSTRAINT fk_com_ref FOREIGN KEY (referral_id) REFERENCES referrals (id) ON DELETE CASCADE,
  CONSTRAINT fk_com_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS payouts (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id       BIGINT UNSIGNED NOT NULL,
  amount        DECIMAL(10,2) NOT NULL,
  method        ENUM('transfer','invoice') NOT NULL DEFAULT 'transfer',
  iban          VARCHAR(34) NULL,
  status        ENUM('requested','processing','paid','rejected') NOT NULL DEFAULT 'requested',
  note          VARCHAR(255) NULL,
  requested_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  paid_at       DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_payout_user (user_id, status),
  CONSTRAINT fk_payout_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------------
-- 12. INFRASTRUKTURA
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS jobs (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  queue         VARCHAR(64) NOT NULL DEFAULT 'default',
  name          VARCHAR(120) NOT NULL,
  payload       JSON NOT NULL,
  team_id       BIGINT UNSIGNED NULL,
  priority      TINYINT NOT NULL DEFAULT 5,
  attempts      TINYINT UNSIGNED NOT NULL DEFAULT 0,
  max_attempts  TINYINT UNSIGNED NOT NULL DEFAULT 3,
  status        ENUM('pending','reserved','done','failed') NOT NULL DEFAULT 'pending',
  available_at  DATETIME NOT NULL,
  reserved_at   DATETIME NULL,
  reserved_by   VARCHAR(64) NULL,
  finished_at   DATETIME NULL,
  last_error    TEXT NULL,
  created_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_jobs_pick (queue, status, available_at, priority),
  KEY idx_jobs_team (team_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS failed_jobs (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  queue       VARCHAR(64) NOT NULL,
  name        VARCHAR(120) NOT NULL,
  payload     JSON NULL,
  exception   MEDIUMTEXT NULL,
  failed_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_fj_date (failed_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS notifications (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  team_id       BIGINT UNSIGNED NOT NULL,
  user_id       BIGINT UNSIGNED NULL,
  type          VARCHAR(60) NOT NULL,
  title         VARCHAR(200) NOT NULL,
  body          VARCHAR(500) NULL,
  action_url    VARCHAR(255) NULL,
  severity      ENUM('info','success','warning','error') NOT NULL DEFAULT 'info',
  read_at       DATETIME NULL,
  created_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_notif_user (user_id, read_at, created_at),
  CONSTRAINT fk_notif_team FOREIGN KEY (team_id) REFERENCES teams (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS audit_logs (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  team_id       BIGINT UNSIGNED NULL,
  user_id       BIGINT UNSIGNED NULL,
  action        VARCHAR(80) NOT NULL,
  entity_type   VARCHAR(60) NULL,
  entity_id     BIGINT UNSIGNED NULL,
  before_state  JSON NULL,
  after_state   JSON NULL,
  ip_address    VARBINARY(16) NULL,
  user_agent    VARCHAR(255) NULL,
  created_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_audit_team_date (team_id, created_at),
  KEY idx_audit_entity (entity_type, entity_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS settings (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  team_id     BIGINT UNSIGNED NULL,
  `key`       VARCHAR(120) NOT NULL,
  value       TEXT NULL,
  type        ENUM('string','int','bool','json') NOT NULL DEFAULT 'string',
  updated_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_setting (team_id, `key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS rate_limits (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  bucket      VARCHAR(120) NOT NULL,
  hits        INT UNSIGNED NOT NULL DEFAULT 0,
  expires_at  DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_bucket (bucket),
  KEY idx_rl_expires (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------------
-- 13. WIDOKI RAPORTOWE
-- ---------------------------------------------------------------------------
CREATE OR REPLACE VIEW v_campaign_stats AS
SELECT c.id AS campaign_id, c.team_id, c.name, c.status, c.leads_total, c.sent_count, c.delivered_count,
       c.open_count, c.click_count, c.reply_count, c.interested_count, c.bounce_count, c.unsub_count,
       ROUND(100 * c.delivered_count / NULLIF(c.sent_count, 0), 2) AS delivery_rate,
       ROUND(100 * c.open_count / NULLIF(c.delivered_count, 0), 2) AS open_rate,
       ROUND(100 * c.click_count / NULLIF(c.delivered_count, 0), 2) AS click_rate,
       ROUND(100 * c.reply_count / NULLIF(c.delivered_count, 0), 2) AS reply_rate,
       ROUND(100 * c.interested_count / NULLIF(c.reply_count, 0), 2) AS interest_rate,
       ROUND(100 * c.bounce_count / NULLIF(c.sent_count, 0), 2) AS bounce_rate
FROM campaigns c WHERE c.deleted_at IS NULL;

CREATE OR REPLACE VIEW v_mailbox_health AS
SELECT ea.id AS email_account_id, ea.team_id, ea.user_id, ea.from_email, ea.from_name, ea.status,
       ea.daily_limit, ea.sent_today, ea.health_score, ea.bounce_rate_7d, ea.signature,
       ea.spf_status, ea.dkim_status, ea.dmarc_status, ea.imap_last_check_at,
       (SELECT COUNT(*) FROM email_messages m WHERE m.email_account_id = ea.id AND m.sent_at >= (NOW() - INTERVAL 7 DAY)) AS sent_7d,
       (SELECT COUNT(*) FROM email_replies r WHERE r.email_account_id = ea.id AND r.received_at >= (NOW() - INTERVAL 7 DAY)) AS replies_7d
FROM email_accounts ea WHERE ea.deleted_at IS NULL;

CREATE OR REPLACE VIEW v_daily_metrics AS
SELECT e.team_id, DATE(e.created_at) AS day,
       SUM(e.type = 'sent') AS sent, SUM(e.type = 'delivered') AS delivered,
       SUM(e.type = 'open' AND e.is_bot = 0) AS opens, SUM(e.type = 'click') AS clicks,
       SUM(e.type = 'reply') AS replies,
       SUM(e.type IN ('bounce_hard','bounce_soft')) AS bounces,
       SUM(e.type = 'unsubscribe') AS unsubs
FROM email_events e GROUP BY e.team_id, DATE(e.created_at);

SET FOREIGN_KEY_CHECKS = 1;
-- ============================ koniec schematu ==============================