-- ================================================================
-- LAND SALES MANAGEMENT SAAS - DATABASE SCHEMA
-- Database: landsales
-- Run this file to create all tables
-- ================================================================

-- Create database if not exists
CREATE DATABASE IF NOT EXISTS landsales CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE landsales;

-- Disable foreign key checks for clean setup
SET FOREIGN_KEY_CHECKS = 0;

-- Drop existing tables if rebuilding
DROP TABLE IF EXISTS activity_logs;
DROP TABLE IF EXISTS password_resets;
DROP TABLE IF EXISTS notification_templates;
DROP TABLE IF EXISTS settings;
DROP TABLE IF EXISTS payment_schedule;
DROP TABLE IF EXISTS payments;
DROP TABLE IF EXISTS expenses;
DROP TABLE IF EXISTS sales;
DROP TABLE IF EXISTS agents;
DROP TABLE IF EXISTS buyers;
DROP TABLE IF EXISTS plots;
DROP TABLE IF EXISTS projects;
DROP TABLE IF EXISTS subscription_payments;
DROP TABLE IF EXISTS subscriptions;
DROP TABLE IF EXISTS plans;
DROP TABLE IF EXISTS users;

-- ================================================================
-- SAAS PLATFORM TABLES
-- ================================================================

-- Subscription Plans
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,
    max_users INT DEFAULT 3,
    features JSON,
    is_active TINYINT(1) DEFAULT 1,
    sort_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Users Table (Subscribers + Platform Admins)
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_logo VARCHAR(255),
    address TEXT,
    city VARCHAR(100),
    region VARCHAR(100),
    country VARCHAR(50) DEFAULT 'Tanzania',
    role ENUM('super_admin', 'admin', 'subscriber', 'staff') DEFAULT 'subscriber',
    status ENUM('pending', 'active', 'suspended', 'deleted') DEFAULT 'pending',
    email_verified_at TIMESTAMP NULL,
    phone_verified_at TIMESTAMP NULL,
    last_login TIMESTAMP NULL,
    login_count INT DEFAULT 0,
    timezone VARCHAR(50) DEFAULT 'Africa/Dar_es_Salaam',
    language VARCHAR(10) DEFAULT 'en',
    settings JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    INDEX idx_email (email),
    INDEX idx_phone (phone),
    INDEX idx_status (status),
    INDEX idx_role (role)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Subscriptions (link users to plans)
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,
    currency VARCHAR(10) DEFAULT 'TZS',
    status ENUM('trial', 'active', 'expired', 'cancelled', 'suspended') DEFAULT 'trial',
    trial_ends_at TIMESTAMP NULL,
    starts_at TIMESTAMP NOT NULL,
    expires_at TIMESTAMP NOT NULL,
    auto_renew TINYINT(1) DEFAULT 0,
    cancelled_at TIMESTAMP NULL,
    cancellation_reason TEXT,
    grace_period_days INT DEFAULT 7,
    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 COLLATE=utf8mb4_unicode_ci;

-- Subscription Payments (Admin-recorded payments)
CREATE TABLE subscription_payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    subscription_id INT NOT NULL,
    user_id INT NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    currency VARCHAR(10) DEFAULT 'TZS',
    payment_method ENUM('cash', 'bank_transfer', 'mobile_money', 'other') DEFAULT 'cash',
    reference_number VARCHAR(100),
    receipt_number VARCHAR(50),
    payment_date DATE NOT NULL,
    period_start DATE NOT NULL,
    period_end DATE NOT NULL,
    status ENUM('pending', 'confirmed', 'cancelled', 'refunded') DEFAULT 'confirmed',
    notes TEXT,
    recorded_by INT NOT NULL,
    confirmed_by INT NULL,
    confirmed_at TIMESTAMP NULL,
    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,
    FOREIGN KEY (recorded_by) REFERENCES users(id),
    FOREIGN KEY (confirmed_by) REFERENCES users(id),
    INDEX idx_subscription (subscription_id),
    INDEX idx_user (user_id),
    INDEX idx_status (status),
    INDEX idx_payment_date (payment_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- LAND BUSINESS TABLES (Per Subscriber)
-- ================================================================

-- Projects (Land Development Projects)
CREATE TABLE projects (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    name VARCHAR(150) NOT NULL,
    slug VARCHAR(150),
    description TEXT,
    location VARCHAR(255),
    region VARCHAR(100),
    district VARCHAR(100),
    ward VARCHAR(100),
    total_area DECIMAL(15,4),
    area_unit ENUM('sqm', 'acres', 'hectares') DEFAULT 'sqm',
    total_plots INT DEFAULT 0,
    sold_plots INT DEFAULT 0,
    reserved_plots INT DEFAULT 0,
    available_plots INT DEFAULT 0,
    base_price_per_sqm DECIMAL(12,2),
    status ENUM('planning', 'active', 'completed', 'archived') DEFAULT 'active',
    start_date DATE,
    completion_date DATE,
    cover_image VARCHAR(255),
    map_image VARCHAR(255),
    documents JSON,
    settings JSON,
    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_status (status),
    UNIQUE KEY idx_user_slug (user_id, slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Plots
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),
    row_number INT,
    column_number INT,
    size DECIMAL(12,4) NOT NULL,
    size_unit ENUM('sqm', 'acres', 'hectares') DEFAULT 'sqm',
    dimensions VARCHAR(50),
    price DECIMAL(15,2) NOT NULL,
    minimum_deposit DECIMAL(15,2) DEFAULT 0,
    status ENUM('available', 'reserved', 'sold', 'unavailable') DEFAULT 'available',
    plot_type ENUM('residential', 'commercial', 'industrial', 'agricultural', 'mixed') DEFAULT 'residential',
    corner_plot TINYINT(1) DEFAULT 0,
    has_road_access TINYINT(1) DEFAULT 1,
    terrain VARCHAR(50),
    coordinates JSON,
    features JSON,
    notes TEXT,
    reserved_at TIMESTAMP NULL,
    reserved_until TIMESTAMP NULL,
    reserved_by INT NULL,
    sold_at TIMESTAMP NULL,
    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),
    INDEX idx_plot_number (plot_number),
    UNIQUE KEY idx_project_plot (project_id, plot_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Buyers (Customers)
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,
    phone_alt VARCHAR(20),
    national_id VARCHAR(50),
    passport_number VARCHAR(50),
    tin_number VARCHAR(50),
    address TEXT,
    city VARCHAR(100),
    region VARCHAR(100),
    country VARCHAR(50) DEFAULT 'Tanzania',
    date_of_birth DATE,
    gender ENUM('male', 'female', 'other'),
    occupation VARCHAR(100),
    employer VARCHAR(150),
    next_of_kin_name VARCHAR(150),
    next_of_kin_phone VARCHAR(20),
    next_of_kin_relation VARCHAR(50),
    photo VARCHAR(255),
    documents JSON,
    notes TEXT,
    status ENUM('active', 'inactive', 'blacklisted') DEFAULT 'active',
    total_purchases INT DEFAULT 0,
    total_amount DECIMAL(15,2) DEFAULT 0,
    total_paid DECIMAL(15,2) DEFAULT 0,
    total_balance DECIMAL(15,2) DEFAULT 0,
    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),
    INDEX idx_email (email),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Agents (Sales Agents)
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,
    national_id VARCHAR(50),
    address TEXT,
    photo VARCHAR(255),
    commission_type ENUM('percentage', 'fixed') DEFAULT 'percentage',
    commission_rate DECIMAL(5,2) DEFAULT 5.00,
    commission_fixed DECIMAL(12,2) DEFAULT 0,
    bank_name VARCHAR(100),
    bank_account VARCHAR(50),
    bank_branch VARCHAR(100),
    mobile_money_number VARCHAR(20),
    status ENUM('active', 'inactive', 'terminated') DEFAULT 'active',
    total_sales INT DEFAULT 0,
    total_commission DECIMAL(15,2) DEFAULT 0,
    commission_paid DECIMAL(15,2) DEFAULT 0,
    commission_pending DECIMAL(15,2) DEFAULT 0,
    joined_at DATE,
    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),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Sales (Plot Sales)
CREATE TABLE sales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    project_id INT NOT NULL,
    plot_id INT NOT NULL,
    buyer_id INT NOT NULL,
    agent_id INT NULL,
    sale_number VARCHAR(50) NOT NULL,
    sale_date DATE NOT NULL,
    agreed_price DECIMAL(15,2) NOT NULL,
    discount_amount DECIMAL(15,2) DEFAULT 0,
    discount_reason VARCHAR(255),
    final_price DECIMAL(15,2) NOT NULL,
    deposit_amount DECIMAL(15,2) DEFAULT 0,
    payment_plan ENUM('full', 'installment') DEFAULT 'installment',
    installment_months INT DEFAULT 12,
    monthly_amount DECIMAL(15,2) DEFAULT 0,
    total_paid DECIMAL(15,2) DEFAULT 0,
    balance DECIMAL(15,2) DEFAULT 0,
    next_payment_date DATE,
    last_payment_date DATE,
    status ENUM('pending', 'active', 'completed', 'cancelled', 'defaulted') DEFAULT 'active',
    completion_date DATE NULL,
    cancellation_date DATE NULL,
    cancellation_reason TEXT,
    agent_commission DECIMAL(15,2) DEFAULT 0,
    commission_paid TINYINT(1) DEFAULT 0,
    commission_paid_date DATE NULL,
    contract_signed TINYINT(1) DEFAULT 0,
    contract_date DATE,
    contract_file VARCHAR(255),
    documents JSON,
    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,
    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_project (project_id),
    INDEX idx_buyer (buyer_id),
    INDEX idx_status (status),
    INDEX idx_sale_date (sale_date),
    UNIQUE KEY idx_sale_number (user_id, sale_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Payments (Installment Payments)
CREATE TABLE payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    sale_id INT NOT NULL,
    buyer_id INT NOT NULL,
    payment_number VARCHAR(50),
    amount DECIMAL(15,2) NOT NULL,
    payment_method ENUM('cash', 'bank_transfer', 'mobile_money', 'cheque', 'other') DEFAULT 'cash',
    reference_number VARCHAR(100),
    receipt_number VARCHAR(50),
    payment_date DATE NOT NULL,
    due_date DATE,
    is_deposit TINYINT(1) DEFAULT 0,
    installment_number INT DEFAULT 0,
    late_fee DECIMAL(12,2) DEFAULT 0,
    total_amount DECIMAL(15,2) NOT NULL,
    status ENUM('pending', 'confirmed', 'cancelled', 'bounced') DEFAULT 'confirmed',
    notes TEXT,
    recorded_by INT NULL,
    confirmed_by INT NULL,
    confirmed_at TIMESTAMP NULL,
    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 (sale_id) REFERENCES sales(id) ON DELETE CASCADE,
    FOREIGN KEY (buyer_id) REFERENCES buyers(id) ON DELETE CASCADE,
    INDEX idx_user (user_id),
    INDEX idx_sale (sale_id),
    INDEX idx_buyer (buyer_id),
    INDEX idx_status (status),
    INDEX idx_payment_date (payment_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Payment Schedule (Expected installments)
CREATE TABLE payment_schedule (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    sale_id INT NOT NULL,
    installment_number INT NOT NULL,
    due_date DATE NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    paid_amount DECIMAL(15,2) DEFAULT 0,
    status ENUM('pending', 'partial', 'paid', 'overdue', 'waived') DEFAULT 'pending',
    payment_id INT NULL,
    paid_date DATE NULL,
    days_overdue INT DEFAULT 0,
    late_fee DECIMAL(12,2) DEFAULT 0,
    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 (sale_id) REFERENCES sales(id) ON DELETE CASCADE,
    FOREIGN KEY (payment_id) REFERENCES payments(id) ON DELETE SET NULL,
    INDEX idx_user (user_id),
    INDEX idx_sale (sale_id),
    INDEX idx_due_date (due_date),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Expenses (Project Expenses)
CREATE TABLE expenses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    project_id INT NULL,
    category ENUM('survey', 'marketing', 'legal', 'infrastructure', 'utilities', 'staff', 'commission', 'tax', 'office', 'transport', 'other') DEFAULT 'other',
    description VARCHAR(255) NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    expense_date DATE NOT NULL,
    payment_method ENUM('cash', 'bank_transfer', 'mobile_money', 'cheque', 'other') DEFAULT 'cash',
    reference_number VARCHAR(100),
    vendor_name VARCHAR(150),
    vendor_phone VARCHAR(20),
    receipt_image VARCHAR(255),
    documents JSON,
    is_recurring TINYINT(1) DEFAULT 0,
    recurrence_period ENUM('weekly', 'monthly', 'quarterly', 'yearly'),
    status ENUM('pending', 'approved', 'paid', 'cancelled') DEFAULT 'paid',
    approved_by INT 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 (project_id) REFERENCES projects(id) ON DELETE SET NULL,
    INDEX idx_user (user_id),
    INDEX idx_project (project_id),
    INDEX idx_category (category),
    INDEX idx_expense_date (expense_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- SYSTEM TABLES
-- ================================================================

-- Activity Logs
CREATE TABLE activity_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NULL,
    action VARCHAR(100) NOT NULL,
    entity_type VARCHAR(50),
    entity_id INT,
    description TEXT,
    old_values JSON,
    new_values JSON,
    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),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- System Settings
CREATE TABLE settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) NOT NULL UNIQUE,
    setting_value TEXT,
    setting_type ENUM('string', 'number', 'boolean', 'json') DEFAULT 'string',
    description TEXT,
    is_public TINYINT(1) DEFAULT 0,
    updated_by INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Password Reset Tokens
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,
    used_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_email (email),
    INDEX idx_token (token)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Notification Templates
CREATE TABLE notification_templates (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) NOT NULL UNIQUE,
    type ENUM('email', 'sms', 'both') DEFAULT 'email',
    subject VARCHAR(255),
    body TEXT,
    variables JSON,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Re-enable foreign key checks
SET FOREIGN_KEY_CHECKS = 1;

-- ================================================================
-- INSERT DEFAULT DATA
-- ================================================================

-- Default Plans
INSERT INTO plans (name, slug, description, price_monthly, price_quarterly, price_semiannual, price_yearly, max_projects, max_plots, max_agents, max_users, features, sort_order) VALUES
('Starter', 'starter', 'Perfect for small land sellers with up to 100 plots', 50000, 140000, 270000, 500000, 2, 100, 3, 2, '{"reports": true, "reminders": true, "export": false, "sms": false}', 1),
('Professional', 'professional', 'For growing land developers with up to 500 plots', 100000, 280000, 540000, 1000000, 10, 500, 10, 5, '{"reports": true, "reminders": true, "export": true, "sms": true, "api": false}', 2),
('Enterprise', 'enterprise', 'For large scale operations with unlimited plots', 200000, 560000, 1080000, 2000000, -1, -1, -1, -1, '{"reports": true, "reminders": true, "export": true, "sms": true, "api": true, "white_label": true}', 3);

-- Default Settings
INSERT INTO settings (setting_key, setting_value, setting_type, description, is_public) VALUES
('site_name', 'LandSales Pro', 'string', 'Website name', 1),
('site_tagline', 'Professional Land Sales Management', 'string', 'Website tagline', 1),
('site_description', 'The complete land sales management system for African land developers and sellers', 'string', 'Website description', 1),
('support_email', 'support@landsales.co.tz', 'string', 'Support email', 1),
('support_phone', '+255 XXX XXX XXX', 'string', 'Support phone', 1),
('whatsapp_number', '+255XXXXXXXXX', 'string', 'WhatsApp number', 1),
('currency', 'TZS', 'string', 'Default currency', 1),
('currency_symbol', 'TZS', 'string', 'Currency symbol', 1),
('timezone', 'Africa/Dar_es_Salaam', 'string', 'Default timezone', 0),
('date_format', 'd M Y', 'string', 'Date format', 0),
('trial_days', '14', 'number', 'Trial period days', 0),
('grace_period_days', '7', 'number', 'Subscription grace period', 0),
('late_payment_fee_percent', '5', 'number', 'Late payment fee percentage', 0),
('default_installment_months', '12', 'number', 'Default installment period', 0),
('default_deposit_percent', '30', 'number', 'Default deposit percentage', 0);

-- Default Super Admin (password: admin123)
INSERT INTO users (email, phone, password, full_name, company_name, role, status, email_verified_at) VALUES
('admin@landsales.co.tz', '0700000000', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'System Administrator', 'LandSales Pro', 'super_admin', 'active', NOW());

-- Notification Templates
INSERT INTO notification_templates (name, slug, type, subject, body, variables) VALUES
('Welcome Email', 'welcome', 'email', 'Welcome to LandSales Pro!', 'Dear {name},\n\nWelcome to LandSales Pro! Your account has been created successfully.\n\nYou can now login and start managing your land sales.\n\nBest regards,\nLandSales Pro Team', '["name", "email"]'),
('Payment Reminder', 'payment-reminder', 'sms', NULL, 'Dear {buyer_name}, your payment of {amount} for plot {plot_number} is due on {due_date}. Please make payment to avoid late fees. - {company_name}', '["buyer_name", "amount", "plot_number", "due_date", "company_name"]'),
('Payment Received', 'payment-received', 'both', 'Payment Received - {amount}', 'Dear {buyer_name},\n\nWe have received your payment of {amount} for plot {plot_number}.\n\nReceipt No: {receipt_number}\nBalance: {balance}\n\nThank you for your payment.\n\n{company_name}', '["buyer_name", "amount", "plot_number", "receipt_number", "balance", "company_name"]'),
('Subscription Expiring', 'subscription-expiring', 'email', 'Your subscription is expiring soon', 'Dear {name},\n\nYour LandSales Pro subscription will expire on {expiry_date}.\n\nPlease renew your subscription to continue using all features.\n\nBest regards,\nLandSales Pro Team', '["name", "expiry_date", "plan_name"]');

-- ================================================================
-- TRIGGERS FOR AUTO-UPDATES
-- ================================================================

DELIMITER //

-- Update project plot counts when plot status changes
CREATE TRIGGER after_plot_update
AFTER UPDATE ON plots
FOR EACH ROW
BEGIN
    IF OLD.status != NEW.status THEN
        UPDATE projects SET
            sold_plots = (SELECT COUNT(*) FROM plots WHERE project_id = NEW.project_id AND status = 'sold'),
            reserved_plots = (SELECT COUNT(*) FROM plots WHERE project_id = NEW.project_id AND status = 'reserved'),
            available_plots = (SELECT COUNT(*) FROM plots WHERE project_id = NEW.project_id AND status = 'available'),
            total_plots = (SELECT COUNT(*) FROM plots WHERE project_id = NEW.project_id)
        WHERE id = NEW.project_id;
    END IF;
END//

-- Update project plot counts when plot is inserted
CREATE TRIGGER after_plot_insert
AFTER INSERT ON plots
FOR EACH ROW
BEGIN
    UPDATE projects SET
        total_plots = (SELECT COUNT(*) FROM plots WHERE project_id = NEW.project_id),
        available_plots = (SELECT COUNT(*) FROM plots WHERE project_id = NEW.project_id AND status = 'available'),
        sold_plots = (SELECT COUNT(*) FROM plots WHERE project_id = NEW.project_id AND status = 'sold'),
        reserved_plots = (SELECT COUNT(*) FROM plots WHERE project_id = NEW.project_id AND status = 'reserved')
    WHERE id = NEW.project_id;
END//

-- Update sale totals when payment is made
CREATE TRIGGER after_payment_insert
AFTER INSERT ON payments
FOR EACH ROW
BEGIN
    IF NEW.status = 'confirmed' THEN
        UPDATE sales SET
            total_paid = (SELECT COALESCE(SUM(total_amount), 0) FROM payments WHERE sale_id = NEW.sale_id AND status = 'confirmed'),
            balance = final_price - (SELECT COALESCE(SUM(total_amount), 0) FROM payments WHERE sale_id = NEW.sale_id AND status = 'confirmed'),
            last_payment_date = NEW.payment_date
        WHERE id = NEW.sale_id;

        -- Update buyer totals
        UPDATE buyers SET
            total_paid = (SELECT COALESCE(SUM(total_paid), 0) FROM sales WHERE buyer_id = NEW.buyer_id),
            total_balance = (SELECT COALESCE(SUM(balance), 0) FROM sales WHERE buyer_id = NEW.buyer_id)
        WHERE id = NEW.buyer_id;
    END IF;
END//

-- Update buyer stats when sale is made
CREATE TRIGGER after_sale_insert
AFTER INSERT ON sales
FOR EACH ROW
BEGIN
    UPDATE buyers SET
        total_purchases = total_purchases + 1,
        total_amount = total_amount + NEW.final_price,
        total_balance = total_balance + NEW.balance
    WHERE id = NEW.buyer_id;

    -- Update agent stats if agent assigned
    IF NEW.agent_id IS NOT NULL THEN
        UPDATE agents SET
            total_sales = total_sales + 1,
            total_commission = total_commission + NEW.agent_commission,
            commission_pending = commission_pending + NEW.agent_commission
        WHERE id = NEW.agent_id;
    END IF;
END//

DELIMITER ;

-- ================================================================
-- VIEWS FOR COMMON QUERIES
-- ================================================================

-- Active subscriptions view
CREATE OR REPLACE VIEW v_active_subscriptions AS
SELECT
    s.*,
    u.full_name,
    u.company_name,
    u.email,
    u.phone,
    p.name as plan_name,
    p.max_projects,
    p.max_plots,
    p.max_agents
FROM subscriptions s
JOIN users u ON s.user_id = u.id
JOIN plans p ON s.plan_id = p.id
WHERE s.status IN ('active', 'trial')
AND s.expires_at >= NOW();

-- Sales summary view
CREATE OR REPLACE VIEW v_sales_summary AS
SELECT
    s.user_id,
    COUNT(*) as total_sales,
    SUM(s.final_price) as total_revenue,
    SUM(s.total_paid) as total_collected,
    SUM(s.balance) as total_outstanding,
    SUM(CASE WHEN s.status = 'completed' THEN 1 ELSE 0 END) as completed_sales,
    SUM(CASE WHEN s.status = 'active' THEN 1 ELSE 0 END) as active_sales
FROM sales s
WHERE s.status != 'cancelled'
GROUP BY s.user_id;

-- Overdue payments view
CREATE OR REPLACE VIEW v_overdue_payments AS
SELECT
    ps.*,
    s.sale_number,
    b.full_name as buyer_name,
    b.phone as buyer_phone,
    p.plot_number,
    pr.name as project_name,
    DATEDIFF(CURDATE(), ps.due_date) as days_overdue
FROM payment_schedule ps
JOIN sales s ON ps.sale_id = s.id
JOIN buyers b ON s.buyer_id = b.id
JOIN plots p ON s.plot_id = p.id
JOIN projects pr ON s.project_id = pr.id
WHERE ps.status IN ('pending', 'partial')
AND ps.due_date < CURDATE()
ORDER BY ps.due_date ASC;

-- ================================================================
-- DONE
-- ================================================================
SELECT 'Database schema created successfully!' as message;
