SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS ssm_companies (
 id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(190) NOT NULL,cui VARCHAR(30),trade_registry VARCHAR(50),address TEXT,city VARCHAR(100),county VARCHAR(100),contact_person VARCHAR(190),phone VARCHAR(50),email VARCHAR(190),activity_code VARCHAR(30),employee_count INT DEFAULT 0,status VARCHAR(30) DEFAULT 'active',notes TEXT,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,updated_at TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ssm_positions (
 id INT AUTO_INCREMENT PRIMARY KEY,company_id INT NOT NULL,name VARCHAR(190) NOT NULL,department VARCHAR(190),risk_level VARCHAR(30) DEFAULT 'low',description TEXT,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(company_id) REFERENCES ssm_companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ssm_employees (
 id INT AUTO_INCREMENT PRIMARY KEY,company_id INT NOT NULL,position_id INT NULL,full_name VARCHAR(190) NOT NULL,cnp VARCHAR(13),id_series VARCHAR(10),id_number VARCHAR(20),employment_date DATE,medical_expiry DATE,ssm_training_expiry DATE,phone VARCHAR(50),email VARCHAR(190),status VARCHAR(30) DEFAULT 'active',notes TEXT,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(company_id) REFERENCES ssm_companies(id) ON DELETE CASCADE,FOREIGN KEY(position_id) REFERENCES ssm_positions(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ssm_job_sheets (
 id INT AUTO_INCREMENT PRIMARY KEY,company_id INT NOT NULL,position_id INT NOT NULL,title VARCHAR(190) NOT NULL,purpose TEXT,responsibilities TEXT,requirements TEXT,ssm_duties TEXT,revision VARCHAR(30),effective_date DATE,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(company_id) REFERENCES ssm_companies(id) ON DELETE CASCADE,FOREIGN KEY(position_id) REFERENCES ssm_positions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ssm_risks (
 id INT AUTO_INCREMENT PRIMARY KEY,company_id INT NOT NULL,position_id INT NULL,risk_name VARCHAR(255) NOT NULL,hazard_source TEXT,probability TINYINT DEFAULT 1,severity TINYINT DEFAULT 1,score INT DEFAULT 1,level VARCHAR(30) DEFAULT 'low',existing_controls TEXT,status VARCHAR(30) DEFAULT 'open',created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(company_id) REFERENCES ssm_companies(id) ON DELETE CASCADE,FOREIGN KEY(position_id) REFERENCES ssm_positions(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ssm_prevention_measures (
 id INT AUTO_INCREMENT PRIMARY KEY,company_id INT NOT NULL,risk_id INT NULL,measure TEXT NOT NULL,responsible VARCHAR(190),deadline DATE,estimated_cost DECIMAL(12,2),completion_date DATE,status VARCHAR(30) DEFAULT 'open',evidence TEXT,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(company_id) REFERENCES ssm_companies(id) ON DELETE CASCADE,FOREIGN KEY(risk_id) REFERENCES ssm_risks(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ssm_trainings (
 id INT AUTO_INCREMENT PRIMARY KEY,company_id INT NOT NULL,employee_id INT NOT NULL,training_type VARCHAR(50),training_date DATE NOT NULL,duration_minutes INT,trainer_name VARCHAR(190),topics TEXT,result VARCHAR(30),next_date DATE,notes TEXT,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(company_id) REFERENCES ssm_companies(id) ON DELETE CASCADE,FOREIGN KEY(employee_id) REFERENCES ssm_employees(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ssm_itm_reports (
 id INT AUTO_INCREMENT PRIMARY KEY,company_id INT NOT NULL,report_type VARCHAR(50),period_start DATE,period_end DATE,accidents_count INT DEFAULT 0,incidents_count INT DEFAULT 0,training_count INT DEFAULT 0,open_measures_count INT DEFAULT 0,summary TEXT,status VARCHAR(30) DEFAULT 'draft',created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(company_id) REFERENCES ssm_companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;