-- DigitallOS v1.0 -> v1.1 non-destructive upgrade
-- Select the existing DigitallOS database before importing.

ALTER TABLE integrations
  ADD COLUMN connection_status ENUM('untested','connected','error') NOT NULL DEFAULT 'untested' AFTER enabled,
  ADD COLUMN last_tested_at DATETIME NULL AFTER connection_status,
  ADD COLUMN last_used_at DATETIME NULL AFTER last_tested_at,
  ADD COLUMN updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER created_at;

ALTER TABLE submissions
  ADD COLUMN status ENUM('new','reviewed','converted','archived') NOT NULL DEFAULT 'new' AFTER source_url,
  ADD COLUMN visitor_id VARCHAR(100) NULL AFTER status,
  ADD INDEX idx_submissions_status (status),
  ADD INDEX idx_submissions_visitor (visitor_id);

ALTER TABLE attribution_events
  ADD COLUMN client_id INT UNSIGNED NULL AFTER id,
  ADD COLUMN session_id VARCHAR(100) NULL AFTER visitor_id,
  ADD COLUMN event_value DECIMAL(12,2) NULL AFTER event_name,
  ADD INDEX idx_attr_client (client_id),
  ADD INDEX idx_attr_session (session_id);

CREATE TABLE IF NOT EXISTS activities (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 client_id INT UNSIGNED NULL,
 contact_id INT UNSIGNED NULL,
 user_id INT UNSIGNED NULL,
 activity_type VARCHAR(80) NOT NULL,
 subject VARCHAR(190) NOT NULL,
 details TEXT NULL,
 related_type VARCHAR(80) NULL,
 related_id BIGINT UNSIGNED NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 INDEX(client_id), INDEX(contact_id), INDEX(user_id), INDEX(created_at)
);

CREATE TABLE IF NOT EXISTS api_request_logs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 integration_id INT UNSIGNED NULL,
 action VARCHAR(80) NOT NULL,
 method VARCHAR(12) NOT NULL,
 response_code SMALLINT UNSIGNED NOT NULL,
 ip_address VARCHAR(45) NULL,
 request_id VARCHAR(64) NULL,
 error_message VARCHAR(500) NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 INDEX(integration_id), INDEX(action), INDEX(created_at)
);