CREATE TABLE IF NOT EXISTS departments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  description TEXT NULL,
  created_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS positions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  description TEXT NULL,
  created_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS employees (
  id INT AUTO_INCREMENT PRIMARY KEY,
  full_name VARCHAR(200) NOT NULL,
  document_number VARCHAR(50) NOT NULL,
  department_id INT NULL,
  position_id INT NULL,
  hire_date DATE NOT NULL,
  salary DECIMAL(12,2) NOT NULL DEFAULT 0,
  email VARCHAR(150) NULL,
  phone VARCHAR(80) NULL,
  address VARCHAR(255) NULL,
  status VARCHAR(30) NOT NULL DEFAULT 'active',
  created_at DATETIME NULL,
  CONSTRAINT fk_emp_department FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE SET NULL,
  CONSTRAINT fk_emp_position FOREIGN KEY (position_id) REFERENCES positions(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS attendance (
  id INT AUTO_INCREMENT PRIMARY KEY,
  employee_id INT NOT NULL,
  attendance_date DATE NOT NULL,
  check_in TIME NULL,
  check_out TIME NULL,
  notes VARCHAR(255) NULL,
  created_at DATETIME NULL,
  CONSTRAINT fk_att_employee FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS leaves_requests (
  id INT AUTO_INCREMENT PRIMARY KEY,
  employee_id INT NOT NULL,
  leave_type VARCHAR(80) NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  paid_leave TINYINT(1) NOT NULL DEFAULT 1,
  status VARCHAR(40) NOT NULL DEFAULT 'Pendiente',
  reason VARCHAR(255) NULL,
  created_at DATETIME NULL,
  CONSTRAINT fk_leave_employee FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS payroll_periods (
  id INT AUTO_INCREMENT PRIMARY KEY,
  year INT NOT NULL,
  month INT NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  status VARCHAR(30) NOT NULL DEFAULT 'Borrador',
  created_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS payroll_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  period_id INT NOT NULL,
  employee_id INT NOT NULL,
  base_salary DECIMAL(12,2) NOT NULL DEFAULT 0,
  extras DECIMAL(12,2) NOT NULL DEFAULT 0,
  discounts DECIMAL(12,2) NOT NULL DEFAULT 0,
  net_pay DECIMAL(12,2) NOT NULL DEFAULT 0,
  created_at DATETIME NULL,
  CONSTRAINT fk_payroll_period FOREIGN KEY (period_id) REFERENCES payroll_periods(id) ON DELETE CASCADE,
  CONSTRAINT fk_payroll_employee FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


CREATE TABLE IF NOT EXISTS salary_advances (
  id INT AUTO_INCREMENT PRIMARY KEY,
  employee_id INT NOT NULL,
  advance_date DATE NOT NULL,
  amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  notes VARCHAR(255) NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'Activo',
  created_at DATETIME NULL,
  CONSTRAINT fk_adv_employee FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO salary_advances (employee_id, advance_date, amount, notes, status, created_at) VALUES
(1, DATE_FORMAT(CURDATE(), '%Y-%m-15'), 1200000, 'Adelanto de quincena demo', 'Activo', NOW());

CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  full_name VARCHAR(180) NOT NULL,
  email VARCHAR(180) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  role VARCHAR(30) NOT NULL DEFAULT 'supervisor',
  status VARCHAR(30) NOT NULL DEFAULT 'active',
  created_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS audit_logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  action_type VARCHAR(50) NOT NULL,
  table_name VARCHAR(100) NOT NULL,
  record_id INT NULL,
  details TEXT NULL,
  created_at DATETIME NULL,
  CONSTRAINT fk_audit_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS company_settings (
  setting_key VARCHAR(100) PRIMARY KEY,
  setting_value TEXT NULL,
  updated_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO departments (name, description, created_at) VALUES
('Administración', 'Área administrativa', NOW()),
('RRHH', 'Gestión de personas', NOW()),
('Ventas', 'Área comercial', NOW());

INSERT INTO positions (name, description, created_at) VALUES
('Analista', 'Cargo analista', NOW()),
('Asistente', 'Cargo asistente', NOW()),
('Supervisor', 'Cargo supervisor', NOW());

INSERT INTO users (full_name, email, password, role, status, created_at) VALUES
('Administrador General', 'admin@local.test', '$2y$12$HXSH/ipjluzoVLtxuwxDuug6G0q6zWZfVE8BpOnzqtiWfUq/jDJqW', 'admin', 'active', NOW()),
('Responsable RRHH', 'rrhh@local.test', '$2y$12$HXSH/ipjluzoVLtxuwxDuug6G0q6zWZfVE8BpOnzqtiWfUq/jDJqW', 'rrhh', 'active', NOW()),
('Supervisor Demo', 'supervisor@local.test', '$2y$12$HXSH/ipjluzoVLtxuwxDuug6G0q6zWZfVE8BpOnzqtiWfUq/jDJqW', 'supervisor', 'active', NOW());

INSERT INTO employees (full_name, document_number, department_id, position_id, hire_date, salary, email, phone, address, status, created_at) VALUES
('Juan Pérez', '1234567', 2, 1, CURDATE(), 4500000, 'juan@empresa.com', '0981000001', 'Asunción', 'active', NOW()),
('María Gómez', '2345678', 3, 3, CURDATE(), 5500000, 'maria@empresa.com', '0981000002', 'Luque', 'active', NOW());

REPLACE INTO company_settings (setting_key, setting_value, updated_at) VALUES
('company_name', 'HM Servicios Informáticos', NOW()),
('company_ruc', '80000000-0', NOW()),
('company_phone', '0961 898449', NOW()),
('company_email', 'info@hm.local', NOW()),
('company_address', 'Asunción, Paraguay', NOW()),
('company_logo', 'assets/img/logo.png', NOW()),
('company_city', 'Asunción', NOW()),
('payroll_note', 'Documento generado por el sistema.', NOW());


CREATE TABLE IF NOT EXISTS recruitment_vacancies (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(180) NOT NULL,
  department VARCHAR(150) NULL,
  location VARCHAR(150) NULL,
  status VARCHAR(30) NOT NULL DEFAULT 'Abierta',
  description TEXT NULL,
  created_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS recruitment_candidates (
  id INT AUTO_INCREMENT PRIMARY KEY,
  full_name VARCHAR(180) NOT NULL,
  document_number VARCHAR(50) NULL,
  phone VARCHAR(80) NULL,
  email VARCHAR(150) NULL,
  notes TEXT NULL,
  created_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS recruitment_applications (
  id INT AUTO_INCREMENT PRIMARY KEY,
  vacancy_id INT NOT NULL,
  candidate_id INT NOT NULL,
  stage VARCHAR(40) NOT NULL DEFAULT 'Postulado',
  score INT NOT NULL DEFAULT 0,
  notes TEXT NULL,
  applied_at DATE NULL,
  created_at DATETIME NULL,
  CONSTRAINT fk_app_vacancy FOREIGN KEY (vacancy_id) REFERENCES recruitment_vacancies(id) ON DELETE CASCADE,
  CONSTRAINT fk_app_candidate FOREIGN KEY (candidate_id) REFERENCES recruitment_candidates(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO recruitment_vacancies (title, department, location, status, description, created_at) VALUES
('Analista RRHH', 'RRHH', 'Asunción', 'Abierta', 'Búsqueda activa para reforzar operaciones de RRHH.', NOW()),
('Ejecutivo Comercial', 'Ventas', 'Luque', 'Abierta', 'Perfil comercial con experiencia en ventas B2B.', NOW());

INSERT INTO recruitment_candidates (full_name, document_number, phone, email, notes, created_at) VALUES
('Carlos Martínez', '3456789', '0981111111', 'carlos@demo.com', 'Buen perfil para entrevista.', NOW()),
('Laura Benítez', '4567890', '0981222222', 'laura@demo.com', 'Experiencia previa en retail.', NOW());

INSERT INTO recruitment_applications (vacancy_id, candidate_id, stage, score, notes, applied_at, created_at) VALUES
(1, 1, 'Entrevista', 82, 'Avanza a segunda entrevista.', CURDATE(), NOW()),
(2, 2, 'Postulado', 75, 'Pendiente de revisión comercial.', CURDATE(), NOW());
