-- ============================================
-- BAYAZID FARM MANAGEMENT SYSTEM
-- PHASE 6 — Weight, Expense, Inventory, Reports & Analytics
-- Run this AFTER schema.sql + schema_phase2..5.sql
-- ============================================

USE technica_bayezid;

DROP TABLE IF EXISTS weight_records;
DROP TABLE IF EXISTS expense_records;
DROP TABLE IF EXISTS inventory_items;
DROP TABLE IF EXISTS inventory_logs;

-- ---------------------------
-- WEIGHT RECORDS (per cow, both genders)
-- ---------------------------
CREATE TABLE weight_records (
    id INT AUTO_INCREMENT PRIMARY KEY,
    cow_id INT NOT NULL,
    record_date DATE NOT NULL,
    weight_kg DECIMAL(6,2) NOT NULL,
    remarks VARCHAR(255) 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_weight_cow ON weight_records(cow_id);

-- ---------------------------
-- EXPENSE RECORDS (per cow, both genders)
-- ---------------------------
CREATE TABLE expense_records (
    id INT AUTO_INCREMENT PRIMARY KEY,
    cow_id INT NOT NULL,
    expense_date DATE NOT NULL,
    category ENUM('Feed','Medical','Breeding','Labor','Transport','Other') NOT NULL DEFAULT 'Other',
    amount DECIMAL(10,2) NOT NULL,
    remarks VARCHAR(255) 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_expense_cow ON expense_records(cow_id);
CREATE INDEX idx_expense_date ON expense_records(expense_date);

-- ---------------------------
-- INVENTORY (farm-wide store, not per-cow)
-- ---------------------------
CREATE TABLE inventory_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    item_name VARCHAR(150) NOT NULL,
    category ENUM('Feed','Medicine','Equipment','Other') NOT NULL DEFAULT 'Other',
    unit VARCHAR(30) NOT NULL DEFAULT 'kg',
    quantity DECIMAL(10,2) NOT NULL DEFAULT 0,
    low_stock_threshold DECIMAL(10,2) NULL,
    notes VARCHAR(255) NULL,
    created_by INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

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

-- Stock movement log (in/out) — keeps a history instead of just overwriting quantity
CREATE TABLE inventory_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    item_id INT NOT NULL,
    change_type ENUM('In','Out') NOT NULL,
    quantity DECIMAL(10,2) NOT NULL,
    log_date DATE NOT NULL,
    remarks VARCHAR(255) NULL,
    created_by INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

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