CREATE TABLE IF NOT EXISTS timeline_history (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT(11) NOT NULL,
  lead_id INT(11) NULL,
  action_type ENUM(
    'user_created', 'user_updated', 'user_deleted',
    'lead_created', 'lead_updated', 'lead_deleted', 
    'email_sent', 'email_opened', 'email_clicked',
    'template_created', 'template_updated', 'template_deleted',
    'smtp_updated', 'settings_updated'
  ) NOT NULL,
  
  -- Change tracking fields
  old_email VARCHAR(255) NULL,
  new_email VARCHAR(255) NULL,
  old_display_name VARCHAR(255) NULL,
  new_display_name VARCHAR(255) NULL,
  old_subject VARCHAR(255) NULL,
  new_subject VARCHAR(255) NULL,
  old_status VARCHAR(50) NULL,
  new_status VARCHAR(50) NULL,
  
  -- Additional info
  description TEXT NULL,
  metadata JSON NULL,
  ip_address VARCHAR(45) NULL,
  user_agent VARCHAR(255) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  
  -- Foreign key constraints
  CONSTRAINT fk_timeline_user_id FOREIGN KEY (user_id) 
    REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_timeline_lead_id FOREIGN KEY (lead_id)
    REFERENCES leads(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Create indexes
CREATE INDEX idx_timeline_user_created ON timeline_history (user_id, created_at);
CREATE INDEX idx_timeline_action_type ON timeline_history (action_type);
CREATE INDEX idx_timeline_lead_id ON timeline_history (lead_id);

DELIMITER $$  -- Set the delimiter to $$ to allow multi-line statements

CREATE TRIGGER `status_after_insert`
AFTER INSERT ON `emails`
FOR EACH ROW
BEGIN
    -- Update the leads table with the new status from emails after an insert
    UPDATE leads
    SET status = NEW.status
    WHERE leads.id = NEW.lead_id;
END$$

DELIMITER ;  -- Reset the delimiter back to the default (;)


DELIMITER $$  -- Set the delimiter to $$ to allow multi-line statements

CREATE TRIGGER `status_after_update`
AFTER UPDATE ON `emails`
FOR EACH ROW
BEGIN
    -- Update the leads table with the new status from emails after an update
    UPDATE leads
    SET status = NEW.status
    WHERE leads.id = NEW.lead_id;
END$$

DELIMITER ;  -- Reset the delimiter back to the default (;)



BEGIN
    UPDATE leads
    SET status = NEW.status
    WHERE leads.id = NEW.lead_id;
END

BEGIN
    UPDATE leads
    SET status = NEW.status
    WHERE leads.id = NEW.lead_id;
END



//new is the column of the table we are taking its value to do the update