<?php
/**
 * Database Setup Script
 * LandSales Pro - Run this to create/reset the database
 *
 * Access via: http://localhost/landsales/database/setup.php
 */

// Basic config
$host = 'localhost';
$user = 'root';
$pass = '';
$dbname = 'landsales';

echo "<h1>LandSales Pro - Database Setup</h1>";
echo "<pre style='background:#f5f5f5; padding:20px; font-family:monospace;'>";

// Connect without database
$conn = new mysqli($host, $user, $pass);
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

echo "Connected to MySQL successfully.\n\n";

// Create database
$conn->query("CREATE DATABASE IF NOT EXISTS `{$dbname}` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");
echo "Database '{$dbname}' created/verified.\n";

$conn->select_db($dbname);
echo "Selected database '{$dbname}'.\n\n";

// Disable foreign key checks
$conn->query("SET FOREIGN_KEY_CHECKS = 0");

// Drop existing tables
$tables = ['activity_logs', 'password_resets', 'settings', 'payment_schedule', 'payments',
           'expenses', 'sales', 'agents', 'buyers', 'plot_beacons', 'plots', 'projects',
           'subscription_payments', 'subscriptions', 'plans', 'staff_permissions', 'staff_users', 'users'];

foreach ($tables as $table) {
    $conn->query("DROP TABLE IF EXISTS `{$table}`");
}
echo "Dropped existing tables.\n\n";

// Create tables
echo "Creating tables...\n";

// Plans
$conn->query("CREATE TABLE plans (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    slug VARCHAR(50) NOT NULL UNIQUE,
    description TEXT,
    price_monthly DECIMAL(12,2) NOT NULL DEFAULT 50000,
    price_quarterly DECIMAL(12,2) NOT NULL DEFAULT 140000,
    price_semiannual DECIMAL(12,2) NOT NULL DEFAULT 270000,
    price_yearly DECIMAL(12,2) NOT NULL DEFAULT 500000,
    max_projects INT DEFAULT 5,
    max_plots INT DEFAULT 500,
    max_agents INT DEFAULT 10,
    features JSON,
    is_active TINYINT(1) DEFAULT 1,
    status ENUM('active', 'inactive') DEFAULT 'active',
    sort_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
echo "  - plans\n";

// Users
$conn->query("CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(100) NOT NULL UNIQUE,
    phone VARCHAR(20),
    password VARCHAR(255) NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    company_name VARCHAR(150),
    company_phone VARCHAR(20),
    company_email VARCHAR(100),
    company_address TEXT,
    role ENUM('super_admin', 'admin', 'subscriber', 'staff') DEFAULT 'subscriber',
    status ENUM('pending', 'active', 'suspended') DEFAULT 'pending',
    last_login TIMESTAMP NULL,
    login_count INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_email (email),
    INDEX idx_role (role),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
echo "  - users\n";

// Subscriptions
$conn->query("CREATE TABLE subscriptions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    plan_id INT NOT NULL,
    billing_cycle ENUM('monthly', 'quarterly', 'semiannual', 'yearly') DEFAULT 'monthly',
    amount DECIMAL(12,2) NOT NULL,
    status ENUM('trial', 'active', 'expired', 'cancelled') DEFAULT 'trial',
    starts_at DATETIME NOT NULL,
    expires_at DATETIME NOT NULL,
    trial_ends_at DATE NULL,
    notes TEXT,
    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,
    FOREIGN KEY (plan_id) REFERENCES plans(id),
    INDEX idx_user (user_id),
    INDEX idx_status (status),
    INDEX idx_expires (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
echo "  - subscriptions\n";

// Subscription Payments
$conn->query("CREATE TABLE subscription_payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    subscription_id INT NOT NULL,
    user_id INT NOT NULL,
    receipt_number VARCHAR(50),
    amount DECIMAL(12,2) NOT NULL,
    payment_method VARCHAR(50) DEFAULT 'cash',
    reference_number VARCHAR(100),
    payment_date DATE NOT NULL,
    period_start DATE,
    period_end DATE,
    status ENUM('pending', 'confirmed', 'cancelled') DEFAULT 'confirmed',
    notes TEXT,
    recorded_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (subscription_id) REFERENCES subscriptions(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
echo "  - subscription_payments\n";

// Projects
$conn->query("CREATE TABLE projects (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    name VARCHAR(150) NOT NULL,
    description TEXT,
    location VARCHAR(255),
    total_area DECIMAL(15,4),
    area_unit ENUM('sqm', 'acres') DEFAULT 'sqm',
    status ENUM('planning', 'active', 'completed') 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,
    INDEX idx_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
echo "  - projects\n";

// Plots
$conn->query("CREATE TABLE plots (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    project_id INT NOT NULL,
    plot_number VARCHAR(50) NOT NULL,
    block VARCHAR(50),
    size DECIMAL(12,4) NOT NULL,
    size_unit ENUM('sqm', 'acres') DEFAULT 'sqm',
    calculated_size DECIMAL(12,4) NULL,
    price DECIMAL(15,2) NOT NULL,
    price_per_sqm DECIMAL(15,2) NULL,
    center_latitude DECIMAL(10,8) NULL,
    center_longitude DECIMAL(11,8) NULL,
    status ENUM('available', 'reserved', 'sold') DEFAULT 'available',
    notes TEXT,
    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,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    INDEX idx_user (user_id),
    INDEX idx_project (project_id),
    INDEX idx_status (status),
    UNIQUE KEY idx_project_plot (project_id, plot_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
echo "  - plots\n";

// Plot Beacons (Corner coordinates for polygon)
$conn->query("CREATE TABLE plot_beacons (
    id INT AUTO_INCREMENT PRIMARY KEY,
    plot_id INT NOT NULL,
    beacon_number INT NOT NULL,
    beacon_name VARCHAR(50),
    latitude DECIMAL(10,8) NOT NULL,
    longitude DECIMAL(11,8) NOT NULL,
    accuracy DECIMAL(8,2) NULL COMMENT 'GPS accuracy in meters',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (plot_id) REFERENCES plots(id) ON DELETE CASCADE,
    INDEX idx_plot (plot_id),
    UNIQUE KEY idx_plot_beacon (plot_id, beacon_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
echo "  - plot_beacons\n";

// Buyers
$conn->query("CREATE TABLE buyers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    full_name VARCHAR(150) NOT NULL,
    email VARCHAR(100),
    phone VARCHAR(20) NOT NULL,
    id_type VARCHAR(50),
    id_number VARCHAR(50),
    address TEXT,
    notes TEXT,
    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,
    INDEX idx_user (user_id),
    INDEX idx_phone (phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
echo "  - buyers\n";

// Agents
$conn->query("CREATE TABLE agents (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    full_name VARCHAR(150) NOT NULL,
    email VARCHAR(100),
    phone VARCHAR(20) NOT NULL,
    commission_rate DECIMAL(5,2) DEFAULT 5.00,
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
echo "  - agents\n";

// Sales
$conn->query("CREATE TABLE sales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    sale_number VARCHAR(50) NOT NULL,
    plot_id INT NOT NULL,
    buyer_id INT NOT NULL,
    agent_id INT NULL,
    sale_date DATE NOT NULL,
    total_price DECIMAL(15,2) NOT NULL,
    deposit_amount DECIMAL(15,2) DEFAULT 0,
    payment_type ENUM('cash', 'installment') DEFAULT 'cash',
    installment_months INT DEFAULT 0,
    status ENUM('active', 'completed', 'cancelled') DEFAULT 'active',
    notes TEXT,
    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,
    FOREIGN KEY (plot_id) REFERENCES plots(id) ON DELETE CASCADE,
    FOREIGN KEY (buyer_id) REFERENCES buyers(id) ON DELETE CASCADE,
    FOREIGN KEY (agent_id) REFERENCES agents(id) ON DELETE SET NULL,
    INDEX idx_user (user_id),
    INDEX idx_buyer (buyer_id),
    INDEX idx_status (status),
    UNIQUE KEY idx_sale_number (user_id, sale_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
echo "  - sales\n";

// Payments
$conn->query("CREATE TABLE payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    sale_id INT NOT NULL,
    payment_number VARCHAR(50),
    amount DECIMAL(15,2) NOT NULL,
    payment_method ENUM('cash', 'bank', 'mobile', 'cheque') DEFAULT 'cash',
    reference VARCHAR(100),
    payment_date DATE NOT NULL,
    status ENUM('pending', 'confirmed', 'cancelled') DEFAULT 'confirmed',
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (sale_id) REFERENCES sales(id) ON DELETE CASCADE,
    INDEX idx_user (user_id),
    INDEX idx_sale (sale_id),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
echo "  - payments\n";

// Payment Schedule
$conn->query("CREATE TABLE payment_schedule (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sale_id INT NOT NULL,
    installment_number INT NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    due_date DATE NOT NULL,
    status ENUM('pending', 'paid', 'overdue') DEFAULT 'pending',
    paid_date DATE NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (sale_id) REFERENCES sales(id) ON DELETE CASCADE,
    INDEX idx_sale (sale_id),
    INDEX idx_due_date (due_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
echo "  - payment_schedule\n";

// Activity Logs
$conn->query("CREATE TABLE activity_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NULL,
    action VARCHAR(100) NOT NULL,
    description TEXT,
    entity_type VARCHAR(50) NULL,
    entity_id INT NULL,
    ip_address VARCHAR(45),
    user_agent VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user (user_id),
    INDEX idx_action (action),
    INDEX idx_entity (entity_type, entity_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
echo "  - activity_logs\n";

// Settings
$conn->query("CREATE TABLE settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) NOT NULL UNIQUE,
    setting_value TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
echo "  - settings\n";

// Password Resets
$conn->query("CREATE TABLE password_resets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(100) NOT NULL,
    token VARCHAR(100) NOT NULL,
    expires_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_token (token)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
echo "  - password_resets\n";

// Staff Users (created by subscribers to use their system)
$conn->query("CREATE TABLE staff_users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    owner_id INT NOT NULL COMMENT 'The subscriber who owns this staff user',
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    password VARCHAR(255) NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    status ENUM('active', 'inactive') DEFAULT 'active',
    last_login TIMESTAMP NULL,
    login_count INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_owner (owner_id),
    INDEX idx_email (email),
    INDEX idx_status (status),
    UNIQUE KEY idx_owner_email (owner_id, email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
echo "  - staff_users\n";

// Staff Permissions
$conn->query("CREATE TABLE staff_permissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    staff_id INT NOT NULL,
    permission VARCHAR(50) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (staff_id) REFERENCES staff_users(id) ON DELETE CASCADE,
    INDEX idx_staff (staff_id),
    UNIQUE KEY idx_staff_permission (staff_id, permission)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
echo "  - staff_permissions\n";

// Re-enable foreign key checks
$conn->query("SET FOREIGN_KEY_CHECKS = 1");

echo "\nAll tables created successfully!\n\n";

// Insert default data
echo "Inserting default data...\n";

// Plans (NULL = unlimited)
$conn->query("INSERT INTO plans (name, slug, description, price_monthly, price_quarterly, price_semiannual, price_yearly, max_projects, max_plots, max_agents, features, sort_order) VALUES
('Starter', 'starter', 'Perfect for small land sellers', 50000, 140000, 270000, 500000, 5, 100, NULL, '{\"reports\":true}', 1),
('Professional', 'professional', 'For growing land developers', 100000, 280000, 540000, 1000000, 10, 500, NULL, '{\"reports\":true,\"export\":true}', 2),
('Enterprise', 'enterprise', 'For large scale operations', 200000, 560000, 1080000, 2000000, NULL, NULL, NULL, '{\"reports\":true,\"export\":true,\"api\":true}', 3)");
echo "  - Default plans inserted\n";

// Admin user (password: admin123)
$adminPassword = password_hash('admin123', PASSWORD_DEFAULT);
$conn->query("INSERT INTO users (email, phone, password, full_name, company_name, role, status) VALUES
('admin@landsales.co.tz', '0700000000', '{$adminPassword}', 'System Administrator', 'LandSales Pro', 'super_admin', 'active')");
echo "  - Admin user created (admin@landsales.co.tz / admin123)\n";

// Demo subscriber (password: demo123)
$demoPassword = password_hash('demo123', PASSWORD_DEFAULT);
$conn->query("INSERT INTO users (email, phone, password, full_name, company_name, role, status) VALUES
('demo@example.com', '0711111111', '{$demoPassword}', 'Demo User', 'Demo Land Company', 'subscriber', 'active')");
$demoUserId = $conn->insert_id;

// Create subscription for demo user
$startsAt = date('Y-m-d H:i:s');
$expiresAt = date('Y-m-d H:i:s', strtotime('+1 month'));
$conn->query("INSERT INTO subscriptions (user_id, plan_id, billing_cycle, amount, status, starts_at, expires_at) VALUES
({$demoUserId}, 2, 'monthly', 100000, 'active', '{$startsAt}', '{$expiresAt}')");
echo "  - Demo user created (demo@example.com / demo123)\n";

// Settings
$conn->query("INSERT INTO settings (setting_key, setting_value) VALUES
('site_name', 'LandSales Pro'),
('currency', 'TZS'),
('trial_days', '14')");
echo "  - Default settings inserted\n";

echo "\n";
echo "========================================\n";
echo "DATABASE SETUP COMPLETE!\n";
echo "========================================\n\n";
echo "You can now:\n";
echo "1. Visit http://localhost/landsales/\n";
echo "2. Login as admin: admin@landsales.co.tz / admin123\n";
echo "3. Login as demo user: demo@example.com / demo123\n\n";
echo "DELETE THIS FILE AFTER SETUP FOR SECURITY!\n";
echo "</pre>";

echo "<p style='margin-top:20px;'>";
echo "<a href='../home.php' style='padding:10px 20px; background:#667eea; color:white; text-decoration:none; border-radius:5px;'>Go to Homepage</a> ";
echo "<a href='../login.php' style='padding:10px 20px; background:#22c55e; color:white; text-decoration:none; border-radius:5px; margin-left:10px;'>Login</a>";
echo "</p>";

$conn->close();
?>
