-- Run once in phpMyAdmin for an existing ItuzaFlow installation.
CREATE TABLE IF NOT EXISTS scheduled_emails(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,user_id BIGINT UNSIGNED NOT NULL,lead_id BIGINT UNSIGNED NULL,recipient_email VARCHAR(190) NOT NULL,subject VARCHAR(220) NOT NULL,body MEDIUMTEXT NOT NULL,scheduled_at DATETIME NULL,status ENUM('Draft','Scheduled','Sent','Failed','Cancelled') NOT NULL DEFAULT 'Draft',sent_at DATETIME NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,KEY emails_schedule(company_id,status,scheduled_at),CONSTRAINT emails_company_fk FOREIGN KEY(company_id) REFERENCES companies(id),CONSTRAINT emails_user_fk FOREIGN KEY(user_id) REFERENCES users(id),CONSTRAINT emails_lead_fk FOREIGN KEY(lead_id) REFERENCES leads(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS pitches(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,user_id BIGINT UNSIGNED NOT NULL,lead_id BIGINT UNSIGNED NULL,title VARCHAR(190) NOT NULL,format ENUM('Email pitch','WhatsApp pitch','Phone script','Meeting pitch','Formal proposal','Elevator pitch','Follow-up message','LinkedIn outreach') NOT NULL DEFAULT 'Email pitch',client_problem TEXT NULL,proposed_solution TEXT NULL,benefits TEXT NULL,pricing VARCHAR(190) NULL,content MEDIUMTEXT NOT NULL,status ENUM('Draft','Ready','Sent') NOT NULL DEFAULT 'Draft',created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,CONSTRAINT pitches_company_fk FOREIGN KEY(company_id) REFERENCES companies(id),CONSTRAINT pitches_user_fk FOREIGN KEY(user_id) REFERENCES users(id),CONSTRAINT pitches_lead_fk FOREIGN KEY(lead_id) REFERENCES leads(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;