CREATE TABLE IF NOT EXISTS users (
    id TEXT PRIMARY KEY,
    organization_tenant_id TEXT,
    email TEXT UNIQUE NOT NULL,
    password_hash TEXT NOT NULL,
    first_name TEXT,
    last_name TEXT,
    phone TEXT,
    avatar_url TEXT,
    role TEXT DEFAULT 'tenant',
    permissions TEXT DEFAULT '[]',
    status TEXT DEFAULT 'active',
    email_verified_at TEXT,
    last_login_at TEXT,
    last_login_ip TEXT,
    two_factor_enabled INTEGER DEFAULT 0,
    failed_login_attempts INTEGER DEFAULT 0,
    locked_until TEXT,
    password_changed_at TEXT,
    password_reset_token TEXT,
    password_reset_expires TEXT,
    email_verification_token TEXT,
    created_at TEXT DEFAULT (datetime('now','localtime')),
    updated_at TEXT DEFAULT (datetime('now','localtime')),
    deleted_at TEXT,
    created_by TEXT,
    updated_by TEXT
);

CREATE TABLE IF NOT EXISTS tenants (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    slug TEXT UNIQUE NOT NULL,
    email TEXT,
    phone TEXT,
    website TEXT,
    business_type TEXT,
    tax_id TEXT,
    registration_number TEXT,
    address_line1 TEXT,
    address_line2 TEXT,
    city TEXT,
    state TEXT,
    postal_code TEXT,
    country TEXT DEFAULT 'Zambia',
    settings TEXT DEFAULT '{}',
    features TEXT DEFAULT '{}',
    branding TEXT DEFAULT '{}',
    status TEXT DEFAULT 'active',
    subscription_plan TEXT DEFAULT 'free',
    subscription_status TEXT DEFAULT 'trial',
    subscription_type TEXT DEFAULT 'trial',
    subscription_start TEXT,
    subscription_end TEXT,
    last_payment_date TEXT,
    last_payment_amount REAL,
    grace_period_ends TEXT,
    is_locked TEXT DEFAULT 'false',
    lock_reason TEXT,
    trial_ends_at TEXT,
    unique_key TEXT UNIQUE,
    admin_email TEXT,
    airtel_active INTEGER DEFAULT 1,
    mtn_active INTEGER DEFAULT 1,
    zamtel_active INTEGER DEFAULT 1,
    flutterwave_active INTEGER DEFAULT 1,
    paystack_active INTEGER DEFAULT 0,
    bank_name TEXT,
    bank_account_name TEXT,
    bank_account_number TEXT,
    bank_branch TEXT,
    bank_swift_code TEXT,
    bank_currency TEXT DEFAULT 'ZMW',
    default_gateway TEXT DEFAULT 'flutterwave',
    default_currency TEXT DEFAULT 'ZMW',
    payment_redirect_url TEXT,
    payment_instructions TEXT,
    paybill_number TEXT,
    till_number TEXT,
    images TEXT DEFAULT '[]',
    primary_image_url TEXT,
    created_at TEXT DEFAULT (datetime('now','localtime')),
    updated_at TEXT DEFAULT (datetime('now','localtime')),
    deleted_at TEXT,
    created_by TEXT,
    metadata TEXT DEFAULT '{}'
);

CREATE TABLE IF NOT EXISTS user_sessions (
    id TEXT PRIMARY KEY,
    user_id TEXT NOT NULL,
    token TEXT UNIQUE NOT NULL,
    refresh_token TEXT,
    ip_address TEXT,
    user_agent TEXT,
    device_info TEXT DEFAULT '{}',
    expires_at TEXT NOT NULL,
    last_activity_at TEXT DEFAULT (datetime('now','localtime')),
    created_at TEXT DEFAULT (datetime('now','localtime')),
    revoked_at TEXT
);

CREATE TABLE IF NOT EXISTS api_keys (
    id TEXT PRIMARY KEY,
    user_id TEXT NOT NULL,
    organization_tenant_id TEXT,
    name TEXT NOT NULL,
    key TEXT UNIQUE NOT NULL,
    scopes TEXT DEFAULT '[]',
    is_active INTEGER DEFAULT 1,
    last_used_at TEXT,
    expires_at TEXT,
    created_at TEXT DEFAULT (datetime('now','localtime')),
    revoked_at TEXT
);

CREATE TABLE IF NOT EXISTS audit_logs (
    id TEXT PRIMARY KEY,
    organization_tenant_id TEXT,
    user_id TEXT,
    user_email TEXT,
    user_role TEXT,
    action TEXT NOT NULL,
    method TEXT,
    path TEXT,
    status_code INTEGER,
    ip_address TEXT,
    user_agent TEXT,
    params TEXT,
    query TEXT,
    body TEXT,
    changes TEXT,
    response_status TEXT,
    error_message TEXT,
    metadata TEXT DEFAULT '{}',
    created_at TEXT DEFAULT (datetime('now','localtime'))
);

CREATE TABLE IF NOT EXISTS properties (
    id TEXT PRIMARY KEY,
    organization_tenant_id TEXT NOT NULL,
    name TEXT NOT NULL,
    property_type TEXT DEFAULT 'residential',
    description TEXT,
    address_line1 TEXT NOT NULL,
    address_line2 TEXT,
    city TEXT,
    state TEXT,
    postal_code TEXT,
    country TEXT DEFAULT 'Zambia',
    latitude REAL,
    longitude REAL,
    year_built INTEGER,
    total_units INTEGER DEFAULT 0,
    total_square_footage REAL,
    number_of_floors INTEGER,
    parking_spaces INTEGER DEFAULT 0,
    amenities TEXT DEFAULT '[]',
    manager_id TEXT,
    agent_id TEXT,
    management_company TEXT,
    currency TEXT DEFAULT 'ZMW',
    tax_rate REAL DEFAULT 16.00,
    late_fee_percentage REAL DEFAULT 5.00,
    late_fee_grace_period INTEGER DEFAULT 5,
    status TEXT DEFAULT 'active',
    primary_image_url TEXT,
    images TEXT DEFAULT '[]',
    documents TEXT DEFAULT '[]',
    created_at TEXT DEFAULT (datetime('now','localtime')),
    updated_at TEXT DEFAULT (datetime('now','localtime')),
    deleted_at TEXT,
    metadata TEXT DEFAULT '{}',
    created_by TEXT,
    updated_by TEXT
);

CREATE TABLE IF NOT EXISTS units (
    id TEXT PRIMARY KEY,
    property_id TEXT NOT NULL,
    organization_tenant_id TEXT NOT NULL,
    unit_number TEXT NOT NULL,
    unit_type TEXT DEFAULT 'Standard',
    floor_number INTEGER,
    square_footage REAL,
    bedrooms INTEGER DEFAULT 0,
    bathrooms REAL DEFAULT 0,
    has_balcony INTEGER DEFAULT 0,
    has_parking INTEGER DEFAULT 0,
    features TEXT DEFAULT '{}',
    base_rent REAL DEFAULT 0,
    monthly_rent REAL DEFAULT 0,
    security_deposit REAL,
    includes_utilities INTEGER DEFAULT 0,
    utility_allowance REAL DEFAULT 0,
    status TEXT DEFAULT 'vacant',
    available_date TEXT,
    current_tenant_id TEXT,
    current_lease_id TEXT,
    lease_start_date TEXT,
    lease_end_date TEXT,
    condition_rating INTEGER,
    maintenance_notes TEXT,
    primary_image_url TEXT,
    floor_plan_url TEXT,
    images TEXT DEFAULT '[]',
    last_inspection_date TEXT,
    next_inspection_date TEXT,
    created_at TEXT DEFAULT (datetime('now','localtime')),
    updated_at TEXT DEFAULT (datetime('now','localtime')),
    deleted_at TEXT,
    metadata TEXT DEFAULT '{}',
    created_by TEXT,
    updated_by TEXT,
    UNIQUE(property_id, unit_number)
);

CREATE TABLE IF NOT EXISTS tenants_table (
    id TEXT PRIMARY KEY,
    organization_tenant_id TEXT NOT NULL,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    email TEXT,
    phone TEXT NOT NULL,
    alternative_phone TEXT,
    password_hash TEXT,
    admin_key TEXT,
    id_type TEXT,
    id_number TEXT,
    date_of_birth TEXT,
    nationality TEXT,
    employer TEXT,
    job_title TEXT,
    employment_status TEXT,
    department TEXT,
    assigned_unit_id TEXT,
    property_id TEXT,
    monthly_rent REAL DEFAULT 0,
    move_in_date TEXT,
    monthly_income REAL,
    emergency_contact_name TEXT,
    emergency_contact_phone TEXT,
    emergency_contact_relation TEXT,
    background_check_status TEXT DEFAULT 'pending',
    credit_score INTEGER,
    documents TEXT DEFAULT '[]',
    status TEXT DEFAULT 'active',
    preferred_move_in_date TEXT,
    preferred_lease_term INTEGER,
    pets_allowed INTEGER DEFAULT 0,
    special_requirements TEXT,
    communication_preference TEXT DEFAULT 'email',
    created_at TEXT DEFAULT (datetime('now','localtime')),
    updated_at TEXT DEFAULT (datetime('now','localtime')),
    deleted_at TEXT,
    metadata TEXT DEFAULT '{}',
    created_by TEXT,
    updated_by TEXT,
    current_address_line1 TEXT,
    current_address_line2 TEXT,
    current_city TEXT,
    current_state TEXT,
    current_postal_code TEXT
);
 
CREATE TABLE IF NOT EXISTS leases (
    id TEXT PRIMARY KEY,
    organization_tenant_id TEXT NOT NULL,
    property_id TEXT NOT NULL,
    unit_id TEXT NOT NULL,
    renter_id TEXT NOT NULL,
    lease_number TEXT UNIQUE NOT NULL,
    lease_type TEXT DEFAULT 'fixed_term',
    start_date TEXT NOT NULL,
    end_date TEXT NOT NULL,
    actual_end_date TEXT,
    move_in_date TEXT,
    notice_period_days INTEGER DEFAULT 30,
    monthly_rent REAL NOT NULL,
    security_deposit REAL NOT NULL,
    security_deposit_type TEXT DEFAULT 'fixed',
    security_deposit_months INTEGER DEFAULT 1,
    pet_deposit REAL DEFAULT 0,
    pet_rent REAL DEFAULT 0,
    parking_fee REAL DEFAULT 0,
    utility_fee REAL DEFAULT 0,
    late_fee_percentage REAL DEFAULT 5.00,
    late_fee_grace_period INTEGER DEFAULT 5,
    rent_due_day INTEGER DEFAULT 1,
    rent_frequency TEXT DEFAULT 'monthly',
    discount_percentage REAL DEFAULT 0,
    currency TEXT DEFAULT 'ZMW',
    status TEXT DEFAULT 'draft',
    renewal_number INTEGER DEFAULT 0,
    parent_lease_id TEXT,
    renewed_to_id TEXT,
    termination_reason TEXT,
    termination_fee REAL DEFAULT 0,
    termination_fee_paid INTEGER DEFAULT 0,
    terms TEXT DEFAULT '{}',
    special_conditions TEXT,
    pets_allowed INTEGER DEFAULT 0,
    max_occupants INTEGER DEFAULT 1,
    created_at TEXT DEFAULT (datetime('now','localtime')),
    updated_at TEXT DEFAULT (datetime('now','localtime')),
    deleted_at TEXT
);

CREATE TABLE IF NOT EXISTS payments (
    id TEXT PRIMARY KEY,
    organization_tenant_id TEXT NOT NULL,
    invoice_id TEXT,
    lease_id TEXT,
    property_id TEXT,
    unit_id TEXT,
    tenant_id TEXT,
    payment_reference TEXT UNIQUE NOT NULL,
    gateway_reference TEXT,
    transaction_id TEXT,
    amount REAL NOT NULL,
    currency TEXT DEFAULT 'ZMW',
    payment_method TEXT NOT NULL,
    gateway TEXT,
    provider TEXT,
    phone_number TEXT,
    payment_details TEXT DEFAULT '{}',
    gateway_response TEXT DEFAULT '{}',
    description TEXT,
    status TEXT DEFAULT 'pending',
    retry_count INTEGER DEFAULT 0,
    payment_date TEXT DEFAULT (datetime('now','localtime')),
    completed_at TEXT,
    failed_at TEXT,
    error_message TEXT,
    error_code TEXT,
    verified INTEGER DEFAULT 0,
    verified_by TEXT,
    verified_at TEXT,
    receipt_sent INTEGER DEFAULT 0,
    receipt_sent_at TEXT,
    created_at TEXT DEFAULT (datetime('now','localtime')),
    updated_at TEXT DEFAULT (datetime('now','localtime')),
    deleted_at TEXT,
    metadata TEXT DEFAULT '{}',
    created_by TEXT
);

CREATE TABLE IF NOT EXISTS invoices (
    id TEXT PRIMARY KEY,
    organization_tenant_id TEXT NOT NULL,
    lease_id TEXT NOT NULL,
    property_id TEXT,
    unit_id TEXT,
    tenant_id TEXT NOT NULL,
    invoice_number TEXT UNIQUE NOT NULL,
    period TEXT NOT NULL,
    invoice_date TEXT DEFAULT (date('now')),
    due_date TEXT NOT NULL,
    paid_date TEXT,
    subtotal REAL NOT NULL,
    tax_rate REAL DEFAULT 0,
    tax_amount REAL DEFAULT 0,
    discount_amount REAL DEFAULT 0,
    total_amount REAL NOT NULL,
    paid_amount REAL DEFAULT 0,
    currency TEXT DEFAULT 'ZMW',
    items TEXT DEFAULT '[]',
    status TEXT DEFAULT 'pending',
    notes TEXT,
    created_at TEXT DEFAULT (datetime('now','localtime')),
    updated_at TEXT DEFAULT (datetime('now','localtime')),
    deleted_at TEXT
);

CREATE TABLE IF NOT EXISTS maintenance_requests (
    id TEXT PRIMARY KEY,
    organization_tenant_id TEXT NOT NULL,
    property_id TEXT NOT NULL,
    unit_id TEXT NOT NULL,
    tenant_id TEXT,
    request_number TEXT UNIQUE NOT NULL,
    request_type TEXT NOT NULL,
    priority TEXT DEFAULT 'medium',
    title TEXT NOT NULL,
    description TEXT NOT NULL,
    status TEXT DEFAULT 'open',
    status_history TEXT DEFAULT '[]',
    scheduled_date TEXT,
    completed_date TEXT,
    estimated_duration_hours REAL,
    actual_duration_hours REAL,
    assigned_to TEXT,
    vendor_id TEXT,
    assigned_at TEXT,
    estimated_cost REAL,
    actual_cost REAL,
    is_billable_to_tenant INTEGER DEFAULT 0,
    approval_required INTEGER DEFAULT 0,
    approved_by TEXT,
    approved_at TEXT,
    images TEXT DEFAULT '[]',
    documents TEXT DEFAULT '[]',
    tenant_rating INTEGER,
    tenant_feedback TEXT,
    resolution_notes TEXT,
    created_at TEXT DEFAULT (datetime('now','localtime')),
    updated_at TEXT DEFAULT (datetime('now','localtime')),
    deleted_at TEXT,
    metadata TEXT DEFAULT '{}'
);

CREATE TABLE IF NOT EXISTS receipts (
    id TEXT PRIMARY KEY,
    organization_tenant_id TEXT NOT NULL,
    payment_id TEXT NOT NULL,
    invoice_id TEXT,
    tenant_id TEXT NOT NULL,
    receipt_number TEXT UNIQUE NOT NULL,
    amount REAL NOT NULL,
    currency TEXT DEFAULT 'ZMW',
    payment_method TEXT NOT NULL,
    gateway TEXT,
    provider TEXT,
    details TEXT DEFAULT '{}',
    sent_via TEXT,
    sent_at TEXT,
    issued_date TEXT DEFAULT (datetime('now','localtime')),
    created_at TEXT DEFAULT (datetime('now','localtime')),
    updated_at TEXT DEFAULT (datetime('now','localtime')),
    metadata TEXT DEFAULT '{}',
    created_by TEXT
);


CREATE TABLE IF NOT EXISTS notifications (
    id TEXT PRIMARY KEY,
    organization_tenant_id TEXT,
    recipient_id TEXT NOT NULL,
    recipient_type TEXT DEFAULT 'user',
    type TEXT NOT NULL,
    title TEXT,
    message TEXT,
    data TEXT DEFAULT '{}',
    channels TEXT DEFAULT '["email"]',
    priority TEXT DEFAULT 'normal',
    status TEXT DEFAULT 'pending',
    read_at TEXT,
    delivery_results TEXT DEFAULT '{}',
    error_message TEXT,
    retry_count INTEGER DEFAULT 0,
    scheduled_for TEXT,
    created_at TEXT DEFAULT (datetime('now','localtime')),
    updated_at TEXT DEFAULT (datetime('now','localtime')),
    delivered_at TEXT
);

CREATE TABLE IF NOT EXISTS notification_preferences (
    user_id TEXT PRIMARY KEY,
    organization_tenant_id TEXT NOT NULL,
    email_enabled INTEGER DEFAULT 1,
    sms_enabled INTEGER DEFAULT 1,
    push_enabled INTEGER DEFAULT 1,
    rent_reminders INTEGER DEFAULT 1,
    payment_receipts INTEGER DEFAULT 1,
    maintenance_updates INTEGER DEFAULT 1,
    lease_notices INTEGER DEFAULT 1,
    created_at TEXT DEFAULT (datetime('now','localtime')),
    updated_at TEXT DEFAULT (datetime('now','localtime'))
);

CREATE TABLE IF NOT EXISTS documents (
    id TEXT PRIMARY KEY,
    organization_tenant_id TEXT NOT NULL,
    original_name TEXT NOT NULL,
    storage_key TEXT NOT NULL,
    mime_type TEXT,
    size INTEGER,
    category TEXT,
    url TEXT,
    thumbnails TEXT DEFAULT '[]',
    tenant_id TEXT,
    property_id TEXT,
    unit_id TEXT,
    lease_id TEXT,
    maintenance_id TEXT,
    payment_id TEXT,
    is_verified INTEGER DEFAULT 0,
    is_public INTEGER DEFAULT 0,
    expiry_date TEXT,
    retention_policy TEXT DEFAULT '3_years',
    created_at TEXT DEFAULT (datetime('now','localtime')),
    updated_at TEXT DEFAULT (datetime('now','localtime')),
    deleted_at TEXT,
    metadata TEXT DEFAULT '{}',
    uploaded_by TEXT,
    verified_by TEXT
);

CREATE TABLE IF NOT EXISTS document_downloads (
    id TEXT PRIMARY KEY,
    document_id TEXT NOT NULL,
    user_id TEXT,
    downloaded_at TEXT DEFAULT (datetime('now','localtime')),
    ip_address TEXT,
    user_agent TEXT
);

CREATE TABLE IF NOT EXISTS communications (
    id TEXT PRIMARY KEY,
    organization_tenant_id TEXT NOT NULL,
    sender_id TEXT NOT NULL,
    sender_type TEXT DEFAULT 'user',
    thread_id TEXT,
    parent_message_id TEXT,
    subject TEXT,
    message_type TEXT DEFAULT 'general',
    body TEXT NOT NULL,
    recipients TEXT DEFAULT '[]',
    cc TEXT DEFAULT '[]',
    bcc TEXT DEFAULT '[]',
    attachments TEXT DEFAULT '[]',
    status TEXT DEFAULT 'sent',
    read_by TEXT DEFAULT '[]',
    created_at TEXT DEFAULT (datetime('now','localtime')),
    updated_at TEXT DEFAULT (datetime('now','localtime')),
    deleted_at TEXT,
    metadata TEXT DEFAULT '{}'
);

CREATE TABLE IF NOT EXISTS generated_reports (
    id TEXT PRIMARY KEY,
    organization_tenant_id TEXT NOT NULL,
    report_type TEXT NOT NULL,
    title TEXT,
    parameters TEXT DEFAULT '{}',
    data TEXT,
    file_url TEXT,
    file_format TEXT,
    status TEXT DEFAULT 'completed',
    is_scheduled INTEGER DEFAULT 0,
    schedule_frequency TEXT,
    next_generation_date TEXT,
    created_at TEXT DEFAULT (datetime('now','localtime')),
    updated_at TEXT DEFAULT (datetime('now','localtime')),
    generated_by TEXT,
    metadata TEXT DEFAULT '{}'
);

CREATE TABLE IF NOT EXISTS scheduled_messages (
    id TEXT PRIMARY KEY,
    recipient TEXT NOT NULL,
    message TEXT NOT NULL,
    sender_id TEXT,
    scheduled_at TEXT NOT NULL,
    provider TEXT DEFAULT 'twilio',
    status TEXT DEFAULT 'scheduled',
    sent_at TEXT,
    error_message TEXT,
    created_at TEXT DEFAULT (datetime('now','localtime')),
    updated_at TEXT DEFAULT (datetime('now','localtime')),
    metadata TEXT DEFAULT '{}'
);

CREATE TABLE IF NOT EXISTS message_logs (
    id TEXT PRIMARY KEY,
    organization_tenant_id TEXT,
    message_id TEXT UNIQUE,
    message_type TEXT,
    recipient TEXT,
    sender TEXT,
    subject TEXT,
    content TEXT,
    status TEXT DEFAULT 'sent',
    status_details TEXT DEFAULT '{}',
    provider TEXT,
    cost REAL,
    delivered_at TEXT,
    opened_at TEXT,
    clicked_at TEXT,
    created_at TEXT DEFAULT (datetime('now','localtime')),
    metadata TEXT DEFAULT '{}'
);

CREATE TABLE IF NOT EXISTS user_devices (
    id TEXT PRIMARY KEY,
    user_id TEXT NOT NULL,
    organization_tenant_id TEXT,
    device_token TEXT NOT NULL,
    device_type TEXT,
    device_name TEXT,
    is_active INTEGER DEFAULT 1,
    last_used_at TEXT DEFAULT (datetime('now','localtime')),
    created_at TEXT DEFAULT (datetime('now','localtime')),
    updated_at TEXT DEFAULT (datetime('now','localtime')),
    UNIQUE(user_id, device_token)
);

CREATE TABLE IF NOT EXISTS property_expenses (
    id TEXT PRIMARY KEY,
    property_id TEXT NOT NULL,
    organization_tenant_id TEXT NOT NULL,
    expense_type TEXT,
    description TEXT NOT NULL,
    amount REAL NOT NULL,
    vendor_name TEXT,
    vendor_contact TEXT,
    invoice_number TEXT,
    expense_date TEXT NOT NULL,
    due_date TEXT,
    paid_date TEXT,
    status TEXT DEFAULT 'pending',
    receipt_url TEXT,
    attachments TEXT DEFAULT '[]',
    approved_by TEXT,
    approved_at TEXT,
    created_at TEXT DEFAULT (datetime('now','localtime')),
    updated_at TEXT DEFAULT (datetime('now','localtime')),
    deleted_at TEXT,
    metadata TEXT DEFAULT '{}',
    created_by TEXT
);

CREATE TABLE IF NOT EXISTS developer_settings (
    id TEXT PRIMARY KEY,
    setting_key TEXT UNIQUE,
    setting_value TEXT,
    description TEXT,
    updated_at TEXT DEFAULT (datetime('now','localtime'))
);

CREATE TABLE IF NOT EXISTS developer_payments (
    id TEXT PRIMARY KEY,
    organization_tenant_id TEXT,
    amount REAL,
    payment_method TEXT,
    payment_reference TEXT,
    status TEXT DEFAULT 'pending',
    subscription_type TEXT,
    subscription_months INTEGER,
    payment_date TEXT,
    created_at TEXT DEFAULT (datetime('now','localtime'))
);

CREATE TABLE IF NOT EXISTS security_logs (
    id TEXT PRIMARY KEY,
    event_type TEXT,
    ip_address TEXT,
    user_agent TEXT,
    details TEXT,
    severity TEXT DEFAULT 'low',
    created_at TEXT DEFAULT (datetime('now','localtime'))
);

CREATE TABLE IF NOT EXISTS tenant_notes (
    id TEXT PRIMARY KEY,
    tenant_id TEXT NOT NULL,
    user_id TEXT NOT NULL,
    note_type TEXT,
    note TEXT NOT NULL,
    is_private INTEGER DEFAULT 0,
    created_at TEXT DEFAULT (datetime('now','localtime')),
    updated_at TEXT DEFAULT (datetime('now','localtime')),
    metadata TEXT DEFAULT '{}'
);

INSERT OR IGNORE INTO developer_settings (id, setting_key, setting_value, description) VALUES ('set-1', 'monthly_price', '1000', 'Monthly subscription price in ZMW');
INSERT OR IGNORE INTO developer_settings (id, setting_key, setting_value, description) VALUES ('set-2', 'biannual_price', '5000', '6-month subscription price in ZMW');
INSERT OR IGNORE INTO developer_settings (id, setting_key, setting_value, description) VALUES ('set-3', 'annual_price', '9000', '12-month subscription price in ZMW');
INSERT OR IGNORE INTO developer_settings (id, setting_key, setting_value, description) VALUES ('set-4', 'developer_email', 'developer@blackarc.com', 'Developer contact email');

CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
CREATE INDEX IF NOT EXISTS idx_users_org ON users(organization_tenant_id);
CREATE INDEX IF NOT EXISTS idx_users_role ON users(role);
CREATE INDEX IF NOT EXISTS idx_tenants_slug ON tenants(slug);
CREATE INDEX IF NOT EXISTS idx_tenants_unique_key ON tenants(unique_key);
CREATE INDEX IF NOT EXISTS idx_properties_org ON properties(organization_tenant_id);
CREATE INDEX IF NOT EXISTS idx_units_property ON units(property_id);
CREATE INDEX IF NOT EXISTS idx_units_org ON units(organization_tenant_id);
CREATE INDEX IF NOT EXISTS idx_tenants_table_org ON tenants_table(organization_tenant_id);
CREATE INDEX IF NOT EXISTS idx_leases_org ON leases(organization_tenant_id);
CREATE INDEX IF NOT EXISTS idx_leases_renter ON leases(renter_id);
CREATE INDEX IF NOT EXISTS idx_payments_org ON payments(organization_tenant_id);
CREATE INDEX IF NOT EXISTS idx_payments_tenant ON payments(tenant_id);
CREATE INDEX IF NOT EXISTS idx_payments_status ON payments(status);
CREATE INDEX IF NOT EXISTS idx_payments_reference ON payments(payment_reference);
CREATE INDEX IF NOT EXISTS idx_payments_method ON payments(payment_method);
CREATE INDEX IF NOT EXISTS idx_payments_gateway ON payments(gateway);
CREATE INDEX IF NOT EXISTS idx_payments_provider ON payments(provider);
CREATE INDEX IF NOT EXISTS idx_invoices_org ON invoices(organization_tenant_id);
CREATE INDEX IF NOT EXISTS idx_receipts_org ON receipts(organization_tenant_id);
CREATE INDEX IF NOT EXISTS idx_receipts_payment ON receipts(payment_id);
CREATE INDEX IF NOT EXISTS idx_maintenance_org ON maintenance_requests(organization_tenant_id);
CREATE INDEX IF NOT EXISTS idx_maintenance_status ON maintenance_requests(status);
CREATE INDEX IF NOT EXISTS idx_notifications_recipient ON notifications(recipient_id);
CREATE INDEX IF NOT EXISTS idx_documents_org ON documents(organization_tenant_id);
CREATE INDEX IF NOT EXISTS idx_security_logs_created ON security_logs(created_at);
CREATE INDEX IF NOT EXISTS idx_developer_payments_org ON developer_payments(organization_tenant_id);