-- ============================================
-- BAYAZID FARM MANAGEMENT SYSTEM
-- PHASE 2 — Health & Vaccination
-- Run this AFTER schema.sql (adds new tables only)
-- ============================================

USE technica_bayezid;

DROP TABLE IF EXISTS vaccinations;
DROP TABLE IF EXISTS health_records;

-- ---------------------------
-- HEALTH RECORDS
-- ---------------------------
CREATE TABLE health_records (
    id INT AUTO_INCREMENT PRIMARY KEY,
    cow_id INT NOT NULL,
    record_date DATE NOT NULL,
    record_type ENUM('Checkup','Illness','Treatment','Injury','Other') NOT NULL DEFAULT 'Checkup',
    diagnosis VARCHAR(255) NULL,
    treatment VARCHAR(255) NULL,
    remarks TEXT NULL,
    created_by INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (cow_id) REFERENCES cows(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ---------------------------
-- VACCINATIONS
-- ---------------------------
CREATE TABLE vaccinations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    cow_id INT NOT NULL,
    vaccine_name VARCHAR(150) NOT NULL,
    dose VARCHAR(50) NULL,
    date_given DATE NOT NULL,
    next_due_date DATE NULL,
    administered_by VARCHAR(100) NULL,
    remarks TEXT NULL,
    created_by INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (cow_id) REFERENCES cows(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE INDEX idx_health_cow ON health_records(cow_id);
CREATE INDEX idx_vaccine_cow ON vaccinations(cow_id);
CREATE INDEX idx_vaccine_due ON vaccinations(next_due_date);