-- ============================================
-- BAYAZID FARM MANAGEMENT SYSTEM
-- PHASE 3 — Feed Management
-- Run this AFTER schema.sql + schema_phase2.sql
-- ============================================

USE technica_bayezid;

DROP TABLE IF EXISTS feed_records;

CREATE TABLE feed_records (
    id INT AUTO_INCREMENT PRIMARY KEY,
    cow_id INT NOT NULL,
    feed_date DATE NOT NULL,
    feed_type VARCHAR(100) NOT NULL,
    quantity_kg DECIMAL(6,2) NULL,
    cost DECIMAL(10,2) 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: CHECK constraints (quantity/cost >= 0) intentionally skipped —
-- some MySQL/MariaDB hosts reject them (error #1901). Enforced in PHP instead.

CREATE INDEX idx_feed_cow ON feed_records(cow_id);
CREATE INDEX idx_feed_date ON feed_records(feed_date);