CREATE DATABASE callvoxx CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE callvoxx;

CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  status ENUM('logged_in','ready','paused') DEFAULT 'ready',
  performance_score INT DEFAULT 0,
  aht_minutes INT DEFAULT 3,
  email VARCHAR(120) DEFAULT NULL,
  group_id INT DEFAULT NULL
);

CREATE TABLE IF NOT EXISTS user_groups (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE IF NOT EXISTS campaigns (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  active TINYINT(1) DEFAULT 1,
  dial_level VARCHAR(30) DEFAULT 'MANUAL',
  dial_speed DECIMAL(4,1) DEFAULT 1.0,
  hopper_level INT DEFAULT 100,
  total_leads INT DEFAULT 0
);

CREATE TABLE IF NOT EXISTS lists (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  campaign_id INT,
  active TINYINT(1) DEFAULT 1
);

CREATE TABLE IF NOT EXISTS leads (
  id INT AUTO_INCREMENT PRIMARY KEY,
  list_id INT,
  full_name VARCHAR(120),
  phone VARCHAR(30),
  status VARCHAR(40) DEFAULT 'NEW',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS calls (
  id INT AUTO_INCREMENT PRIMARY KEY,
  type ENUM('inbound','outbound') NOT NULL,
  status VARCHAR(40) DEFAULT 'ringing',
  hour_of_day INT DEFAULT 0,
  duration_sec INT DEFAULT 0,
  agent_id INT DEFAULT NULL
);

CREATE TABLE IF NOT EXISTS dispositions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  type VARCHAR(60) NOT NULL,
  count INT DEFAULT 0
);

CREATE TABLE IF NOT EXISTS scripts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  content TEXT
);

CREATE TABLE IF NOT EXISTS recordings (
  id INT AUTO_INCREMENT PRIMARY KEY,
  agent VARCHAR(120),
  customer VARCHAR(120),
  duration_sec INT DEFAULT 0,
  file_path VARCHAR(255) DEFAULT ''
);

CREATE TABLE IF NOT EXISTS admins (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(80) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO user_groups(name) VALUES ('Sales'), ('Support'), ('QA');

INSERT INTO users(name,status,performance_score,aht_minutes,email,group_id) VALUES
('Agent A','logged_in',45,3,'a@example.com',1),
('Agent B','ready',30,4,'b@example.com',1),
('Agent C','paused',50,2,'c@example.com',2),
('Agent D','ready',25,6,'d@example.com',3);

INSERT INTO campaigns(name,active,dial_level,dial_speed,hopper_level,total_leads) VALUES
('Outbound',1,'RATIO',1.0,100,100748),
('Inbound',1,'INBOUND_MAN',1.0,1,0),
('Ingroup',1,'MANUAL',0,1,0),
('Progressive Camp',1,'MANUAL',0,100,0);

INSERT INTO lists(name,campaign_id,active) VALUES
('Default List 1',1,1),
('Default List 2',1,1),
('Inbound List',2,1);

INSERT INTO leads(list_id,full_name,phone,status) VALUES
(1,'John Doe','9991112222','NEW'),
(1,'Jane Smith','9992223333','CALLBACK'),
(2,'Ravi Kumar','9993334444','RINGING'),
(3,'Sita Iyer','9994445555','PTP');

INSERT INTO dispositions(type,count) VALUES
('PTP',50),('Not Answer',120),('Ringing',90),('Callback',40),('Not Paying',30),('Dispute',20),('Settlement',25);

-- basic call seeds (sample)
INSERT INTO calls(type,status,hour_of_day,duration_sec,agent_id) VALUES
('inbound','completed',9,120,NULL),
('outbound','completed',9,240,NULL),
('inbound','completed',10,60,NULL),
('outbound','completed',11,90,NULL);

-- IMPORTANT: Admin seeding
-- Replace 'REPLACE_WITH_HASH' with a real password hash produced by PHP password_hash('YourStrongPassword', PASSWORD_DEFAULT)
INSERT INTO admins(username,password_hash) VALUES ('admin', 'Test@123');