-- ═══════════════════════════════════════════════════════════════════════
--  Certificate verification system for ExpertAims
--  Import this in cPanel → phpMyAdmin after schema.sql
--  Safe to re-import: it drops and recreates its own table.
-- ═══════════════════════════════════════════════════════════════════════

SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS certificates;
SET FOREIGN_KEY_CHECKS = 1;

CREATE TABLE IF NOT EXISTS certificates (
  id INT AUTO_INCREMENT PRIMARY KEY,
  certificate_id VARCHAR(50) NOT NULL UNIQUE,
  internee_name VARCHAR(160) NOT NULL,
  email VARCHAR(190) DEFAULT NULL,
  education VARCHAR(255) DEFAULT NULL,
  institution VARCHAR(255) DEFAULT NULL,
  role VARCHAR(160) NOT NULL,
  company VARCHAR(255) NOT NULL,
  location VARCHAR(160) DEFAULT NULL,
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  issued_date DATE NOT NULL,
  description TEXT DEFAULT NULL,
  status ENUM('published','draft') NOT NULL DEFAULT 'published',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_cert_id (certificate_id),
  KEY idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Sample certificate for testing (not a real issued certificate)
INSERT INTO certificates (certificate_id, internee_name, email, education, institution, role, company, location, start_date, end_date, issued_date, description, status) VALUES
('EA-DEMO-2026-0001', 'Sample Internee', NULL, 'B.Tech in Engineering', 'Sample Institute', 'Summer Internship', 'ExpertAims Consultancy LLP', 'Kuwait Office', '2026-06-20', '2026-07-20', '2026-07-20', 'Sample certificate for demonstration purposes.', 'published');
