

-- Animals table
CREATE TABLE IF NOT EXISTS animals (
    id VARCHAR(36) PRIMARY KEY,
    species VARCHAR(255),
    common_name VARCHAR(255),
    age_group VARCHAR(50),
    sex VARCHAR(20),
    weight DECIMAL(10,2),
    weight_unit VARCHAR(10) DEFAULT 'g',
    patient_id VARCHAR(100),
    rescue_reference VARCHAR(100),
    microchip_number VARCHAR(100),
    
    -- Rescue information
    rescue_date DATE,
    rescue_time TIME,
    rescue_location TEXT,
    rescue_suburb VARCHAR(255),
    rescue_coordinates TEXT,
    rescue_reason TEXT,
    
    -- Medical information
    condition_on_arrival TEXT,
    injuries TEXT,
    diagnosis TEXT,
    treatment_plan TEXT,
    medications TEXT,
    special_care_instructions TEXT,
    vet_name VARCHAR(255),
    vet_clinic VARCHAR(255),
    vet_email VARCHAR(255),
    vet_phone VARCHAR(50),
    vet_notes TEXT,
    history_reasons TEXT,
    
    -- Care information
    assigned_carer VARCHAR(36),
    care_group VARCHAR(255),
    housing_type VARCHAR(100),
    diet TEXT,
    feeding_schedule TEXT,
    status ENUM('in_care', 'released', 'deceased', 'transferred') NOT NULL DEFAULT 'in_care',
    outcome TEXT,
    
    -- Donor/finder information
    donor_name VARCHAR(255),
    donor_email VARCHAR(255),
    donor_phone VARCHAR(50),
    donor_address TEXT,
    donor_consent BOOLEAN DEFAULT FALSE,
    
    -- Documents and media
    photo_url TEXT,
    documents TEXT,
    
    -- Additional fields
    additional_notes TEXT,
    created_by VARCHAR(36),
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_species (species),
    INDEX idx_status (status),
    INDEX idx_assigned_carer (assigned_carer),
    INDEX idx_rescue_date (rescue_date),
    INDEX idx_patient_id (patient_id),
    FOREIGN KEY (assigned_carer) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Medical records table
CREATE TABLE IF NOT EXISTS medical_records (
    id VARCHAR(36) PRIMARY KEY,
    animal_id VARCHAR(36) NOT NULL,
    date DATE NOT NULL,
    time TIME,
    type VARCHAR(100),
    vet_name VARCHAR(255),
    clinic VARCHAR(255),
    diagnosis TEXT,
    treatment TEXT,
    medications TEXT,
    notes TEXT,
    follow_up_required BOOLEAN DEFAULT FALSE,
    follow_up_date DATE,
    created_by VARCHAR(36),
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_animal_id (animal_id),
    INDEX idx_date (date),
    FOREIGN KEY (animal_id) REFERENCES animals(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Weight records table
CREATE TABLE IF NOT EXISTS weight_records (
    id VARCHAR(36) PRIMARY KEY,
    animal_id VARCHAR(36) NOT NULL,
    date DATE NOT NULL,
    time TIME,
    weight DECIMAL(10,2) NOT NULL,
    unit VARCHAR(10) DEFAULT 'g',
    body_condition VARCHAR(50),
    notes TEXT,
    created_by VARCHAR(36),
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_animal_id (animal_id),
    INDEX idx_date (date),
    FOREIGN KEY (animal_id) REFERENCES animals(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Feeding records table
CREATE TABLE IF NOT EXISTS feeding_records (
    id VARCHAR(36) PRIMARY KEY,
    animal_id VARCHAR(36) NOT NULL,
    date DATE NOT NULL,
    time TIME,
    food_type VARCHAR(255),
    amount VARCHAR(100),
    accepted BOOLEAN,
    notes TEXT,
    created_by VARCHAR(36),
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_animal_id (animal_id),
    INDEX idx_date (date),
    FOREIGN KEY (animal_id) REFERENCES animals(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Documents table
CREATE TABLE IF NOT EXISTS documents (
    id VARCHAR(36) PRIMARY KEY,
    animal_id VARCHAR(36) NOT NULL,
    file_name VARCHAR(255) NOT NULL,
    file_type VARCHAR(50),
    file_size INT,
    file_url TEXT NOT NULL,
    uploaded_by VARCHAR(36),
    uploaded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_animal_id (animal_id),
    FOREIGN KEY (animal_id) REFERENCES animals(id) ON DELETE CASCADE,
    FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Activity logs table
CREATE TABLE IF NOT EXISTS activity_logs (
    id VARCHAR(36) PRIMARY KEY,
    animal_id VARCHAR(36),
    user_id VARCHAR(36),
    action VARCHAR(100) NOT NULL,
    description TEXT,
    metadata TEXT,
    timestamp DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_animal_id (animal_id),
    INDEX idx_user_id (user_id),
    INDEX idx_timestamp (timestamp),
    FOREIGN KEY (animal_id) REFERENCES animals(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create indexes for performance
CREATE INDEX idx_animals_created_at ON animals(created_at DESC);
CREATE INDEX idx_medical_records_created_at ON medical_records(created_at DESC);
CREATE INDEX idx_weight_records_created_at ON weight_records(created_at DESC);
CREATE INDEX idx_feeding_records_created_at ON feeding_records(created_at DESC);
