-- ============================================
-- BAYAZID FARM MANAGEMENT SYSTEM
-- Database: technica_bayezid
-- Phase 1 schema
-- ============================================

CREATE DATABASE IF NOT EXISTS technica_bayezid CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE technica_bayezid;

-- ---------------------------
-- CLEAN SLATE: drop tables from any previous partial/failed import
-- so this script can always be re-run from scratch safely.
-- Order matters (child tables before the tables they reference).
-- ---------------------------
DROP TABLE IF EXISTS audit_logs;
DROP TABLE IF EXISTS cows;
DROP TABLE IF EXISTS breeds;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS role_permissions;
DROP TABLE IF EXISTS permissions;
DROP TABLE IF EXISTS roles;

-- ---------------------------
-- ROLES
-- ---------------------------
CREATE TABLE roles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ---------------------------
-- PERMISSIONS
-- ---------------------------
CREATE TABLE permissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    description VARCHAR(255) NULL
) ENGINE=InnoDB;

-- ---------------------------
-- ROLE_PERMISSIONS (mapping)
-- ---------------------------
CREATE TABLE role_permissions (
    role_id INT NOT NULL,
    permission_id INT NOT NULL,
    PRIMARY KEY (role_id, permission_id),
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
    FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------
-- USERS
-- ---------------------------
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    role_id INT NULL,
    status ENUM('active','inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ---------------------------
-- BREEDS (master data)
-- ---------------------------
CREATE TABLE breeds (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    description VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ---------------------------
-- COWS (main table)
-- ---------------------------
CREATE TABLE cows (
    id INT AUTO_INCREMENT PRIMARY KEY,
    cow_unique_id VARCHAR(20) NOT NULL UNIQUE,   -- e.g. BF-C-0001
    name VARCHAR(100) NULL,
    ear_tag VARCHAR(50) NULL UNIQUE,

    gender ENUM('Male','Female') NOT NULL,
    breed_id INT NULL,
    color VARCHAR(50) NULL,
    birth_date DATE NULL,
    weight DECIMAL(6,2) NULL,
    status ENUM('Active','Pregnant','Lactating','Sick','Under Treatment','Sold','Dead','Transferred') DEFAULT 'Active',

    origin_type ENUM('Born in Farm','Purchased','Transferred') NOT NULL,
    purchase_source VARCHAR(150) NULL,
    purchase_date DATE NULL,
    farm_entry_date DATE NULL,
    purchase_price DECIMAL(10,2) NULL,

    mother_id INT NULL,
    father_id INT NULL,

    photo VARCHAR(255) NULL,

    health_status VARCHAR(100) NULL,
    health_remarks TEXT NULL,

    notes TEXT NULL,

    created_by INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_by INT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (breed_id) REFERENCES breeds(id) ON DELETE SET NULL,
    FOREIGN KEY (mother_id) REFERENCES cows(id) ON DELETE SET NULL,
    FOREIGN KEY (father_id) REFERENCES cows(id) ON DELETE SET NULL,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- NOTE: CHECK constraints (mother/father cannot be self, weight/price
-- cannot be negative) are intentionally NOT defined at the database level.
-- This host's MySQL/MariaDB version rejects them with error #1901 even
-- when added via a separate ALTER TABLE, so they are not portable here.
-- These same rules are already enforced in PHP in
-- admin/cows/add.php and admin/cows/edit.php, so nothing is lost.

-- ---------------------------
-- AUDIT LOGS (recommended)
-- ---------------------------
CREATE TABLE audit_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NULL,
    action VARCHAR(50) NOT NULL,
    table_name VARCHAR(50) NOT NULL,
    record_id INT NULL,
    details TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ---------------------------
-- SEED DATA
-- ---------------------------
INSERT INTO roles (name) VALUES ('Admin'), ('Manager'), ('Staff');

INSERT INTO breeds (name) VALUES ('Holstein Friesian'), ('Jersey'), ('Sahiwal'), ('Local/Deshi'), ('Crossbred');

-- Default admin user, password: Admin@123 (bcrypt hash below, CHANGE after first login)
-- Hash generated for 'Admin@123'
INSERT INTO users (name, email, password_hash, role_id, status)
VALUES ('Administrator', 'admin@bayazidfarm.com', '$2y$10$6b8jeRgbpl/qcUojvJt5c.haER9LSdy3SntyZNaaRlF/hAj4y2j9e', 1, 'active');