-- Master's Programs Table
CREATE TABLE IF NOT EXISTS programs (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name_en TEXT NOT NULL,
  name_tr TEXT NOT NULL,
  category TEXT NOT NULL,
  field TEXT NOT NULL,
  language TEXT NOT NULL,
  thesis_type TEXT NOT NULL, -- 'thesis', 'non-thesis', 'both'
  tuition_fee INTEGER NOT NULL,
  advance_payment INTEGER NOT NULL,
  study_period TEXT NOT NULL,
  attendance TEXT NOT NULL DEFAULT 'Physical attendance 2-3 days per week',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Applications Table
CREATE TABLE IF NOT EXISTS applications (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  full_name TEXT NOT NULL,
  email TEXT NOT NULL,
  phone TEXT,
  nationality TEXT NOT NULL,
  current_residence_status TEXT NOT NULL,
  residence_expiry_date TEXT,
  education_level TEXT NOT NULL,
  bachelor_university TEXT,
  bachelor_field TEXT,
  bachelor_graduation_year TEXT,
  program_interest TEXT NOT NULL,
  language_preference TEXT NOT NULL,
  message TEXT,
  document_urls TEXT, -- JSON array of document URLs
  status TEXT NOT NULL DEFAULT 'pending', -- pending, reviewed, accepted, rejected
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Create indexes for better query performance
CREATE INDEX IF NOT EXISTS idx_programs_category ON programs(category);
CREATE INDEX IF NOT EXISTS idx_programs_language ON programs(language);
CREATE INDEX IF NOT EXISTS idx_programs_thesis_type ON programs(thesis_type);
CREATE INDEX IF NOT EXISTS idx_applications_email ON applications(email);
CREATE INDEX IF NOT EXISTS idx_applications_status ON applications(status);
CREATE INDEX IF NOT EXISTS idx_applications_created ON applications(created_at);
