SET NAMES utf8mb4;
CREATE TABLE IF NOT EXISTS automation_connectors (
 id INT AUTO_INCREMENT PRIMARY KEY, code VARCHAR(50) NOT NULL UNIQUE,
 name VARCHAR(190) NOT NULL, description TEXT NULL, base_url VARCHAR(500) NULL,
 username VARCHAR(190) NULL, encrypted_password LONGTEXT NULL,
 icon_class VARCHAR(100) NOT NULL DEFAULT 'bi-globe', sort_order INT NOT NULL DEFAULT 0,
 status VARCHAR(30) NOT NULL DEFAULT 'active', notes TEXT NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS automation_recipes (
 id INT AUTO_INCREMENT PRIMARY KEY, connector_id INT NOT NULL,
 code VARCHAR(100) NOT NULL, name VARCHAR(190) NOT NULL, category VARCHAR(100) NULL,
 description TEXT NULL, recipe_json LONGTEXT NOT NULL,
 output_extension VARCHAR(20) NOT NULL DEFAULT 'pdf', sort_order INT NOT NULL DEFAULT 0,
 status VARCHAR(30) NOT NULL DEFAULT 'active', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,
 UNIQUE KEY uq_automation_recipe(connector_id,code),
 FOREIGN KEY(connector_id) REFERENCES automation_connectors(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS automation_jobs (
 id INT AUTO_INCREMENT PRIMARY KEY, recipe_id INT NOT NULL, name VARCHAR(255) NOT NULL,
 parameters_json LONGTEXT NULL, status VARCHAR(30) NOT NULL DEFAULT 'pending',
 requested_by INT NULL, started_at DATETIME NULL, completed_at DATETIME NULL,
 output_file_path VARCHAR(700) NULL, message TEXT NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,
 FOREIGN KEY(recipe_id) REFERENCES automation_recipes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS automation_documents (
 id INT AUTO_INCREMENT PRIMARY KEY, job_id INT NULL, connector_id INT NULL,
 recipe_id INT NULL, title VARCHAR(255) NOT NULL, file_name VARCHAR(255) NOT NULL,
 file_path VARCHAR(700) NOT NULL, file_format VARCHAR(20) NULL,
 period_label VARCHAR(100) NULL, status VARCHAR(30) NOT NULL DEFAULT 'saved',
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(job_id) REFERENCES automation_jobs(id) ON DELETE SET NULL,
 FOREIGN KEY(connector_id) REFERENCES automation_connectors(id) ON DELETE SET NULL,
 FOREIGN KEY(recipe_id) REFERENCES automation_recipes(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS automation_settings (
 id INT AUTO_INCREMENT PRIMARY KEY, chrome_path VARCHAR(700) NOT NULL,
 powershell_path VARCHAR(700) NOT NULL, profile_path VARCHAR(700) NULL,
 visible_browser TINYINT(1) NOT NULL DEFAULT 1, job_timeout_seconds INT NOT NULL DEFAULT 180,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT IGNORE INTO automation_connectors(code,name,description,base_url,icon_class,sort_order) VALUES
('SAGA','SAGA WEB','Contabilitate, rapoarte și arhivă','https://web.sagasoft.ro/','bi-calculator',10),
('SPV','SPV ANAF','Mesaje, recipise și documente fiscale','https://www.anaf.ro/','bi-bank',20),
('EFACTURA','e-Factura','Facturi emise și primite','https://www.anaf.ro/','bi-receipt',30),
('ETRANSPORT','e-Transport','Coduri UIT și documente transport','https://www.anaf.ro/','bi-truck-front',40),
('ETVA','e-TVA','Decont precompletat și verificări TVA','https://www.anaf.ro/','bi-percent',50),
('CONEX','Conex','Cataloage, stocuri și comenzi furnizor','https://www.conexdist.ro/','bi-box-seam',60),
('INTERCARS','Inter Cars','Produse, stocuri, prețuri și comenzi','https://www.intercars.ro/','bi-car-front',70),
('AUTONET','Autonet','Produse, stocuri, prețuri și comenzi','https://www.autonet-group.ro/','bi-car-front-fill',80),
('24BROKER','24Broker','Ofertare și operațiuni de asigurare','https://24broker.ro/','bi-shield-check',90);
INSERT INTO automation_settings(chrome_path,powershell_path,profile_path,visible_browser,job_timeout_seconds)
SELECT 'C:\\Program Files\\Google\\Chrome\\Application\\chrome.exe',
'C:\\Windows\\System32\\WindowsPowerShell\\v1.0\\powershell.exe',
'C:\\ABS-Automation-Chrome',1,180
WHERE NOT EXISTS(SELECT 1 FROM automation_settings);