-- Land Survey Database
-- Create database
CREATE DATABASE IF NOT EXISTS land_survey_db;
USE land_survey_db;

-- Users table with roles and approval status
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    full_name VARCHAR(100),
    phone VARCHAR(20),
    role ENUM('admin', 'broker', 'user') DEFAULT 'user',
    status ENUM('pending', 'approved', 'rejected', 'suspended') DEFAULT 'pending',
    approved_by INT NULL,
    approved_at TIMESTAMP NULL,
    rejection_reason TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (approved_by) REFERENCES users(id) ON DELETE SET NULL
);

-- Lands table
CREATE TABLE IF NOT EXISTS lands (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    land_name VARCHAR(100) NOT NULL,
    description TEXT,
    location_address VARCHAR(255),
    total_area DECIMAL(15,4) DEFAULT 0,
    area_unit ENUM('sqm', 'acres', 'hectares') DEFAULT 'sqm',
    share_token VARCHAR(64) UNIQUE,
    is_public TINYINT(1) DEFAULT 0,
    status ENUM('active', 'disputed', 'verified') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Beacon points table
CREATE TABLE IF NOT EXISTS beacon_points (
    id INT AUTO_INCREMENT PRIMARY KEY,
    land_id INT NOT NULL,
    latitude DECIMAL(10, 8) NOT NULL,
    longitude DECIMAL(11, 8) NOT NULL,
    point_order INT NOT NULL,
    point_label VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (land_id) REFERENCES lands(id) ON DELETE CASCADE
);

-- Overlap alerts table
CREATE TABLE IF NOT EXISTS overlap_alerts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    land_id_1 INT NOT NULL,
    land_id_2 INT NOT NULL,
    overlap_percentage DECIMAL(5,2),
    alert_message TEXT,
    status ENUM('pending', 'investigating', 'resolved', 'dismissed') DEFAULT 'pending',
    resolved_by INT NULL,
    resolution_notes TEXT NULL,
    is_resolved TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    resolved_at TIMESTAMP NULL,
    FOREIGN KEY (land_id_1) REFERENCES lands(id) ON DELETE CASCADE,
    FOREIGN KEY (land_id_2) REFERENCES lands(id) ON DELETE CASCADE,
    FOREIGN KEY (resolved_by) REFERENCES users(id) ON DELETE SET NULL
);

-- Activity logs for admin tracking
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
);

-- 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
);

-- Link lands to clients (optional - land can belong to client managed by broker)
ALTER TABLE lands ADD COLUMN client_id INT NULL AFTER user_id;
ALTER TABLE lands ADD CONSTRAINT fk_land_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE SET NULL;

-- Insert default payment settings
INSERT 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');

-- Visual boundary points table (squeezed/adjusted polygon to match satellite imagery)
-- This stores user-adjusted coordinates that visually match satellite images
-- while original GPS coordinates remain in beacon_points
CREATE TABLE IF NOT EXISTS visual_boundary_points (
    id INT AUTO_INCREMENT PRIMARY KEY,
    land_id INT NOT NULL,
    latitude DECIMAL(10, 8) NOT NULL,
    longitude DECIMAL(11, 8) NOT NULL,
    point_order INT NOT NULL,
    point_label VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (land_id) REFERENCES lands(id) ON DELETE CASCADE
);

-- Index for faster lookups
CREATE INDEX idx_visual_boundary_land ON visual_boundary_points(land_id);

-- Insert default admin user (password: admin123)
INSERT 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');
