-- Run this if you already have the old database
-- This will add the new columns to existing tables (skips if already exists)

USE land_survey_db;

-- Add columns to users table (one by one, ignore errors if exists)
ALTER TABLE users ADD COLUMN role ENUM('admin', 'broker', 'user') DEFAULT 'user' AFTER phone;
ALTER TABLE users ADD COLUMN status ENUM('pending', 'approved', 'rejected', 'suspended') DEFAULT 'pending' AFTER role;
ALTER TABLE users ADD COLUMN approved_by INT NULL AFTER status;
ALTER TABLE users ADD COLUMN approved_at TIMESTAMP NULL AFTER approved_by;
ALTER TABLE users ADD COLUMN rejection_reason TEXT NULL AFTER approved_at;

-- Add new column to lands table
ALTER TABLE lands ADD COLUMN status ENUM('active', 'disputed', 'verified') DEFAULT 'active' AFTER is_public;

-- Update overlap_alerts table
ALTER TABLE overlap_alerts ADD COLUMN status ENUM('pending', 'investigating', 'resolved', 'dismissed') DEFAULT 'pending' AFTER alert_message;
ALTER TABLE overlap_alerts ADD COLUMN resolved_by INT NULL AFTER status;
ALTER TABLE overlap_alerts ADD COLUMN resolution_notes TEXT NULL AFTER resolved_by;
ALTER TABLE overlap_alerts ADD COLUMN resolved_at TIMESTAMP NULL AFTER is_resolved;

-- Create activity_logs table
CREATE TABLE IF NOT EXISTS activity_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NULL,
    action VARCHAR(100) NOT NULL,
    description TEXT,
    ip_address VARCHAR(45),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

-- Update existing admin user or insert new one
UPDATE users SET role = 'admin', status = 'approved' WHERE username = 'admin';

-- If no admin exists, insert one (password: admin123)
INSERT IGNORE INTO users (username, email, password, full_name, role, status) VALUES
('admin', 'admin@landsurvey.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'System Administrator', 'admin', 'approved');

-- ================================================
-- NEW ADDITIONS: Location system and enhanced beacon points
-- ================================================

-- Add beacon_number and additional_info columns to beacon_points
ALTER TABLE beacon_points ADD COLUMN beacon_number VARCHAR(50) NULL AFTER point_label;
ALTER TABLE beacon_points ADD COLUMN additional_info TEXT NULL AFTER beacon_number;

-- Add region, district, and village columns to lands
ALTER TABLE lands ADD COLUMN region_id INT NULL AFTER location_address;
ALTER TABLE lands ADD COLUMN district_id INT NULL AFTER region_id;
ALTER TABLE lands ADD COLUMN village VARCHAR(100) NULL AFTER district_id;

-- Create regions table
CREATE TABLE IF NOT EXISTS regions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create districts table
CREATE TABLE IF NOT EXISTS districts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    region_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (region_id) REFERENCES regions(id) ON DELETE CASCADE
);

-- Create villages table
CREATE TABLE IF NOT EXISTS villages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    district_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    added_by INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (district_id) REFERENCES districts(id) ON DELETE CASCADE,
    FOREIGN KEY (added_by) REFERENCES users(id) ON DELETE SET NULL
);

-- Create beacon landmarks table (for landmarks between beacon points)
CREATE TABLE IF NOT EXISTS beacon_landmarks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    land_id INT NOT NULL,
    from_point INT NOT NULL,
    to_point INT NOT NULL,
    landmark_description TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (land_id) REFERENCES lands(id) ON DELETE CASCADE
);

-- NOTE: Run the tanzania_locations.sql file separately to populate regions and districts

-- ================================================
-- CLIENT MANAGEMENT AND PAYMENT SYSTEM
-- ================================================

-- Clients table (customers added by brokers/agents)
CREATE TABLE IF NOT EXISTS clients (
    id INT AUTO_INCREMENT PRIMARY KEY,
    broker_id INT NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    email VARCHAR(100),
    phone VARCHAR(20) NOT NULL,
    national_id VARCHAR(50),
    address TEXT,
    notes TEXT,
    access_status ENUM('active', 'suspended', 'expired') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (broker_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Payment settings table (admin controls)
CREATE TABLE IF NOT EXISTS payment_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(50) NOT NULL UNIQUE,
    setting_value VARCHAR(255) NOT NULL,
    description TEXT,
    updated_by INT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
);

-- Client payments table
CREATE TABLE IF NOT EXISTS client_payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    client_id INT NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    payment_year INT NOT NULL,
    payment_date DATE NOT NULL,
    payment_method ENUM('cash', 'bank_transfer', 'mobile_money', 'other') DEFAULT 'cash',
    reference_number VARCHAR(100),
    receipt_number VARCHAR(100),
    notes TEXT,
    status ENUM('pending', 'confirmed', 'cancelled') DEFAULT 'confirmed',
    recorded_by INT NOT NULL,
    confirmed_by INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    FOREIGN KEY (recorded_by) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (confirmed_by) REFERENCES users(id) ON DELETE SET NULL
);

-- Add client_id column to lands table
ALTER TABLE lands ADD COLUMN client_id INT NULL AFTER user_id;

-- Insert default payment settings (ignore if already exists)
INSERT IGNORE INTO payment_settings (setting_key, setting_value, description) VALUES
('yearly_fee', '50000', 'Yearly subscription fee in TZS'),
('grace_period_days', '30', 'Grace period in days after payment due date'),
('payment_due_month', '1', 'Month when payment is due (1-12)'),
('payment_due_day', '1', 'Day of month when payment is due')
