-- ============================================
-- BAYAZID FARM MANAGEMENT SYSTEM
-- Multiple photo gallery per cow (cows.photo stays as the single cover photo)
-- Run this once, any time after schema.sql
-- ============================================

USE technica_bayezid;

DROP TABLE IF EXISTS cow_photos;

CREATE TABLE cow_photos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    cow_id INT NOT NULL,
    filename VARCHAR(255) NOT 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_cow_photos_cow ON cow_photos(cow_id);