-- Adoption Application Database Schema
-- Creates tables for storing adoption applications

-- Main adoption applications table
CREATE TABLE adoption_applications (
  id INT AUTO_INCREMENT PRIMARY KEY,
  application_number VARCHAR(20) UNIQUE NOT NULL,
  pet_id VARCHAR(20) NULL, -- Links to pets table, can be NULL if no specific pet
  status ENUM('pending', 'approved', 'denied', 'withdrawn') DEFAULT 'pending',
  
  -- Personal Information
  first_name VARCHAR(100) NOT NULL,
  last_name VARCHAR(100) NOT NULL,
  email VARCHAR(255) NOT NULL,
  phone_1 VARCHAR(20) NOT NULL,
  phone_2 VARCHAR(20) NULL,
  
  -- Address
  street_address VARCHAR(255) NOT NULL,
  address_line_2 VARCHAR(255) NULL,
  city VARCHAR(100) NOT NULL,
  state VARCHAR(50) NOT NULL,
  zip_code VARCHAR(20) NOT NULL,
  
  -- Employment
  employer_name VARCHAR(255) NULL,
  employer_phone VARCHAR(20) NULL,
  
  -- Living Circumstances
  housing_type ENUM('house', 'condo', 'apartment', 'mobile_home', 'farm') NOT NULL,
  housing_ownership ENUM('own', 'rent', 'live_with_family', 'live_with_roommates') NOT NULL,
  landlord_first_name VARCHAR(100) NULL,
  landlord_last_name VARCHAR(100) NULL,
  landlord_phone VARCHAR(20) NULL,
  pet_deposit_required BOOLEAN NULL,
  
  -- Household
  adults_in_household INT NOT NULL DEFAULT 1,
  children_in_household INT NOT NULL DEFAULT 0,
  children_ages TEXT NULL, -- JSON or comma-separated ages
  household_supportive BOOLEAN NOT NULL,
  household_allergies BOOLEAN NOT NULL,
  active_military BOOLEAN NOT NULL,
  
  -- Veterinary Information
  vet_first_name VARCHAR(100) NULL,
  vet_last_name VARCHAR(100) NULL,
  vet_hospital_name VARCHAR(255) NULL,
  vet_phone VARCHAR(20) NULL,
  vet_record_name_first VARCHAR(100) NULL, -- Name on vet records
  vet_record_name_last VARCHAR(100) NULL,
  
  -- Current Pet Care
  pets_current_vaccinations BOOLEAN NULL,
  pets_heartworm_preventative BOOLEAN NULL,
  heartworm_prevention_explanation TEXT NULL,
  heartworm_transmission_explanation TEXT NULL,
  heartworm_preventative_brand VARCHAR(255) NULL,
  heartworm_medication_frequency VARCHAR(255) NULL,
  
  -- Pet Selection
  desired_pet_name VARCHAR(100) NULL, -- Name while at shelter
  desired_pet_type ENUM('dog', 'cat') NULL,
  
  -- Why pet questions
  first_pet BOOLEAN NOT NULL,
  previously_adopted BOOLEAN NOT NULL,
  given_away_pet BOOLEAN NOT NULL,
  pet_reason_family BOOLEAN DEFAULT FALSE,
  pet_reason_guard BOOLEAN DEFAULT FALSE,
  pet_reason_friend BOOLEAN DEFAULT FALSE,
  pet_reason_gift BOOLEAN DEFAULT FALSE,
  pet_reason_other BOOLEAN DEFAULT FALSE,
  pet_reason_other_text VARCHAR(255) NULL,
  
  -- Agreement checkboxes
  agree_stress_adaptation BOOLEAN NOT NULL DEFAULT FALSE,
  agree_time_commitment BOOLEAN NOT NULL DEFAULT FALSE,
  agree_spay_neuter BOOLEAN NOT NULL DEFAULT FALSE,
  agree_lifetime_commitment BOOLEAN NOT NULL DEFAULT FALSE,
  agree_no_surrender BOOLEAN NOT NULL DEFAULT FALSE,
  
  -- Metadata
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  submitted_at TIMESTAMP NULL,
  reviewed_at TIMESTAMP NULL,
  reviewed_by INT NULL, -- Links to admin_users table
  notes TEXT NULL,
  
  FOREIGN KEY (pet_id) REFERENCES pets(pet_id) ON DELETE SET NULL,
  FOREIGN KEY (reviewed_by) REFERENCES admin_users(id) ON DELETE SET NULL,
  INDEX idx_status (status),
  INDEX idx_pet_id (pet_id),
  INDEX idx_email (email),
  INDEX idx_created_at (created_at)
);

-- Pet history table (for previous pets)
CREATE TABLE application_pet_history (
  id INT AUTO_INCREMENT PRIMARY KEY,
  application_id INT NOT NULL,
  pet_number INT NOT NULL, -- 1, 2, 3, or 4
  pet_name VARCHAR(100) NULL,
  pet_type ENUM('dog', 'cat') NULL,
  spayed_neutered ENUM('yes', 'no', 'unknown') NULL,
  current_or_previous ENUM('current', 'previous') NULL,
  
  FOREIGN KEY (application_id) REFERENCES adoption_applications(id) ON DELETE CASCADE,
  INDEX idx_application_id (application_id)
);

-- References table
CREATE TABLE application_references (
  id INT AUTO_INCREMENT PRIMARY KEY,
  application_id INT NOT NULL,
  reference_number INT NOT NULL, -- 1 or 2
  first_name VARCHAR(100) NULL,
  last_name VARCHAR(100) NULL,
  phone VARCHAR(20) NULL,
  email VARCHAR(255) NULL,
  
  FOREIGN KEY (application_id) REFERENCES adoption_applications(id) ON DELETE CASCADE,
  INDEX idx_application_id (application_id)
);

-- Function to generate application numbers
DELIMITER $$
CREATE FUNCTION generate_application_number() 
RETURNS VARCHAR(20)
READS SQL DATA
DETERMINISTIC
BEGIN
  DECLARE app_num VARCHAR(20);
  DECLARE current_year INT;
  DECLARE current_count INT;
  
  SET current_year = YEAR(NOW());
  
  SELECT COUNT(*) + 1 INTO current_count 
  FROM adoption_applications 
  WHERE YEAR(created_at) = current_year;
  
  SET app_num = CONCAT('APP-', current_year, '-', LPAD(current_count, 4, '0'));
  
  RETURN app_num;
END$$
DELIMITER ;

-- Trigger to auto-generate application numbers
DELIMITER $$
CREATE TRIGGER set_application_number 
BEFORE INSERT ON adoption_applications
FOR EACH ROW
BEGIN
  IF NEW.application_number IS NULL OR NEW.application_number = '' THEN
    SET NEW.application_number = generate_application_number();
  END IF;
END$$
DELIMITER ;