use salarymarket ;
CREATE TABLE company_reports_authorization (
id INT AUTO_INCREMENT PRIMARY KEY,
report_generated INT NOT NULL,
report_authorized VARCHAR(255) NOT NULL,
companyId INT NOT NULL,
createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,     
updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (companyId) REFERENCES company(id) ON DELETE CASCADE ON UPDATE CASCADE);
ALTER TABLE company 
DROP COLUMN logo ;
ALTER TABLE company 
ADD COLUMN actif boolean NOT NULL;
ALTER TABLE solution_setting_country
ADD COLUMN round INT;
UPDATE solution_setting_country
SET round = 3
WHERE code = 'TN';
UPDATE solution_setting_country
SET round = 2
WHERE code = 'FR';
CREATE TABLE company_activities_translate (
	id  INT AUTO_INCREMENT PRIMARY KEY,
	description VARCHAR(255),
	activityId INT NOT NULL,
	languageId INT NOT NULL,
	created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,     
	updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	FOREIGN KEY (languageId) REFERENCES languages(id) ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY (activityId) REFERENCES company_activities(id) ON DELETE CASCADE ON UPDATE CASCADE
);

alter table company_compensation DROP COLUMN annual_cost_car_depreciation;
-- fix translate tag language
UPDATE solution_setting_business_sector_translate
SET languageId = 1
WHERE businessSectorCode = 'BS04' 
  AND label = 'Industrial market';
  
INSERT INTO company_reports_authorization (companyId, report_generated, report_authorized)
SELECT 
    c.id AS companyId,
    COUNT(cr.id) AS report_generated,
    CASE 
        WHEN c.subscription_type = 'Advanced' THEN 'Illimited'
        ELSE '1'
    END AS report_authorized
FROM company c
LEFT JOIN company_reports cr ON cr.companyId = c.id
WHERE c.id NOT IN (
    SELECT companyId FROM company_reports_authorization
)
GROUP BY c.id, c.subscription_type;


use salarymarket;
INSERT INTO company_activities_translate (description, activityId, languageId)
SELECT 
    ca.description,
    ca.id AS activityId,
    2 AS languageId  -- Français
FROM company_activities ca
WHERE ca.description IS NOT NULL ;

INSERT INTO company_activities_translate (description, activityId, languageId)
VALUES 
('Ibtihel HR Group from HR House International deleted a user Eya HR Group.', 1, 1),
('Tarek HR Group of HR House International created a company MS Solutions.', 2, 1),
('Tarek HR Group of HR House International created a profile.', 3, 1),
('Ibtihel HR Group from HR House International sent an email to Henda Msaed.', 4, 1),
('Ibtihel HR Group of HR House International validated an MS Solutions company.', 5, 1),
('Tarek HR Group of HR House International created a company Telnet Groupe.', 6, 1),
('Tarek HR Group of HR House International created a profile.', 7, 1),
('Tarek HR Group of HR House International sent an email to Yousra ZIADA.', 8, 1),
('Tarek HR Group of HR House International validated a Telnet Group company.', 9, 1),
('Ahlem HR Group from HR House International sent an email to Sabrine Trabelsi.', 10, 1),
('Ahlem HR Group from HR House International created a user Sabrine Trabelsi.', 11, 1);