CREATE TABLE IF NOT EXISTS job_field_notes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    job_id INT NOT NULL,
    note_text TEXT NOT NULL,
    created_by INT NOT NULL,
    updated_by INT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NULL,
    is_deleted TINYINT(1) NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS job_field_note_versions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    field_note_id INT NOT NULL,
    previous_text TEXT NOT NULL,
    changed_by INT NOT NULL,
    changed_at DATETIME NOT NULL
);

ALTER TABLE customer_portal_tokens ADD COLUMN IF NOT EXISTS quote_id INT NULL;
ALTER TABLE customer_portal_tokens ADD COLUMN IF NOT EXISTS invoice_id INT NULL;
ALTER TABLE customer_portal_tokens ADD COLUMN IF NOT EXISTS token_nonce VARCHAR(80) NULL;
ALTER TABLE customer_portal_tokens ADD COLUMN IF NOT EXISTS last_accessed_at DATETIME NULL;
ALTER TABLE customer_portal_tokens ADD COLUMN IF NOT EXISTS revoked_at DATETIME NULL;
ALTER TABLE customer_portal_tokens ADD COLUMN IF NOT EXISTS revoked_by INT NULL;
ALTER TABLE customer_portal_tokens ADD COLUMN IF NOT EXISTS created_by INT NULL;

CREATE TABLE IF NOT EXISTS customer_message_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    client_id INT NOT NULL,
    job_id INT NOT NULL,
    portal_token_id INT NULL,
    channel VARCHAR(20) NOT NULL,
    recipient VARCHAR(190) NOT NULL,
    subject VARCHAR(190) NULL,
    body TEXT NOT NULL,
    provider_status VARCHAR(40) NOT NULL,
    provider_message VARCHAR(255) NULL,
    created_by INT NULL,
    created_at DATETIME NOT NULL
);