-- ============================================
-- BAYAZID FARM MANAGEMENT SYSTEM
-- PHASE 4 — Milk Production
-- Run this AFTER schema.sql + schema_phase2.sql + schema_phase3.sql
-- ============================================

USE technica_bayezid;

DROP TABLE IF EXISTS milk_records;

CREATE TABLE milk_records (
    id INT AUTO_INCREMENT PRIMARY KEY,
    cow_id INT NOT NULL,
    milk_date DATE NOT NULL,
    session ENUM('Morning','Afternoon','Evening') NOT NULL DEFAULT 'Morning',
    quantity_liters DECIMAL(6,2) NOT 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;

-- NOTE: quantity >= 0 enforced in PHP, not as a CHECK constraint
-- (some MySQL/MariaDB hosts reject CHECK — error #1901).

CREATE INDEX idx_milk_cow ON milk_records(cow_id);
CREATE INDEX idx_milk_date ON milk_records(milk_date);

-- Prevent logging the same cow + date + session twice by mistake
ALTER TABLE milk_records ADD UNIQUE KEY uq_milk_cow_date_session (cow_id, milk_date, session);