-- Database schema for UMIS-style login
CREATE TABLE IF NOT EXISTS users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(190) NOT NULL UNIQUE,
  full_name VARCHAR(120) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Demo user (email: demo@example.com, password: DemoPass123!)
-- NOTE: Password hash generated via PHP password_hash('DemoPass123!', PASSWORD_DEFAULT)
INSERT INTO users (email, full_name, password_hash) VALUES
('demo@example.com', 'Demo User', '$2y$10$2vJw1cS5mPa8CV7l1V.7fOql7bI/4G0J4gRsyvLr1w7rR8i6LthoG');


-- Admins table
CREATE TABLE IF NOT EXISTS admins (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(190) NOT NULL UNIQUE,
  full_name VARCHAR(120) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Students table
CREATE TABLE IF NOT EXISTS students (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  id_number VARCHAR(50) NOT NULL UNIQUE,
  student_number VARCHAR(50) NOT NULL UNIQUE,
  first_name VARCHAR(100) NOT NULL,
  last_name VARCHAR(100) NOT NULL,
  place_of_birth VARCHAR(120) NOT NULL,
  date_of_birth DATE NOT NULL,
  nationality VARCHAR(100) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  document_path VARCHAR(255) DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Seed admin (email: admin@example.com, password: AdminPass123!)
INSERT INTO admins (email, full_name, password_hash) VALUES
('admin@example.com', 'Primary Admin', '$2y$10$2vJw1cS5mPa8CV7l1V.7fOql7bI/4G0J4gRsyvLr1w7rR8i6LthoG')
ON DUPLICATE KEY UPDATE email=email;

