-- Cattle Farm Management System Database Schema

-- Create database
CREATE DATABASE IF NOT EXISTS cattle_farm CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE cattle_farm;

-- Users table
CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    role ENUM('super_admin', 'farm_manager', 'veterinarian', 'data_entry', 'viewer') NOT NULL DEFAULT 'viewer',
    phone VARCHAR(20),
    profile_pic VARCHAR(255),
    farm_id INT DEFAULT NULL,
    last_login DATETIME,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Farms table
CREATE TABLE farms (
    farm_id INT PRIMARY KEY AUTO_INCREMENT,
    farm_name VARCHAR(100) NOT NULL,
    farm_code VARCHAR(20) UNIQUE,
    address TEXT,
    city VARCHAR(100),
    state VARCHAR(100),
    country VARCHAR(100) DEFAULT 'India',
    phone VARCHAR(20),
    email VARCHAR(100),
    total_area DECIMAL(10,2),
    capacity INT,
    established_date DATE,
    owner_name VARCHAR(100),
    logo VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Breeds table
CREATE TABLE breeds (
    breed_id INT PRIMARY KEY AUTO_INCREMENT,
    breed_name VARCHAR(100) NOT NULL,
    breed_code VARCHAR(20),
    origin_country VARCHAR(100),
    milk_production_avg DECIMAL(6,2),
    fat_percentage DECIMAL(4,2),
    maturity_age_months INT,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert default breeds
INSERT INTO breeds (breed_name, breed_code, origin_country, milk_production_avg, fat_percentage, maturity_age_months) VALUES
('Holstein Friesian', 'HF', 'Netherlands', 25.5, 3.5, 24),
('Jersey', 'JER', 'England', 20.0, 5.0, 22),
('Sahiwal', 'SAH', 'Pakistan', 15.0, 4.5, 30),
('Gir', 'GIR', 'India', 12.0, 4.8, 36),
('Brown Swiss', 'BS', 'Switzerland', 22.0, 4.0, 26);

-- Sheds/Sections
CREATE TABLE sheds (
    shed_id INT PRIMARY KEY AUTO_INCREMENT,
    shed_name VARCHAR(50) NOT NULL,
    shed_number VARCHAR(20),
    farm_id INT NOT NULL,
    capacity INT DEFAULT 50,
    current_count INT DEFAULT 0,
    area_sqft DECIMAL(10,2),
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (farm_id) REFERENCES farms(farm_id) ON DELETE CASCADE
);

-- Animals table
CREATE TABLE animals (
    animal_id INT PRIMARY KEY AUTO_INCREMENT,
    tag_number VARCHAR(50) UNIQUE NOT NULL,
    internal_id VARCHAR(50) UNIQUE,
    name VARCHAR(100),
    breed_id INT NOT NULL,
    gender ENUM('male', 'female') NOT NULL,
    date_of_birth DATE NOT NULL,
    age_months INT GENERATED ALWAYS AS (TIMESTAMPDIFF(MONTH, date_of_birth, CURDATE())) STORED,
    sire_id INT,
    dam_id INT,
    purchase_date DATE,
    purchase_cost DECIMAL(10,2),
    purchase_vendor VARCHAR(100),
    current_status ENUM('active', 'sold', 'dead', 'transferred', 'drying', 'pregnant') DEFAULT 'active',
    status_date DATE,
    shed_id INT,
    farm_id INT NOT NULL,
    identification_marks TEXT,
    weight_current DECIMAL(6,2),
    weight_last_updated DATE,
    height_cm DECIMAL(5,2),
    body_condition_score INT CHECK (body_condition_score BETWEEN 1 AND 5),
    photo_url VARCHAR(255),
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (breed_id) REFERENCES breeds(breed_id),
    FOREIGN KEY (sire_id) REFERENCES animals(animal_id) ON DELETE SET NULL,
    FOREIGN KEY (dam_id) REFERENCES animals(animal_id) ON DELETE SET NULL,
    FOREIGN KEY (shed_id) REFERENCES sheds(shed_id) ON DELETE SET NULL,
    FOREIGN KEY (farm_id) REFERENCES farms(farm_id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(user_id) ON DELETE SET NULL,
    INDEX idx_tag_number (tag_number),
    INDEX idx_status (current_status),
    INDEX idx_farm_shed (farm_id, shed_id)
);

-- Breeding records
CREATE TABLE breeding_records (
    record_id INT PRIMARY KEY AUTO_INCREMENT,
    animal_id INT NOT NULL,
    breeding_type ENUM('ai', 'natural', 'embryo') NOT NULL,
    breeding_date DATE NOT NULL,
    bull_id INT,
    semen_id INT,
    heat_date DATE,
    heat_detection_method VARCHAR(50),
    technician VARCHAR(100),
    notes TEXT,
    pregnancy_check_date DATE,
    pregnancy_status ENUM('positive', 'negative', 'unknown', 'aborted') DEFAULT 'unknown',
    expected_calving_date DATE,
    actual_calving_date DATE,
    calving_ease ENUM('easy', 'normal', 'difficult', 'assisted', 'c-section'),
    calf_gender ENUM('male', 'female'),
    calf_tag_number VARCHAR(50),
    calf_status ENUM('alive', 'dead', 'weak'),
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (animal_id) REFERENCES animals(animal_id) ON DELETE CASCADE,
    FOREIGN KEY (bull_id) REFERENCES animals(animal_id) ON DELETE SET NULL,
    FOREIGN KEY (created_by) REFERENCES users(user_id) ON DELETE SET NULL,
    INDEX idx_animal_id (animal_id),
    INDEX idx_breeding_date (breeding_date),
    INDEX idx_pregnancy_status (pregnancy_status)
);

-- Milk production
CREATE TABLE milk_production (
    milk_id INT PRIMARY KEY AUTO_INCREMENT,
    animal_id INT NOT NULL,
    milking_date DATE NOT NULL,
    milking_time ENUM('morning', 'evening', 'both') NOT NULL,
    milk_yield DECIMAL(6,2) NOT NULL,
    fat_percentage DECIMAL(4,2),
    snf_percentage DECIMAL(4,2),
    protein_percentage DECIMAL(4,2),
    lactation_number INT,
    days_in_milk INT,
    milker_id INT,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (animal_id) REFERENCES animals(animal_id) ON DELETE CASCADE,
    FOREIGN KEY (milker_id) REFERENCES users(user_id) ON DELETE SET NULL,
    INDEX idx_milking_date (milking_date),
    INDEX idx_animal_date (animal_id, milking_date),
    UNIQUE KEY unique_milking (animal_id, milking_date, milking_time)
);

-- Lactation periods
CREATE TABLE lactation_periods (
    lactation_id INT PRIMARY KEY AUTO_INCREMENT,
    animal_id INT NOT NULL,
    lactation_number INT NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE,
    total_milk DECIMAL(10,2) DEFAULT 0,
    avg_daily_yield DECIMAL(6,2),
    peak_yield DECIMAL(6,2),
    peak_day INT,
    days_in_milk INT,
    dry_date DATE,
    status ENUM('active', 'completed') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (animal_id) REFERENCES animals(animal_id) ON DELETE CASCADE,
    UNIQUE KEY unique_lactation (animal_id, lactation_number),
    INDEX idx_status (status)
);

-- Medicines/Vaccines master
CREATE TABLE medicines (
    medicine_id INT PRIMARY KEY AUTO_INCREMENT,
    medicine_type ENUM('vaccine', 'antibiotic', 'anti-inflammatory', 'dewormer', 'supplement', 'other') NOT NULL,
    name VARCHAR(100) NOT NULL,
    brand VARCHAR(100),
    dosage_form VARCHAR(50),
    active_ingredient TEXT,
    dosage_instructions TEXT,
    storage_conditions VARCHAR(200),
    shelf_life_months INT,
    min_stock_level INT DEFAULT 10,
    current_stock INT DEFAULT 0,
    unit VARCHAR(20),
    cost_per_unit DECIMAL(8,2),
    supplier VARCHAR(100),
    last_purchase_date DATE,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_medicine_type (medicine_type)
);

-- Insert some default medicines
INSERT INTO medicines (medicine_type, name, brand, dosage_form, unit, cost_per_unit) VALUES
('vaccine', 'Foot and Mouth Disease Vaccine', 'FMD', 'Injectable', 'ml', 150),
('vaccine', 'Hemorrhagic Septicemia Vaccine', 'HS', 'Injectable', 'ml', 120),
('dewormer', 'Albendazole', 'Alben', 'Oral', 'tablet', 15),
('antibiotic', 'Oxytetracycline', 'OTC', 'Injectable', 'ml', 80),
('supplement', 'Calcium Bolus', 'Cal-B', 'Oral', 'tablet', 25);

-- Health records
CREATE TABLE health_records (
    health_id INT PRIMARY KEY AUTO_INCREMENT,
    animal_id INT NOT NULL,
    record_date DATE NOT NULL,
    diagnosis VARCHAR(200) NOT NULL,
    symptoms TEXT,
    disease_category ENUM('infectious', 'metabolic', 'parasitic', 'reproductive', 'respiratory', 'digestive', 'other'),
    severity ENUM('mild', 'moderate', 'severe', 'critical'),
    vet_id INT,
    vet_name VARCHAR(100),
    treatment_plan TEXT,
    recovery_status ENUM('recovered', 'under_treatment', 'chronic', 'died') DEFAULT 'under_treatment',
    follow_up_date DATE,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (animal_id) REFERENCES animals(animal_id) ON DELETE CASCADE,
    FOREIGN KEY (vet_id) REFERENCES users(user_id) ON DELETE SET NULL,
    FOREIGN KEY (created_by) REFERENCES users(user_id) ON DELETE SET NULL,
    INDEX idx_record_date (record_date),
    INDEX idx_recovery_status (recovery_status)
);

-- Vaccination schedule
CREATE TABLE vaccination_schedule (
    schedule_id INT PRIMARY KEY AUTO_INCREMENT,
    animal_id INT NOT NULL,
    vaccine_id INT NOT NULL,
    due_date DATE NOT NULL,
    scheduled_date DATE,
    actual_date DATE,
    administered_by INT,
    dosage VARCHAR(50),
    batch_number VARCHAR(50),
    next_due_date DATE,
    status ENUM('scheduled', 'completed', 'missed', 'cancelled') DEFAULT 'scheduled',
    notes TEXT,
    reminder_sent BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (animal_id) REFERENCES animals(animal_id) ON DELETE CASCADE,
    FOREIGN KEY (vaccine_id) REFERENCES medicines(medicine_id) ON DELETE CASCADE,
    FOREIGN KEY (administered_by) REFERENCES users(user_id) ON DELETE SET NULL,
    INDEX idx_due_date (due_date),
    INDEX idx_status (status)
);

-- Feed types
CREATE TABLE feed_types (
    feed_id INT PRIMARY KEY AUTO_INCREMENT,
    feed_name VARCHAR(100) NOT NULL,
    feed_category ENUM('roughage', 'concentrate', 'supplement', 'mineral', 'other') NOT NULL,
    protein_percent DECIMAL(4,2),
    fiber_percent DECIMAL(4,2),
    energy_kcal DECIMAL(8,2),
    unit VARCHAR(20) DEFAULT 'kg',
    cost_per_unit DECIMAL(8,2),
    min_stock_level DECIMAL(10,2),
    current_stock DECIMAL(10,2) DEFAULT 0,
    supplier VARCHAR(100),
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert default feed types
INSERT INTO feed_types (feed_name, feed_category, protein_percent, cost_per_unit) VALUES
('Green Grass', 'roughage', 8.5, 5.00),
('Dry Hay', 'roughage', 6.0, 8.00),
('Maize Silage', 'roughage', 7.0, 12.00),
('Cotton Seed Cake', 'concentrate', 24.0, 35.00),
('Wheat Bran', 'concentrate', 15.0, 20.00),
('Mineral Mixture', 'mineral', NULL, 50.00);

-- Financial transactions
CREATE TABLE financial_transactions (
    transaction_id INT PRIMARY KEY AUTO_INCREMENT,
    transaction_date DATE NOT NULL,
    transaction_type ENUM('income', 'expense') NOT NULL,
    category ENUM('milk_sale', 'animal_sale', 'calf_sale', 'other_income', 'feed', 'medicine', 'veterinary', 'labor', 'purchase', 'maintenance', 'utility', 'other_expense'),
    amount DECIMAL(12,2) NOT NULL,
    description TEXT,
    animal_id INT,
    payment_method ENUM('cash', 'bank_transfer', 'cheque', 'digital'),
    reference_number VARCHAR(100),
    farm_id INT NOT NULL,
    recorded_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (animal_id) REFERENCES animals(animal_id) ON DELETE SET NULL,
    FOREIGN KEY (farm_id) REFERENCES farms(farm_id) ON DELETE CASCADE,
    FOREIGN KEY (recorded_by) REFERENCES users(user_id) ON DELETE SET NULL,
    INDEX idx_transaction_date (transaction_date),
    INDEX idx_category (category)
);

-- System settings
CREATE TABLE system_settings (
    setting_id INT PRIMARY KEY AUTO_INCREMENT,
    setting_key VARCHAR(50) UNIQUE NOT NULL,
    setting_value TEXT,
    setting_group VARCHAR(50),
    is_public BOOLEAN DEFAULT FALSE,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Insert default settings
INSERT INTO system_settings (setting_key, setting_value, setting_group) VALUES
('farm_name', 'My Cattle Farm', 'general'),
('low_yield_threshold', '20', 'alerts'),
('calving_alert_days', '7', 'alerts'),
('vaccination_reminder_days', '3', 'alerts'),
('milk_price_per_liter', '50', 'financial'),
('currency', 'INR', 'general');

-- Audit log
CREATE TABLE audit_log (
    log_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    action VARCHAR(100) NOT NULL,
    table_name VARCHAR(50),
    record_id INT,
    old_values JSON,
    new_values JSON,
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE SET NULL,
    INDEX idx_created_at (created_at),
    INDEX idx_user_action (user_id, action)
);

-- Create default farm
INSERT INTO farms (farm_name, farm_code, address, city, state, country, phone, email) VALUES
('Main Cattle Farm', 'CF001', '123 Farm Road, Farm Area', 'Vijayawada', 'Andhra Pradesh', 'India', '+919876543210', 'farm@example.com');

-- Note: Default admin user will be created during installation