-- Add job openings table (run once on existing databases that were created before job_openings existed)
-- mysql -u USER -p DATABASE < sql/migrate_job_openings.sql

SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS job_openings (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  slug VARCHAR(255) NOT NULL UNIQUE,
  location VARCHAR(255) NULL,
  employment_type VARCHAR(120) NULL,
  short_description TEXT NULL,
  description MEDIUMTEXT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Optional dummy openings (safe to run once; skipped if slug already exists)
INSERT IGNORE INTO job_openings (title, slug, location, employment_type, short_description, description, sort_order, is_active) VALUES
('Solar Installation Engineer', 'solar-installation-engineer', 'Bilaspur, Chhattisgarh', 'Full-time', 'Rooftop installs, commissioning, and site safety.', 'We need a hands-on engineer comfortable with residential and small commercial rooftop systems. You will coordinate with the design team, lead on-site execution, and document as-built details. Experience with Tata Power Solar or similar brands is a plus.', 10, 1),
('Sales & Site Survey Executive', 'sales-site-survey-executive', 'Bilaspur, Chhattisgarh', 'Full-time', 'Customer visits, load assessment, and proposal support.', 'Visit homes and businesses, explain solar benefits, capture roof and electrical details, and support quotations. Good communication in Hindi and English required; basic understanding of net metering is helpful.', 20, 1),
('Electrical Technician (AC/DC)', 'electrical-technician-ac-dc', 'Bilaspur, Chhattisgarh', 'Full-time', 'Panel termination, inverter wiring, and testing.', 'Support AC/DC cabling, inverter installation, earthing checks, and pre-commissioning tests. ITI/Diploma in electrical trades preferred; safety training will be provided.', 30, 1),
('Operations & Logistics Coordinator', 'operations-logistics-coordinator', 'Bilaspur, Chhattisgarh', 'Full-time', 'Material planning, vendor follow-up, and dispatch.', 'Coordinate panel, inverter, and structure deliveries; track stock; liaise with installers and customers for timelines. Proficiency in Excel or Google Sheets is required.', 40, 1),
('Intern — Renewable Energy', 'intern-renewable-energy', 'Bilaspur, Chhattisgarh', 'Internship', '3-month learning role for students or fresh graduates.', 'Shadow site visits, assist with documentation, and learn basic design tools. Stipend as per company policy; certificate on completion.', 50, 1);
