-- Modulo de gestao de contratos por e-mail

CREATE TABLE IF NOT EXISTS email_contracts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    contract_number VARCHAR(100) DEFAULT NULL,
    company_name VARCHAR(255) NOT NULL,
    unit_name VARCHAR(255) NOT NULL,
    responsible_name VARCHAR(255) DEFAULT NULL,
    sender_email VARCHAR(255) DEFAULT NULL,
    recipient_email VARCHAR(255) DEFAULT NULL,
    email_subject VARCHAR(255) DEFAULT NULL,
    direction ENUM('incoming', 'outgoing') NOT NULL DEFAULT 'incoming',
    status ENUM('received', 'analysis', 'waiting_approval', 'approved', 'rejected', 'finalized') NOT NULL DEFAULT 'received',
    priority ENUM('low', 'normal', 'high', 'critical') NOT NULL DEFAULT 'normal',
    contract_date DATE DEFAULT NULL,
    due_date DATE DEFAULT NULL,
    amount DECIMAL(12,2) DEFAULT NULL,
    email_received_at DATETIME DEFAULT NULL,
    last_message_at DATETIME DEFAULT NULL,
    observations TEXT DEFAULT NULL,
    created_by INT DEFAULT NULL,
    assigned_to INT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_email_contracts_status (status),
    INDEX idx_email_contracts_direction (direction),
    INDEX idx_email_contracts_due_date (due_date),
    INDEX idx_email_contracts_unit_name (unit_name),
    CONSTRAINT fk_email_contracts_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_email_contracts_assigned_to FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS email_contract_messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    contract_id INT DEFAULT NULL,
    message_uid VARCHAR(191) DEFAULT NULL,
    message_id_header VARCHAR(255) DEFAULT NULL,
    folder_name VARCHAR(120) DEFAULT NULL,
    sender_name VARCHAR(255) DEFAULT NULL,
    sender_email VARCHAR(255) DEFAULT NULL,
    recipient_email VARCHAR(255) DEFAULT NULL,
    subject VARCHAR(255) DEFAULT NULL,
    body_html LONGTEXT DEFAULT NULL,
    body_text LONGTEXT DEFAULT NULL,
    sent_at DATETIME DEFAULT NULL,
    direction ENUM('incoming', 'outgoing') NOT NULL DEFAULT 'incoming',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_email_contract_message_uid (message_uid),
    INDEX idx_email_contract_messages_contract (contract_id),
    CONSTRAINT fk_email_contract_messages_contract FOREIGN KEY (contract_id) REFERENCES email_contracts(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS email_contract_attachments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    contract_id INT DEFAULT NULL,
    message_id INT DEFAULT NULL,
    original_name VARCHAR(255) NOT NULL,
    stored_path VARCHAR(255) NOT NULL,
    file_ext VARCHAR(20) DEFAULT NULL,
    mime_type VARCHAR(120) DEFAULT NULL,
    file_size BIGINT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_email_contract_attachments_contract (contract_id),
    INDEX idx_email_contract_attachments_message (message_id),
    CONSTRAINT fk_email_contract_attachments_contract FOREIGN KEY (contract_id) REFERENCES email_contracts(id) ON DELETE CASCADE,
    CONSTRAINT fk_email_contract_attachments_message FOREIGN KEY (message_id) REFERENCES email_contract_messages(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS email_contract_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    contract_id INT NOT NULL,
    action_type VARCHAR(100) NOT NULL,
    old_status VARCHAR(50) DEFAULT NULL,
    new_status VARCHAR(50) DEFAULT NULL,
    description TEXT DEFAULT NULL,
    changed_by INT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_email_contract_history_contract (contract_id),
    INDEX idx_email_contract_history_created_at (created_at),
    CONSTRAINT fk_email_contract_history_contract FOREIGN KEY (contract_id) REFERENCES email_contracts(id) ON DELETE CASCADE,
    CONSTRAINT fk_email_contract_history_changed_by FOREIGN KEY (changed_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS email_contract_notifications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    contract_id INT NOT NULL,
    notification_type VARCHAR(80) NOT NULL,
    channel ENUM('system', 'email') NOT NULL DEFAULT 'system',
    recipient VARCHAR(255) DEFAULT NULL,
    status ENUM('pending', 'sent', 'failed') NOT NULL DEFAULT 'pending',
    payload TEXT DEFAULT NULL,
    sent_at DATETIME DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_email_contract_notifications_contract (contract_id),
    CONSTRAINT fk_email_contract_notifications_contract FOREIGN KEY (contract_id) REFERENCES email_contracts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS email_contract_user_access (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    access_level ENUM('admin', 'manager', 'employee', 'unit') NOT NULL DEFAULT 'employee',
    can_view_all TINYINT(1) NOT NULL DEFAULT 0,
    can_approve TINYINT(1) NOT NULL DEFAULT 0,
    can_send_email TINYINT(1) NOT NULL DEFAULT 0,
    can_manage_settings TINYINT(1) NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_email_contract_user_access (user_id),
    CONSTRAINT fk_email_contract_user_access_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO system_settings (setting_key, setting_value) VALUES
('email_imap_host', ''),
('email_imap_port', '993'),
('email_imap_encryption', 'ssl'),
('email_imap_folder', 'INBOX'),
('email_imap_username', ''),
('email_imap_password', ''),
('email_smtp_host', ''),
('email_smtp_port', '587'),
('email_smtp_encryption', 'tls'),
('email_smtp_username', ''),
('email_smtp_password', ''),
('email_from_name', 'Central de Contratos'),
('email_from_address', ''),
('email_notifications_enabled', '1');
