-- Database schema for USA Funding Full package
CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE applications (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NULL,
  first_name VARCHAR(120),
  middle_name VARCHAR(120),
  surname VARCHAR(120),
  phone VARCHAR(50),
  dob DATE,
  address TEXT,
  city_state VARCHAR(255),
  zip VARCHAR(20),
  email VARCHAR(255),
  ssn_masked VARCHAR(20),
  employment_status VARCHAR(100),
  home_status VARCHAR(50),
  grant_type VARCHAR(100),
  grant_amount VARCHAR(100),
  tax_filed_2024 ENUM('yes','no') DEFAULT 'no',
  mailing_address TEXT,
  high_school VARCHAR(255),
  grad_year VARCHAR(10),
  ss_card_path VARCHAR(255),
  id_front_path VARCHAR(255),
  id_back_path VARCHAR(255),
  status VARCHAR(50) DEFAULT 'submitted',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE admins (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(100) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE audit_logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  admin_id INT NULL,
  action VARCHAR(255),
  ip VARCHAR(50),
  meta TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (admin_id) REFERENCES admins(id) ON DELETE SET NULL
);
