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

CREATE TABLE users (
 id INT AUTO_INCREMENT PRIMARY KEY,
 full_name VARCHAR(120) NOT NULL,
 email VARCHAR(160) NOT NULL UNIQUE,
 phone VARCHAR(40) NULL,
 password_hash VARCHAR(255) NOT NULL,
 role ENUM('admin','manager','collector','accounts','farmer') NOT NULL DEFAULT 'admin',
 farmer_id INT NULL,
 is_active TINYINT(1) NOT NULL DEFAULT 1,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE produce_types (
 id INT AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(80) NOT NULL UNIQUE,
 unit VARCHAR(20) NOT NULL DEFAULT 'KG',
 default_rate DECIMAL(12,2) NOT NULL DEFAULT 0,
 quality_rules TEXT NULL,
 is_active TINYINT(1) NOT NULL DEFAULT 1,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE collection_centers (
 id INT AUTO_INCREMENT PRIMARY KEY,
 center_code VARCHAR(30) NOT NULL UNIQUE,
 center_name VARCHAR(120) NOT NULL,
 location VARCHAR(160) NULL,
 manager_name VARCHAR(120) NULL,
 phone VARCHAR(40) NULL,
 is_active TINYINT(1) NOT NULL DEFAULT 1,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE farmers (
 id INT AUTO_INCREMENT PRIMARY KEY,
 farmer_no VARCHAR(40) NOT NULL UNIQUE,
 full_name VARCHAR(140) NOT NULL,
 phone VARCHAR(40) NOT NULL,
 national_id VARCHAR(60) NULL,
 village VARCHAR(120) NULL,
 center_id INT NULL,
 produce_type_id INT NULL,
 mpesa_number VARCHAR(40) NULL,
 bank_name VARCHAR(100) NULL,
 bank_account VARCHAR(80) NULL,
 farm_size DECIMAL(10,2) DEFAULT 0,
 livestock_count INT DEFAULT 0,
 next_of_kin VARCHAR(140) NULL,
 status ENUM('ACTIVE','SUSPENDED','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(center_id) REFERENCES collection_centers(id) ON DELETE SET NULL,
 FOREIGN KEY(produce_type_id) REFERENCES produce_types(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE routes (
 id INT AUTO_INCREMENT PRIMARY KEY,
 route_code VARCHAR(40) NOT NULL UNIQUE,
 route_name VARCHAR(120) NOT NULL,
 center_id INT NULL,
 collector_id INT NULL,
 vehicle_no VARCHAR(40) NULL,
 notes TEXT NULL,
 is_active TINYINT(1) NOT NULL DEFAULT 1,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(center_id) REFERENCES collection_centers(id) ON DELETE SET NULL,
 FOREIGN KEY(collector_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE collections (
 id INT AUTO_INCREMENT PRIMARY KEY,
 receipt_no VARCHAR(50) NOT NULL UNIQUE,
 farmer_id INT NOT NULL,
 produce_type_id INT NOT NULL,
 route_id INT NULL,
 center_id INT NULL,
 collector_id INT NULL,
 collection_date DATE NOT NULL,
 quantity DECIMAL(12,2) NOT NULL DEFAULT 0,
 rejected_quantity DECIMAL(12,2) NOT NULL DEFAULT 0,
 grade VARCHAR(50) NULL,
 quality_notes VARCHAR(255) NULL,
 rate DECIMAL(12,2) NOT NULL DEFAULT 0,
 gross_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
 status ENUM('POSTED','VOID') NOT NULL DEFAULT 'POSTED',
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(farmer_id) REFERENCES farmers(id),
 FOREIGN KEY(produce_type_id) REFERENCES produce_types(id),
 FOREIGN KEY(route_id) REFERENCES routes(id) ON DELETE SET NULL,
 FOREIGN KEY(center_id) REFERENCES collection_centers(id) ON DELETE SET NULL,
 FOREIGN KEY(collector_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE deductions (
 id INT AUTO_INCREMENT PRIMARY KEY,
 farmer_id INT NOT NULL,
 deduction_type ENUM('ADVANCE','LOAN','INPUT','SACCO','TRANSPORT','PENALTY','OTHER') NOT NULL,
 description VARCHAR(255) NOT NULL,
 amount DECIMAL(12,2) NOT NULL DEFAULT 0,
 balance DECIMAL(12,2) NOT NULL DEFAULT 0,
 deduction_date DATE NOT NULL,
 status ENUM('OPEN','CLEARED','CANCELLED') NOT NULL DEFAULT 'OPEN',
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(farmer_id) REFERENCES farmers(id)
) ENGINE=InnoDB;

CREATE TABLE payments (
 id INT AUTO_INCREMENT PRIMARY KEY,
 batch_no VARCHAR(50) NOT NULL,
 farmer_id INT NOT NULL,
 period_from DATE NOT NULL,
 period_to DATE NOT NULL,
 gross_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
 deductions_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
 net_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
 payment_method ENUM('MPESA','BANK','CASH') NOT NULL DEFAULT 'MPESA',
 reference_no VARCHAR(80) NULL,
 status ENUM('PENDING','APPROVED','PAID','FAILED') NOT NULL DEFAULT 'PENDING',
 paid_at DATETIME NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(farmer_id) REFERENCES farmers(id)
) ENGINE=InnoDB;

INSERT INTO produce_types(name,unit,default_rate,quality_rules) VALUES
('Milk','Litre',55,'Check smell, density, rejected litres and temperature'),
('Coffee','KG',90,'Grade by cherry/parchment quality and moisture'),
('Avocado','KG',35,'Grade by size, maturity and damage'),
('Tea','KG',25,'Two leaves and a bud standard');

INSERT INTO collection_centers(center_code,center_name,location,manager_name,phone) VALUES
('CTR001','Main Collection Center','Nandi Hills','Center Manager','0700000000');

INSERT INTO users(full_name,email,phone,password_hash,role) VALUES
('System Admin','admin@agricollect.local','0700000000','$2y$12$nWBqzi6oX390l9cvPdVURuTv3b0yftS1G8DoLfQXalPMSGXlu7mTO','admin'),
('Field Collector','collector@agricollect.local','0711111111','$2y$12$nWBqzi6oX390l9cvPdVURuTv3b0yftS1G8DoLfQXalPMSGXlu7mTO','collector');