CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL UNIQUE,
  password_hash VARCHAR(100) NOT NULL,
  name VARCHAR(100) NOT NULL,
  role ENUM('admin','manager','agent') NOT NULL DEFAULT 'agent',
  active TINYINT NOT NULL DEFAULT 1,
  sip_username VARCHAR(100) NULL,
  status ENUM('offline','ready','paused','on_call','wrap') NOT NULL DEFAULT 'offline',
  status_since DATETIME NULL,
  pause_reason VARCHAR(40) NULL,
  campaign_id INT NULL,
  current_lead_id INT NULL,
  current_call_id INT NULL,
  last_seen DATETIME NULL,
  phone_state ENUM('off','dialing','connected') NOT NULL DEFAULT 'off',
  phone_since DATETIME NULL,
  phone_cc_id VARCHAR(150) NULL,
  conf_id VARCHAR(150) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS campaigns (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  status ENUM('stopped','running') NOT NULL DEFAULT 'stopped',
  caller_id VARCHAR(20) NULL,
  call_start TIME NOT NULL DEFAULT '09:00:00',
  call_end TIME NOT NULL DEFAULT '20:00:00',
  timezone VARCHAR(50) NOT NULL DEFAULT 'America/New_York',
  days_allowed VARCHAR(20) NOT NULL DEFAULT '1,2,3,4,5',
  blocked_states VARCHAR(255) NOT NULL DEFAULT '',
  max_attempts INT NOT NULL DEFAULT 5,
  retry_minutes INT NOT NULL DEFAULT 120,
  dial_level DECIMAL(3,1) NOT NULL DEFAULT 1.0,
  record TINYINT NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS lists (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  campaign_id INT NOT NULL,
  active TINYINT NOT NULL DEFAULT 1,
  source VARCHAR(80) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS leads (
  id INT AUTO_INCREMENT PRIMARY KEY,
  campaign_id INT NOT NULL,
  list_id INT NOT NULL,
  phone VARCHAR(20) NOT NULL,
  state VARCHAR(10) NULL,
  data MEDIUMTEXT NULL,
  search_text VARCHAR(400) NULL,
  status ENUM('new','dialing','callback','done','dnc','bad') NOT NULL DEFAULT 'new',
  attempts INT NOT NULL DEFAULT 0,
  last_disposition VARCHAR(30) NULL,
  next_attempt_at DATETIME NULL,
  last_call_at DATETIME NULL,
  reserved_by INT NULL,
  reserved_at DATETIME NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_camp_phone (campaign_id, phone),
  KEY idx_pick (campaign_id, status, next_attempt_at),
  KEY idx_list (list_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS lead_fields (
  id INT AUTO_INCREMENT PRIMARY KEY,
  field_key VARCHAR(40) NOT NULL UNIQUE,
  label VARCHAR(60) NOT NULL,
  sort INT NOT NULL DEFAULT 0,
  active TINYINT NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS dispositions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(30) NOT NULL UNIQUE,
  label VARCHAR(60) NOT NULL,
  action ENUM('final','retry','callback','bad','dnc') NOT NULL,
  retry_minutes INT NULL,
  recyclable TINYINT NOT NULL DEFAULT 0,
  hotkey CHAR(1) NULL,
  agent_visible TINYINT NOT NULL DEFAULT 1,
  sort INT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS calls (
  id INT AUTO_INCREMENT PRIMARY KEY,
  lead_id INT NOT NULL,
  campaign_id INT NOT NULL,
  agent_id INT NULL,
  type ENUM('auto','manual') NOT NULL,
  status VARCHAR(20) NOT NULL,
  cc_id VARCHAR(150) NULL,
  conf_id VARCHAR(150) NULL,
  started_at DATETIME NOT NULL,
  answered_at DATETIME NULL,
  ended_at DATETIME NULL,
  duration INT NOT NULL DEFAULT 0,
  disposition_code VARCHAR(30) NULL,
  notes TEXT NULL,
  rec_on TINYINT NOT NULL DEFAULT 0,
  recording_id VARCHAR(100) NULL,
  hangup_cause VARCHAR(60) NULL,
  KEY idx_cc (cc_id),
  KEY idx_conf (conf_id),
  KEY idx_lead (lead_id),
  KEY idx_camp_time (campaign_id, started_at),
  KEY idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS dnc (
  phone VARCHAR(20) PRIMARY KEY,
  added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS daily_stats (
  campaign_id INT NOT NULL,
  day DATE NOT NULL,
  dialed INT NOT NULL DEFAULT 0,
  answered INT NOT NULL DEFAULT 0,
  abandoned INT NOT NULL DEFAULT 0,
  no_answer INT NOT NULL DEFAULT 0,
  PRIMARY KEY (campaign_id, day)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS agent_time (
  user_id INT NOT NULL,
  day DATE NOT NULL,
  ready_s INT NOT NULL DEFAULT 0,
  paused_s INT NOT NULL DEFAULT 0,
  on_call_s INT NOT NULL DEFAULT 0,
  wrap_s INT NOT NULL DEFAULT 0,
  PRIMARY KEY (user_id, day)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS agent_events (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  event VARCHAR(40) NOT NULL,
  detail VARCHAR(200) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY idx_user_time (user_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS admin_log (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  action VARCHAR(60) NOT NULL,
  detail VARCHAR(500) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO lead_fields (field_key,label,sort) VALUES
('company','Company',1),('first_name','First name',2),('last_name','Last name',3),('title','Title',4),
('alt_phone','Alt phone',5),('email','Email',6),('address1','Address 1',7),('address2','Address 2',8),
('city','City',9),('state','State',10),('zip','Zip',11),('country','Country',12),
('industry','Industry',13),('monthly_revenue','Monthly revenue',14),('funding_needed','Funding needed',15),
('time_in_business','Time in business',16),('website','Website',17),('source','Source',18),
('vendor_code','Vendor lead code',19),('comments','Comments',99);

INSERT IGNORE INTO dispositions (code,label,action,retry_minutes,recyclable,hotkey,agent_visible,sort) VALUES
('INTERESTED','Interested','final',NULL,0,'1',1,1),
('CALLBACK','Call back','callback',NULL,0,'2',1,2),
('NOT_INTERESTED','Not interested','final',NULL,0,'3',1,3),
('NOT_AVAILABLE','Owner not available','retry',240,1,'4',1,4),
('VOICEMAIL','Voicemail','retry',240,1,'5',1,5),
('APP_OUT','Apps out','final',NULL,0,'6',1,6),
('WRONG_NUMBER','Wrong number','bad',NULL,0,'7',1,7),
('DNC','Do not call','dnc',NULL,0,'8',1,8),
('NO_ANSWER','No answer','retry',120,1,NULL,0,90),
('ABANDONED','No agent free','retry',5,1,NULL,0,91),
('DIAL_FAILED','Dial failed','retry',30,1,NULL,0,92);