-- ============================================
-- BAYAZID FARM MANAGEMENT SYSTEM
-- PHASE 5 — Breeding & Reproduction (Female cows only)
-- Run this AFTER schema.sql + schema_phase2.sql + schema_phase3.sql + schema_phase4.sql
-- ============================================

USE technica_bayezid;

DROP TABLE IF EXISTS breeding_records;

CREATE TABLE breeding_records (
    id INT AUTO_INCREMENT PRIMARY KEY,
    cow_id INT NOT NULL,
    breeding_date DATE NOT NULL,
    method ENUM('Natural','Artificial Insemination') NOT NULL DEFAULT 'Natural',
    bull_source VARCHAR(150) NULL,
    expected_calving_date DATE NULL,
    actual_calving_date DATE NULL,
    status ENUM('Bred','Confirmed Pregnant','Not Pregnant','Calved','Aborted') NOT NULL DEFAULT 'Bred',
    calf_gender ENUM('Male','Female') 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_breeding_cow ON breeding_records(cow_id);
CREATE INDEX idx_breeding_expected ON breeding_records(expected_calving_date);