-- ============================================================================
-- BLACKSTAR VIRTUAL MINT - STARCOIN GLOBAL CURRENCY SYSTEM
-- ============================================================================
-- Database Schema for World Single Currency System
-- Currency: Starcoin (★100,000 base index)
--
-- Economic Model:
-- - Amalgamates all global currencies against world debt exchange rates
-- - Base currencies: USD, GBP, EUR, CNY
-- - Population growth factor birth expansion rate
-- - Mortality sustainment calculations
-- - Trade of goods and services
-- - GDP salvage operations
--
-- Multiple Dashboard Integration:
-- - Customer Dashboard
-- - Staff Dashboard
-- - Executive Dashboard
-- ============================================================================

-- ============================================================================
-- SECTION 1: CORE CURRENCY SYSTEM
-- ============================================================================

-- Base Global Currencies Table
CREATE TABLE global_base_currencies (
    currency_id VARCHAR(10) PRIMARY KEY,
    currency_code VARCHAR(3) UNIQUE NOT NULL,
    currency_name VARCHAR(100) NOT NULL,
    currency_symbol VARCHAR(5) NOT NULL,
    is_base_currency BOOLEAN DEFAULT FALSE,
    exchange_rate_to_starcoin DECIMAL(20, 10) NOT NULL,
    world_debt_factor DECIMAL(10, 6) NOT NULL,
    current_market_value DECIMAL(30, 10) NOT NULL,
    total_global_supply DECIMAL(30, 2) NOT NULL DEFAULT 0.00,
    status VARCHAR(20) DEFAULT 'ACTIVE',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_by VARCHAR(50)
);

-- Insert Base Currencies (USD, GBP, EUR, CNY)
INSERT INTO global_base_currencies (currency_id, currency_code, currency_name, currency_symbol, is_base_currency, exchange_rate_to_starcoin, world_debt_factor, current_market_value) VALUES
('USD_BASE', 'USD', 'United States Dollar', '$', TRUE, 1.0000000000, 100.000000, 1.0000000000),
('GBP_BASE', 'GBP', 'British Pound Sterling', '£', TRUE, 1.2650000000, 95.500000, 1.2650000000),
('EUR_BASE', 'EUR', 'Euro', '€', TRUE, 1.0850000000, 98.250000, 1.0850000000),
('CNY_BASE', 'CNY', 'Chinese Yuan Renminbi', '¥', TRUE, 0.1390000000, 92.750000, 0.1390000000);

-- Note: MariaDB/MySQL does not support DOMAIN, using DECIMAL directly

-- Starcoin Currency Configurations
CREATE TABLE starcoin_configuration (
    config_id VARCHAR(50) PRIMARY KEY,
    base_index_rate DECIMAL(20, 8) NOT NULL DEFAULT 100000.00000000,
    currency_name VARCHAR(50) NOT NULL DEFAULT 'Starcoin',
    currency_symbol VARCHAR(10) NOT NULL DEFAULT '★',
    currency_code VARCHAR(10) NOT NULL DEFAULT 'STR',
    total_supply_limit DECIMAL(35, 2) NOT NULL,
    current_circulation DECIMAL(35, 2) NOT NULL DEFAULT 0.00,
    minting_rate DECIMAL(10, 6) NOT NULL DEFAULT 0.000025,
    burn_rate DECIMAL(10, 6) NOT NULL DEFAULT 0.000015,
    reserve_ratio DECIMAL(5, 4) NOT NULL DEFAULT 0.1500,
    governance_enabled BOOLEAN DEFAULT TRUE,
    audit_frequency VARCHAR(20) DEFAULT 'DAILY',
    last_audit_at TIMESTAMP,
    next_audit_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert Starcoin Configuration
INSERT INTO starcoin_configuration (config_id, base_index_rate, total_supply_limit, current_circulation, next_audit_at) VALUES
('STARCOIN_MAIN', 100000.00000000, 100000000000000000.00, 0.00, DATE_ADD(CURRENT_TIMESTAMP, INTERVAL 24 HOUR));

-- Global Currency Exchange Rates Table
CREATE TABLE currency_exchange_rates (
    rate_id VARCHAR(50) PRIMARY KEY,
    from_currency VARCHAR(10) NOT NULL,
    to_currency VARCHAR(10) NOT NULL,
    exchange_rate DECIMAL(20, 10) NOT NULL,
    inverse_rate DECIMAL(20, 10) NOT NULL,
    world_debt_adjustment DECIMAL(10, 6) NOT NULL,
    trade_volume_24h DECIMAL(30, 2) DEFAULT 0.00,
    liquidity_pool_size DECIMAL(35, 2) DEFAULT 0.00,
    rate_source VARCHAR(100),
    confidence_score DECIMAL(5, 4),
    valid_from TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    valid_until TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (from_currency, to_currency, valid_from)
);

-- ============================================================================
-- SECTION 2: WORLD POPULATION & DEMOGRAPHICS
-- ============================================================================

-- World Population Statistics
CREATE TABLE world_population_stats (
    stat_id VARCHAR(50) PRIMARY KEY,
    continent VARCHAR(50) NOT NULL,
    country_iso2 CHAR(2),
    country_name VARCHAR(100),
    total_population BIGINT NOT NULL,
    male_population BIGINT NOT NULL,
    female_population BIGINT NOT NULL,
    birth_rate_per_1000 DECIMAL(10, 4) NOT NULL,
    death_rate_per_1000 DECIMAL(10, 4) NOT NULL,
    net_growth_rate DECIMAL(10, 6) NOT NULL,
    population_growth_factor DECIMAL(15, 8) NOT NULL DEFAULT 1.00000000,
    life_expectancy DECIMAL(5, 2),
    median_age DECIMAL(5, 2),
    fertility_rate DECIMAL(5, 3),
    urban_population_percentage DECIMAL(5, 2),
    labor_force_count BIGINT,
    gdp_per_capita DECIMAL(15, 2),
    inflation_rate DECIMAL(10, 4),
    unemployment_rate DECIMAL(5, 2),
    gini_index DECIMAL(5, 2),
    data_year INTEGER NOT NULL,
    data_quarter VARCHAR(2),
    stat_effective_date DATE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Birth Expansion Rate Tracking
CREATE TABLE birth_expansion_tracking (
    tracking_id VARCHAR(50) PRIMARY KEY,
    country_iso2 CHAR(2) NOT NULL,
    birth_period_start DATE NOT NULL,
    birth_period_end DATE NOT NULL,
    total_births BIGINT NOT NULL,
    expected_births BIGINT NOT NULL,
    expansion_rate DECIMAL(10, 6) NOT NULL,
    expansion_factor DECIMAL(15, 8) NOT NULL,
    starcoin_allocation_per_birth DECIMAL(20, 8) NOT NULL,
    total_starcoin_allocated DECIMAL(35, 2) NOT NULL DEFAULT 0.00,
    economic_impact_score DECIMAL(10, 4),
    sustainability_index DECIMAL(10, 4),
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Mortality Sustainment Operations
CREATE TABLE mortality_sustainment (
    sustainment_id VARCHAR(50) PRIMARY KEY,
    country_iso2 CHAR(2) NOT NULL,
    sustainment_period_start DATE NOT NULL,
    sustainment_period_end DATE NOT NULL,
    total_deaths BIGINT NOT NULL,
    natural_deaths BIGINT NOT NULL,
    preventable_deaths BIGINT DEFAULT 0,
    sustainment_rate DECIMAL(10, 6) NOT NULL,
    sustainment_factor DECIMAL(15, 8) NOT NULL DEFAULT 1.00000000,
    starcoin_redistribution_per_death DECIMAL(20, 8) NOT NULL,
    total_starcoin_redistributed DECIMAL(35, 2) NOT NULL DEFAULT 0.00,
    healthcare_access_index DECIMAL(5, 4),
    infrastructure_score DECIMAL(5, 4),
    economic_stabilization_fund DECIMAL(35, 2) DEFAULT 0.00,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ============================================================================
-- SECTION 3: TRADE & ECONOMICS
-- ============================================================================

-- Global Trade of Goods and Services
CREATE TABLE global_trade_operations (
    trade_id VARCHAR(50) PRIMARY KEY,
    trade_reference VARCHAR(100) UNIQUE NOT NULL,
    trade_type VARCHAR(20) NOT NULL CHECK (trade_type IN ('GOODS', 'SERVICES', 'FINANCIAL')),
    sub_type VARCHAR(50),
    exporter_country VARCHAR(50) NOT NULL,
    importer_country VARCHAR(50) NOT NULL,
    product_category VARCHAR(100),
    service_sector VARCHAR(100),
    trade_value_usd DECIMAL(30, 2) NOT NULL,
    trade_value_starcoin DECIMAL(35, 2) NOT NULL,
    exchange_rate_used DECIMAL(20, 10) NOT NULL,
    world_debt_factor_applied DECIMAL(10, 6) NOT NULL,
    quantity DECIMAL(20, 4),
    unit_of_measure VARCHAR(20),
    trade_date DATE NOT NULL,
    settlement_date DATE,
    payment_terms VARCHAR(50),
    trade_status VARCHAR(20) DEFAULT 'PENDING' CHECK (trade_status IN ('PENDING', 'CONFIRMED', 'SETTLED', 'CANCELLED', 'DISPUTED')),
    customs_reference VARCHAR(100),
    logistics_provider VARCHAR(100),
    carbon_footprint DECIMAL(15, 4),
    sustainability_score DECIMAL(5, 4),
    trade_gdp_contribution DECIMAL(10, 4),
    blockchain_tx_hash VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Gross Domestic Product Tracking
CREATE TABLE gdp_tracking (
    gdp_id VARCHAR(50) PRIMARY KEY,
    country_iso2 CHAR(2) NOT NULL,
    country_name VARCHAR(100) NOT NULL,
    reporting_period_start DATE NOT NULL,
    reporting_period_end DATE NOT NULL,
    nominal_gdp_usd DECIMAL(30, 2) NOT NULL,
    real_gdp_usd DECIMAL(30, 2) NOT NULL,
    gdp_starcoin_equivalent DECIMAL(35, 2) NOT NULL,
    gdp_growth_rate DECIMAL(10, 4) NOT NULL,
    gdp_per_capita_usd DECIMAL(15, 2) NOT NULL,
    gdp_per_capita_starcoin DECIMAL(20, 2) NOT NULL,
    sector_agriculture DECIMAL(5, 2),
    sector_industry DECIMAL(5, 2),
    sector_services DECIMAL(5, 2),
    sector_technology DECIMAL(5, 2),
    inflation_rate DECIMAL(10, 4),
    unemployment_rate DECIMAL(5, 2),
    government_debt DECIMAL(30, 2),
    current_account_balance DECIMAL(30, 2),
    trade_balance DECIMAL(30, 2),
    foreign_direct_investment DECIMAL(30, 2),
    starcoin_gdp_share DECIMAL(10, 4),
    salvage_eligible BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- GDP Salvage Operations
CREATE TABLE gdp_salvage_operations (
    salvage_id VARCHAR(50) PRIMARY KEY,
    salvage_reference VARCHAR(100) UNIQUE NOT NULL,
    country_iso2 CHAR(2) NOT NULL,
    source_gdp_id VARCHAR(50) NOT NULL,
    salvage_type VARCHAR(30) NOT NULL CHECK (salvage_type IN ('INFLATION', 'RECESSION', 'CRISIS', 'NATURAL_DISASTER', 'PANDEMIC', 'WAR', 'ECONOMIC_DOWNTURN')),
    salvage_trigger_event VARCHAR(255),
    original_gdp_value DECIMAL(30, 2) NOT NULL,
    salvage_amount DECIMAL(30, 2) NOT NULL,
    salvage_percentage DECIMAL(5, 2) NOT NULL,
    starcoin_equivalent DECIMAL(35, 2) NOT NULL,
    salvage_coefficient DECIMAL(10, 6) NOT NULL,
    economic_stabilization_factor DECIMAL(10, 6) NOT NULL,
    population_impact_score DECIMAL(10, 4),
    trade_restoration_priority INTEGER,
    salvage_date DATE NOT NULL,
    expected_recovery_date DATE,
    actual_recovery_date DATE,
    recovery_percentage DECIMAL(5, 2),
    status VARCHAR(20) DEFAULT 'INITIATED' CHECK (status IN ('INITIATED', 'IN_PROGRESS', 'COMPLETED', 'CANCELLED')),
    justification TEXT,
    approved_by VARCHAR(50),
    blockchain_tx_hash VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (source_gdp_id) REFERENCES gdp_tracking(gdp_id)
);

-- ============================================================================
-- SECTION 4: WORLD DEBT MANAGEMENT
-- ============================================================================

-- World Debt Exchange Rates
CREATE TABLE world_debt_exchange_rates (
    debt_exchange_id VARCHAR(50) PRIMARY KEY,
    currency_code VARCHAR(3) NOT NULL UNIQUE,
    total_national_debt DECIMAL(35, 2) NOT NULL,
    debt_to_gdp_ratio DECIMAL(10, 4),
    debt_per_capita DECIMAL(15, 2),
    starcoin_debt_equivalent DECIMAL(40, 2) NOT NULL,
    debt_sustainability_index DECIMAL(10, 4),
    debt_service_coverage DECIMAL(10, 4),
    external_debt DECIMAL(35, 2),
    interest_payment_obligations DECIMAL(30, 2),
    debt_exchange_rate_to_starcoin DECIMAL(20, 10) NOT NULL,
    world_debt_factor_applied DECIMAL(10, 6) NOT NULL,
    credit_rating VARCHAR(10),
    outlook VARCHAR(20),
    rating_date DATE,
    effective_date DATE NOT NULL,
    next_review_date DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert sample world debt data
INSERT INTO world_debt_exchange_rates (debt_exchange_id, currency_code, total_national_debt, debt_to_gdp_ratio, debt_per_capita, debt_sustainability_index, debt_exchange_rate_to_starcoin, world_debt_factor_applied, effective_date, next_review_date) VALUES
('US_DEBT', 'USD', 31300000000000.00, 121.5, 94000.00, 85.50, 0.0000320000, 100.000000, CURDATE(), DATE_ADD(CURDATE(), INTERVAL 30 DAY)),
('UK_DEBT', 'GBP', 2700000000000.00, 101.2, 40000.00, 78.30, 0.0000405000, 95.500000, CURDATE(), DATE_ADD(CURDATE(), INTERVAL 30 DAY)),
('EU_DEBT', 'EUR', 12500000000000.00, 92.0, 27500.00, 82.10, 0.0000370000, 98.250000, CURDATE(), DATE_ADD(CURDATE(), INTERVAL 30 DAY)),
('CN_DEBT', 'CNY', 85000000000000.00, 77.8, 60000.00, 88.40, 0.0002280000, 92.750000, CURDATE(), DATE_ADD(CURDATE(), INTERVAL 30 DAY));

-- ============================================================================
-- SECTION 5: USER ACCOUNTS & BALANCES
-- ============================================================================

-- User Accounts Table
CREATE TABLE user_accounts (
    user_id VARCHAR(50) PRIMARY KEY,
    account_number VARCHAR(8) UNIQUE NOT NULL,
    username VARCHAR(50) UNIQUE NOT NULL,
    email_address VARCHAR(100) UNIQUE NOT NULL,
    phone_number VARCHAR(20),
    full_name VARCHAR(100) NOT NULL,
    date_of_birth DATE,
    country_of_residence CHAR(2) NOT NULL,
    nationality CHAR(2) NOT NULL,
    account_type VARCHAR(20) NOT NULL CHECK (account_type IN ('CUSTOMER', 'STAFF', 'EXECUTIVE', 'MERCHANT', 'INSTITUTIONAL', 'GOVERNMENT')),
    account_status VARCHAR(20) DEFAULT 'ACTIVE' CHECK (account_status IN ('ACTIVE', 'FROZEN', 'SUSPENDED', 'CLOSED', 'PENDING_VERIFICATION')),
    kyc_level VARCHAR(20) DEFAULT 'BASIC' CHECK (kyc_level IN ('BASIC', 'INTERMEDIATE', 'ADVANCED', 'FULL')),
    kyc_completed_at TIMESTAMP,
    risk_score INTEGER DEFAULT 0 CHECK (risk_score >= 0 AND risk_score <= 100),
    credit_rating DECIMAL(5, 2),
    total_balance_starcoin DECIMAL(35, 2) DEFAULT 0.00,
    available_balance_starcoin DECIMAL(35, 2) DEFAULT 0.00,
    frozen_balance_starcoin DECIMAL(35, 2) DEFAULT 0.00,
    pending_balance_starcoin DECIMAL(35, 2) DEFAULT 0.00,
    registration_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_login_at TIMESTAMP,
    last_transaction_at TIMESTAMP,
    device_count INTEGER DEFAULT 0,
    two_factor_enabled BOOLEAN DEFAULT FALSE,
    biometric_enabled BOOLEAN DEFAULT FALSE,
    referral_code VARCHAR(20),
    referred_by VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Account Balances by Currency
CREATE TABLE account_balances (
    balance_id VARCHAR(50) PRIMARY KEY,
    user_id VARCHAR(50) NOT NULL,
    account_number VARCHAR(8) NOT NULL,
    currency_code VARCHAR(10) NOT NULL,
    balance DECIMAL(35, 2) NOT NULL DEFAULT 0.00,
    available_balance DECIMAL(35, 2) NOT NULL DEFAULT 0.00,
    frozen_balance DECIMAL(35, 2) NOT NULL DEFAULT 0.00,
    pending_balance DECIMAL(35, 2) NOT NULL DEFAULT 0.00,
    starcoin_equivalent DECIMAL(35, 2) NOT NULL DEFAULT 0.00,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES user_accounts(user_id) ON DELETE CASCADE,
    UNIQUE (user_id, currency_code)
);

-- Create indexes for performance
CREATE INDEX idx_user_accounts_account_number ON user_accounts(account_number);
CREATE INDEX idx_user_accounts_email ON user_accounts(email_address);
CREATE INDEX idx_user_accounts_account_type ON user_accounts(account_type);
CREATE INDEX idx_account_balances_user_id ON account_balances(user_id);
CREATE INDEX idx_account_balances_account_number ON account_balances(account_number);

-- ============================================================================
-- SECTION 6: TRANSACTIONS SYSTEM
-- ============================================================================

-- Transaction Records
CREATE TABLE transactions (
    transaction_id VARCHAR(50) PRIMARY KEY,
    transaction_reference VARCHAR(100) UNIQUE NOT NULL,
    transaction_type VARCHAR(30) NOT NULL CHECK (transaction_type IN ('DEPOSIT', 'WITHDRAWAL', 'TRANSFER', 'PAYMENT', 'EXCHANGE', 'MINT', 'BURN', 'SALVAGE', 'TRADE', 'INVESTMENT', 'LOAN', 'REPAYMENT')),
    from_account VARCHAR(8) NOT NULL,
    from_user_id VARCHAR(50),
    to_account VARCHAR(8),
    to_user_id VARCHAR(50),
    amount DECIMAL(35, 2) NOT NULL,
    amount_starcoin DECIMAL(35, 2),
    currency_code VARCHAR(10) DEFAULT 'STR',
    exchange_rate DECIMAL(20, 10),
    world_debt_factor DECIMAL(10, 6),
    fee_amount DECIMAL(20, 2) DEFAULT 0.00,
    fee_starcoin DECIMAL(20, 2) DEFAULT 0.00,
    status VARCHAR(20) DEFAULT 'PENDING' CHECK (status IN ('PENDING', 'CONFIRMED', 'COMPLETED', 'FAILED', 'CANCELLED', 'DISPUTED')),
    merchant_name VARCHAR(100),
    merchant_category VARCHAR(50),
    description TEXT,
    reference_notes TEXT,
    ip_address VARCHAR(45),
    device_id VARCHAR(100),
    location_country VARCHAR(50),
    fraud_score INTEGER DEFAULT 0,
    requires_approval BOOLEAN DEFAULT FALSE,
    approved_by VARCHAR(50),
    approved_at TIMESTAMP,
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (from_user_id) REFERENCES user_accounts(user_id),
    FOREIGN KEY (to_user_id) REFERENCES user_accounts(user_id)
);

-- Transaction History View
CREATE VIEW transaction_history_view AS
SELECT 
    t.transaction_id,
    t.transaction_reference,
    t.transaction_type,
    t.from_account,
    ua_from.full_name AS from_account_name,
    t.to_account,
    ua_to.full_name AS to_account_name,
    t.amount,
    t.amount_starcoin,
    t.currency_code,
    t.fee_amount,
    t.fee_starcoin,
    t.status,
    t.merchant_name,
    t.merchant_category,
    t.description,
    t.timestamp
FROM transactions t
LEFT JOIN user_accounts ua_from ON t.from_user_id = ua_from.user_id
LEFT JOIN user_accounts ua_to ON t.to_user_id = ua_to.user_id;

-- ============================================================================
-- SECTION 7: MINTING & BURNING OPERATIONS
-- ============================================================================

-- Minting Operations
CREATE TABLE minting_operations (
    minting_id VARCHAR(50) PRIMARY KEY,
    minting_reference VARCHAR(100) UNIQUE NOT NULL,
    minting_type VARCHAR(30) NOT NULL CHECK (minting_type IN ('BIRTH_EXPANSION', 'POPULATION_GROWTH', 'GDP_SALVAGE', 'ECONOMIC_STIMULUS', 'MARKET_DEMAND', 'LIQUIDITY', 'GOVERNANCE_APPROVED')),
    minting_amount DECIMAL(35, 2) NOT NULL,
    minting_rate DECIMAL(15, 8) NOT NULL,
    base_index_rate DECIMAL(20, 8) NOT NULL,
    population_factor DECIMAL(15, 8) NOT NULL,
    economic_factor DECIMAL(15, 8) NOT NULL,
    country_impact VARCHAR(50) DEFAULT 'GLOBAL',
    target_recipient VARCHAR(50),
    approved_by VARCHAR(50),
    governance_vote_id VARCHAR(50),
    blockchain_tx_hash VARCHAR(100),
    minting_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Burning Operations
CREATE TABLE burning_operations (
    burning_id VARCHAR(50) PRIMARY KEY,
    burning_reference VARCHAR(100) UNIQUE NOT NULL,
    burning_type VARCHAR(30) NOT NULL CHECK (burning_type IN ('MORTALITY_SUSTAINMENT', 'EXCESS_SUPPLY', 'INFLATION_CONTROL', 'TRANSACTION_FEES', 'GOVERNANCE_APPROVED', 'MARKET_CONTRACTION')),
    burning_amount DECIMAL(35, 2) NOT NULL,
    burning_rate DECIMAL(15, 8) NOT NULL,
    economic_impact DECIMAL(10, 4),
    from_source VARCHAR(50) NOT NULL,
    source_account VARCHAR(8),
    approved_by VARCHAR(50),
    governance_vote_id VARCHAR(50),
    blockchain_tx_hash VARCHAR(100),
    burning_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ============================================================================
-- SECTION 8: ECONOMIC INDICES & ANALYTICS
-- ============================================================================

-- World Economic Index
CREATE TABLE world_economic_index (
    index_id VARCHAR(50) PRIMARY KEY,
    index_name VARCHAR(100) NOT NULL,
    index_type VARCHAR(50) NOT NULL,
    base_value DECIMAL(20, 8) NOT NULL,
    current_value DECIMAL(20, 8) NOT NULL,
    change_percentage DECIMAL(10, 4),
    calculation_method TEXT,
    component_weights JSON,
    effective_date DATE NOT NULL,
    period VARCHAR(20) NOT NULL,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Starcoin Liquidity Pool
CREATE TABLE starcoin_liquidity_pool (
    pool_id VARCHAR(50) PRIMARY KEY,
    pool_name VARCHAR(100) NOT NULL,
    pool_type VARCHAR(20) NOT NULL CHECK (pool_type IN ('PRIMARY', 'SECONDARY', 'STABILIZATION', 'RESERVE')),
    total_starcoin DECIMAL(35, 2) NOT NULL DEFAULT 0.00,
    total_base_currencies DECIMAL(35, 2) NOT NULL DEFAULT 0.00,
    liquidity_ratio DECIMAL(10, 6) NOT NULL DEFAULT 1.000000,
    min_liquidity_threshold DECIMAL(35, 2) NOT NULL,
    stabilization_fund DECIMAL(35, 2) DEFAULT 0.00,
    utilization_rate DECIMAL(10, 4),
    last_rebalanced TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Analytics Events
CREATE TABLE analytics_events (
    event_id VARCHAR(50) PRIMARY KEY,
    event_type VARCHAR(50) NOT NULL,
    event_category VARCHAR(50),
    event_data JSON NOT NULL,
    user_id VARCHAR(50),
    account_number VARCHAR(8),
    session_id VARCHAR(100),
    device_type VARCHAR(20),
    ip_address VARCHAR(45),
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ============================================================================
-- SECTION 9: CONFIGURATION & SETTINGS
-- ============================================================================

-- System Configuration
CREATE TABLE system_configuration (
    config_key VARCHAR(100) PRIMARY KEY,
    config_value TEXT NOT NULL,
    config_type VARCHAR(20) NOT NULL CHECK (config_type IN ('STRING', 'INTEGER', 'DECIMAL', 'JSON', 'BOOLEAN')),
    description TEXT,
    is_sensitive BOOLEAN DEFAULT FALSE,
    is_editable BOOLEAN DEFAULT TRUE,
    validation_regex VARCHAR(255),
    default_value TEXT,
    last_modified_by VARCHAR(50),
    last_modified_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert system configurations
INSERT INTO system_configuration (config_key, config_value, config_type, description, default_value) VALUES
('system_name', 'Blackstar Virtual Mint', 'STRING', 'System name', 'Blackstar Virtual Mint'),
('starcoin_symbol', '★', 'STRING', 'Starcoin currency symbol', '★'),
('base_index_rate', '100000', 'DECIMAL', 'Base index rate for Starcoin', '100000'),
('minting_enabled', 'true', 'BOOLEAN', 'Enable minting operations', 'true'),
('burning_enabled', 'true', 'BOOLEAN', 'Enable burning operations', 'true'),
('salvage_enabled', 'true', 'BOOLEAN', 'Enable GDP salvage operations', 'true'),
('max_daily_mint', '1000000000.00', 'DECIMAL', 'Maximum daily minting limit', '1000000000.00'),
('max_daily_burn', '500000000.00', 'DECIMAL', 'Maximum daily burning limit', '500000000.00'),
('transaction_fee_rate', '0.001', 'DECIMAL', 'Transaction fee rate (0.1%)', '0.001'),
('min_transaction_amount', '0.01', 'DECIMAL', 'Minimum transaction amount', '0.01'),
('max_transaction_amount', '1000000000.00', 'DECIMAL', 'Maximum transaction amount', '1000000000.00'),
('kyc_required_for_withdrawal', 'true', 'BOOLEAN', 'KYC required for withdrawals', 'true'),
('two_factor_required_for_large_transactions', 'true', 'BOOLEAN', '2FA required for large transactions', 'true'),
('large_transaction_threshold', '10000.00', 'DECIMAL', 'Threshold for large transactions', '10000.00'),
('audit_logging_enabled', 'true', 'BOOLEAN', 'Enable audit logging', 'true'),
('blockchain_integration_enabled', 'true', 'BOOLEAN', 'Enable blockchain integration', 'true');

-- ============================================================================
-- SECTION 10: AUDIT & GOVERNANCE
-- ============================================================================

-- Audit Logs
CREATE TABLE audit_logs (
    audit_id VARCHAR(50) PRIMARY KEY,
    user_id VARCHAR(50),
    account_number VARCHAR(8),
    action_type VARCHAR(50) NOT NULL,
    action_category VARCHAR(50),
    resource_type VARCHAR(50),
    resource_id VARCHAR(50),
    old_value JSON,
    new_value JSON,
    ip_address VARCHAR(45),
    user_agent VARCHAR(500),
    action_result VARCHAR(20) DEFAULT 'SUCCESS' CHECK (action_result IN ('SUCCESS', 'FAILURE', 'WARNING')),
    error_message TEXT,
    session_id VARCHAR(100),
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Governance Proposals
CREATE TABLE governance_proposals (
    proposal_id VARCHAR(50) PRIMARY KEY,
    proposal_reference VARCHAR(100) UNIQUE NOT NULL,
    proposal_type VARCHAR(30) NOT NULL CHECK (proposal_type IN ('MINT', 'BURN', 'CONFIG_CHANGE', 'POLICY_UPDATE', 'EMERGENCY')),
    proposal_title VARCHAR(255) NOT NULL,
    proposal_description TEXT NOT NULL,
    proposed_by VARCHAR(50) NOT NULL,
    proposal_data JSON NOT NULL,
    voting_start_at TIMESTAMP NOT NULL,
    voting_end_at TIMESTAMP NOT NULL,
    total_votes INTEGER DEFAULT 0,
    votes_for INTEGER DEFAULT 0,
    votes_against INTEGER DEFAULT 0,
    votes_abstain INTEGER DEFAULT 0,
    required_quorum DECIMAL(5, 2) NOT NULL,
    approval_threshold DECIMAL(5, 2) NOT NULL,
    status VARCHAR(20) DEFAULT 'PROPOSED' CHECK (status IN ('PROPOSED', 'VOTING', 'APPROVED', 'REJECTED', 'EXECUTED', 'CANCELLED')),
    executed_at TIMESTAMP,
    result_notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ============================================================================
-- SECTION 11: FUNCTIONS & TRIGGERS
-- ============================================================================

-- Function: Calculate Starcoin equivalent from USD
CREATE FUNCTION calculate_starcoin_from_usd(usd_amount DECIMAL(30, 2), world_debt_factor DECIMAL(10, 6))
RETURNS DECIMAL(35, 2)
DETERMINISTIC
READS SQL DATA
BEGIN
    DECLARE base_index_rate DECIMAL(20, 8);
    DECLARE starcoin_amount DECIMAL(35, 2);
    
    SELECT base_index_rate INTO base_index_rate FROM starcoin_configuration WHERE config_id = 'STARCOIN_MAIN';
    
    SET starcoin_amount = (usd_amount * base_index_rate) * (world_debt_factor / 100.0);
    
    RETURN ROUND(starcoin_amount, 2);
END;

-- Function: Calculate population growth factor
CREATE FUNCTION calculate_population_growth_factor(birth_rate DECIMAL(10, 4), death_rate DECIMAL(10, 4))
RETURNS DECIMAL(15, 8)
DETERMINISTIC
NO SQL
BEGIN
    RETURN ROUND((1.0 + (birth_rate - death_rate) / 1000.0), 8);
END;

-- Function: Calculate GDP salvage coefficient
CREATE FUNCTION calculate_gdp_salvage_coefficient(gdp_value DECIMAL(30, 2), economic_impact DECIMAL(10, 4))
RETURNS DECIMAL(10, 6)
DETERMINISTIC
NO SQL
BEGIN
    DECLARE base_coefficient DECIMAL(10, 6) DEFAULT 0.05;
    DECLARE adjusted_coefficient DECIMAL(10, 6);
    
    SET adjusted_coefficient = base_coefficient * (1.0 + economic_impact);
    RETURN ROUND(adjusted_coefficient, 6);
END;

-- Trigger: Update user account timestamp on update
CREATE FUNCTION update_user_account_timestamp()
RETURNS TRIGGER
DETERMINISTIC
BEGIN
    SET NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;

CREATE TRIGGER trigger_update_user_account_timestamp
    BEFORE UPDATE ON user_accounts
    FOR EACH ROW
    CALL update_user_account_timestamp();

-- Trigger: Update account balance timestamp on update
CREATE FUNCTION update_account_balance_timestamp()
RETURNS TRIGGER
DETERMINISTIC
BEGIN
    SET NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;

CREATE TRIGGER trigger_update_account_balance_timestamp
    BEFORE UPDATE ON account_balances
    FOR EACH ROW
    CALL update_account_balance_timestamp();

-- ============================================================================
-- SECTION 12: STORED PROCEDURES
-- ============================================================================

-- Procedure: Mint Starcoins for Birth Expansion
CREATE PROCEDURE mint_starcoins_birth_expansion(
    IN p_country_iso2 CHAR(2),
    IN p_birth_count BIGINT,
    IN p_per_birth_amount DECIMAL(20, 8),
    IN p_minting_reference VARCHAR(100)
)
BEGIN
    DECLARE v_total_amount DECIMAL(35, 2);
    DECLARE v_minting_id VARCHAR(50);
    DECLARE v_population_factor DECIMAL(15, 8);
    DECLARE v_economic_factor DECIMAL(15, 8);
    DECLARE v_base_index_rate DECIMAL(20, 8);
    
    -- Get base index rate
    SELECT base_index_rate INTO v_base_index_rate 
    FROM starcoin_configuration WHERE config_id = 'STARCOIN_MAIN';
    
    -- Calculate population factor
    SELECT population_growth_factor INTO v_population_factor
    FROM world_population_stats 
    WHERE country_iso2 = p_country_iso2 
    ORDER BY stat_effective_date DESC LIMIT 1;
    
    -- Calculate economic factor
    SET v_economic_factor = 1.0 + (p_birth_count / 1000000.0) * 0.1;
    
    -- Calculate total minting amount
    SET v_total_amount = p_birth_count * p_per_birth_amount * v_base_index_rate;
    
    -- Generate minting ID
    SET v_minting_id = CONCAT('MINT-', DATE_FORMAT(NOW(), '%Y%m%d%H%i%s'), '-', UPPER(SUBSTRING(MD5(RAND()), 1, 8)));
    
    -- Insert minting record
    INSERT INTO minting_operations (
        minting_id, minting_reference, minting_type, minting_amount,
        minting_rate, base_index_rate, population_factor, economic_factor,
        country_impact, minting_date
    ) VALUES (
        v_minting_id, p_minting_reference, 'BIRTH_EXPANSION', v_total_amount,
        v_population_factor, v_base_index_rate, v_population_factor, v_economic_factor,
        p_country_iso2, NOW()
    );
    
    -- Update Starcoin configuration
    UPDATE starcoin_configuration
    SET current_circulation = current_circulation + v_total_amount,
        updated_at = NOW()
    WHERE config_id = 'STARCOIN_MAIN';
    
    -- Log to audit
    INSERT INTO audit_logs (
        audit_id, action_type, action_category, resource_type, resource_id,
        old_value, new_value, action_result
    ) VALUES (
        CONCAT('AUDIT-', DATE_FORMAT(NOW(), '%Y%m%d%H%i%s'), '-', UPPER(SUBSTRING(MD5(RAND()), 1, 8))),
        'MINT_COINS', 'BIRTH_EXPANSION', 'minting_operations', v_minting_id,
        JSON_OBJECT('amount', 0),
        JSON_OBJECT('amount', v_total_amount, 'country', p_country_iso2, 'birth_count', p_birth_count),
        'SUCCESS'
    );
    
END;

-- Procedure: Mint Starcoins for GDP Salvage
CREATE PROCEDURE mint_starcoins_gdp_salvage(
    IN p_gdp_id VARCHAR(50),
    IN p_salvage_type VARCHAR(30),
    IN p_justification TEXT,
    IN p_approved_by VARCHAR(50)
)
BEGIN
    DECLARE v_gdp_nominal_gdp DECIMAL(35, 2);
    DECLARE v_gdp_growth_rate DECIMAL(10, 4);
    DECLARE v_gdp_country_iso2 CHAR(2);
    DECLARE v_salvage_amount DECIMAL(35, 2);
    DECLARE v_starcoin_equivalent DECIMAL(35, 2);
    DECLARE v_salvage_coefficient DECIMAL(10, 6);
    DECLARE v_stabilization_factor DECIMAL(10, 6);
    DECLARE v_salvage_id VARCHAR(50);
    DECLARE v_minting_id VARCHAR(50);
    DECLARE v_base_index_rate DECIMAL(20, 8);
    
    -- Get GDP data
    SELECT nominal_gdp_usd, gdp_growth_rate, country_iso2 
    INTO v_gdp_nominal_gdp, v_gdp_growth_rate, v_gdp_country_iso2
    FROM gdp_tracking
    WHERE gdp_id = p_gdp_id
    LIMIT 1;
    
    -- Check if GDP record exists
    IF v_gdp_nominal_gdp IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'GDP record not found';
    END IF;
    
    -- Get base index rate
    SELECT base_index_rate INTO v_base_index_rate
    FROM starcoin_configuration WHERE config_id = 'STARCOIN_MAIN';
    
    -- Calculate salvage coefficient
    SET v_salvage_coefficient = calculate_gdp_salvage_coefficient(v_gdp_nominal_gdp, 0.15);
    
    -- Calculate economic stabilization factor
    SET v_stabilization_factor = 1.0 - (v_gdp_growth_rate / 100.0);
    
    -- Calculate salvage amount
    SET v_salvage_amount = v_gdp_nominal_gdp * v_salvage_coefficient;
    
    -- Calculate Starcoin equivalent
    SET v_starcoin_equivalent = calculate_starcoin_from_usd(v_salvage_amount, 100.0);
    
    -- Generate IDs
    SET v_salvage_id = CONCAT('SALVAGE-', DATE_FORMAT(NOW(), '%Y%m%d%H%i%s'), '-', p_salvage_type);
    SET v_minting_id = CONCAT('MINT-', DATE_FORMAT(NOW(), '%Y%m%d%H%i%s'), '-SALVAGE');
    
    -- Insert salvage operation
    INSERT INTO gdp_salvage_operations (
        salvage_id, salvage_reference, country_iso2, source_gdp_id,
        salvage_type, salvage_trigger_event, original_gdp_value,
        salvage_amount, salvage_percentage, starcoin_equivalent,
        salvage_coefficient, economic_stabilization_factor, salvage_date,
        justification, approved_by, status
    ) VALUES (
        v_salvage_id, v_salvage_id, v_gdp_country_iso2, p_gdp_id,
        p_salvage_type, p_justification, v_gdp_nominal_gdp,
        v_salvage_amount, (v_salvage_coefficient * 100.0), v_starcoin_equivalent,
        v_salvage_coefficient, v_stabilization_factor, CURDATE(),
        p_justification, p_approved_by, 'INITIATED'
    );
    
    -- Insert minting operation
    INSERT INTO minting_operations (
        minting_id, minting_reference, minting_type, minting_amount,
        minting_rate, base_index_rate, population_factor, economic_factor,
        country_impact, governance_vote_id, approved_by, minting_date
    ) VALUES (
        v_minting_id, v_salvage_id, 'GDP_SALVAGE', v_starcoin_equivalent,
        v_stabilization_factor, v_base_index_rate, 1.0, v_stabilization_factor,
        v_gdp_country_iso2, v_salvage_id, p_approved_by, NOW()
    );
    
    -- Update Starcoin configuration
    UPDATE starcoin_configuration
    SET current_circulation = current_circulation + v_starcoin_equivalent,
        updated_at = NOW()
    WHERE config_id = 'STARCOIN_MAIN';
    
    -- Update GDP tracking
    UPDATE gdp_tracking
    SET salvage_eligible = FALSE,
        updated_at = NOW()
    WHERE gdp_id = p_gdp_id;
    
    -- Log to audit
    INSERT INTO audit_logs (
        audit_id, action_type, action_category, resource_type, resource_id,
        old_value, new_value, action_result
    ) VALUES (
        CONCAT('AUDIT-', DATE_FORMAT(NOW(), '%Y%m%d%H%i%s'), '-', UPPER(SUBSTRING(MD5(RAND()), 1, 8))),
        'MINT_COINS', 'GDP_SALVAGE', 'gdp_salvage_operations', v_salvage_id,
        JSON_OBJECT('amount', 0),
        JSON_OBJECT('salvage_amount', v_salvage_amount, 'starcoin', v_starcoin_equivalent, 'country', v_gdp_country_iso2),
        'SUCCESS'
    );
    
END;

-- Procedure: Burn Starcoins for Mortality Sustainment
CREATE PROCEDURE burn_starcoins_mortality_sustainment(
    IN p_user_id VARCHAR(50),
    IN p_account_number VARCHAR(8),
    IN p_burn_amount DECIMAL(35, 2),
    IN p_burning_reference VARCHAR(100)
)
BEGIN
    DECLARE v_burning_id VARCHAR(50);
    DECLARE v_economic_impact DECIMAL(10, 4);
    DECLARE v_sustainment_rate DECIMAL(10, 6);
    DECLARE v_base_index_rate DECIMAL(20, 8);
    DECLARE v_user_country CHAR(2);
    DECLARE v_current_balance DECIMAL(35, 2);
    
    -- Verify account exists
    SELECT country_of_residence INTO v_user_country
    FROM user_accounts
    WHERE user_id = p_user_id AND account_number = p_account_number
    LIMIT 1;
    
    IF v_user_country IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = CONCAT('User account not found: ', p_user_id, '/', p_account_number);
    END IF;
    
    -- Check sufficient balance
    SELECT balance INTO v_current_balance
    FROM account_balances
    WHERE user_id = p_user_id AND currency_code = 'STR'
    LIMIT 1;
    
    IF v_current_balance < p_burn_amount THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = CONCAT('Insufficient balance to burn ', p_burn_amount);
    END IF;
    
    -- Get base index rate
    SELECT base_index_rate INTO v_base_index_rate
    FROM starcoin_configuration WHERE config_id = 'STARCOIN_MAIN';
    
    -- Calculate sustainment rate
    SET v_sustainment_rate = 0.000008; -- Base sustainment rate
    SET v_economic_impact = -0.05; -- Negative impact for burning operation
    
    -- Generate burning ID
    SET v_burning_id = CONCAT('BURN-', DATE_FORMAT(NOW(), '%Y%m%d%H%i%s'), '-', UPPER(SUBSTRING(MD5(RAND()), 1, 8)));
    
    -- Insert burning record
    INSERT INTO burning_operations (
        burning_id, burning_reference, burning_type, burning_amount,
        burning_rate, economic_impact, from_source, source_account, burning_date
    ) VALUES (
        v_burning_id, p_burning_reference, 'MORTALITY_SUSTAINMENT', p_burn_amount,
        v_sustainment_rate, v_economic_impact, 'ACCOUNT', p_account_number, NOW()
    );
    
    -- Deduct from account balance
    UPDATE account_balances
    SET balance = balance - p_burn_amount,
        updated_at = NOW()
    WHERE user_id = p_user_id AND currency_code = 'STR';
    
    -- Update user account
    UPDATE user_accounts
    SET total_balance_starcoin = total_balance_starcoin - p_burn_amount,
        available_balance_starcoin = available_balance_starcoin - p_burn_amount,
        updated_at = NOW()
    WHERE user_id = p_user_id;
    
    -- Update Starcoin configuration
    UPDATE starcoin_configuration
    SET current_circulation = current_circulation - p_burn_amount,
        updated_at = NOW()
    WHERE config_id = 'STARCOIN_MAIN';
    
    -- Log to audit
    INSERT INTO audit_logs (
        audit_id, action_type, action_category, resource_type, resource_id,
        old_value, new_value, action_result
    ) VALUES (
        CONCAT('AUDIT-', DATE_FORMAT(NOW(), '%Y%m%d%H%i%s'), '-', UPPER(SUBSTRING(MD5(RAND()), 1, 8))),
        'BURN_COINS', 'MORTALITY_SUSTAINMENT', 'burning_operations', v_burning_id,
        JSON_OBJECT('user_id', p_user_id),
        JSON_OBJECT('burned_amount', p_burn_amount, 'account', p_account_number),
        'SUCCESS'
    );
    
END;

-- ============================================================================
-- SECTION 13: VIEWS FOR ANALYTICS & REPORTING
-- ============================================================================

-- View: World Economic Summary
CREATE VIEW world_economic_summary AS
SELECT 
    DATE(CURRENT_TIMESTAMP) AS report_date,
    (SELECT base_index_rate FROM starcoin_configuration) AS starcoin_base_index,
    (SELECT current_circulation FROM starcoin_configuration) AS starcoin_in_circulation,
    (SELECT COUNT(*) FROM global_base_currencies WHERE status = 'ACTIVE') AS active_currencies,
    (SELECT SUM(total_population) FROM world_population_stats LIMIT 1) AS world_population,
    (SELECT SUM(nominal_gdp_usd) FROM gdp_tracking) AS global_gdp_usd,
    (SELECT SUM(total_national_debt) FROM world_debt_exchange_rates) AS world_debt_total,
    (SELECT SUM(trade_value_starcoin) FROM global_trade_operations WHERE trade_date >= CURDATE()) AS today_trade_volume_starcoin,
    (SELECT COUNT(*) FROM user_accounts WHERE account_status = 'ACTIVE') AS active_users,
    (SELECT COUNT(*) FROM transactions WHERE status = 'COMPLETED' AND DATE(timestamp) = CURDATE()) AS today_transactions
UNION ALL
SELECT 
    NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL;

-- View: Account Summary by Type
CREATE VIEW account_summary_by_type AS
SELECT 
    account_type,
    COUNT(*) AS total_accounts,
    SUM(total_balance_starcoin) AS total_balance_starcoin,
    SUM(available_balance_starcoin) AS available_balance_starcoin,
    AVG(total_balance_starcoin) AS avg_balance_starcoin,
    MAX(total_balance_starcoin) AS max_balance_starcoin,
    MIN(total_balance_starcoin) AS min_balance_starcoin
FROM user_accounts
WHERE account_status = 'ACTIVE'
GROUP BY account_type;

-- View: Currency Exchange Summary
CREATE VIEW currency_exchange_summary AS
SELECT 
    from_currency,
    to_currency,
    AVG(exchange_rate) AS avg_exchange_rate,
    AVG(world_debt_adjustment) AS avg_debt_factor,
    SUM(trade_volume_24h) AS total_trade_volume,
    COUNT(*) AS rate_updates
FROM currency_exchange_rates
WHERE valid_from >= DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 7 DAY)
GROUP BY from_currency, to_currency;

-- View: Minting/Burning Analytics
CREATE VIEW minting_burning_analytics AS
SELECT 
    'MINTING' AS operation_type,
    minting_type AS operation_subtype,
    COUNT(*) AS total_operations,
    SUM(minting_amount) AS total_amount,
    AVG(minting_amount) AS avg_amount,
    MAX(minting_amount) AS max_amount,
    MIN(minting_amount) AS min_amount,
    country_impact AS country,
    minting_date AS operation_date
FROM minting_operations
GROUP BY minting_type, country_impact, minting_date
UNION ALL
SELECT 
    'BURNING' AS operation_type,
    burning_type AS operation_subtype,
    COUNT(*) AS total_operations,
    SUM(burning_amount) AS total_amount,
    AVG(burning_amount) AS avg_amount,
    MAX(burning_amount) AS max_amount,
    MIN(burning_amount) AS min_amount,
    from_source AS country,
    burning_date AS operation_date
FROM burning_operations
GROUP BY burning_type, from_source, burning_date;

-- ============================================================================
-- SECTION 14: SAMPLE DATA INSERTIONS
-- ============================================================================

-- Insert sample world population data
INSERT INTO world_population_stats (stat_id, continent, country_iso2, country_name, total_population, male_population, female_population, birth_rate_per_1000, death_rate_per_1000, net_growth_rate, population_growth_factor, life_expectancy, median_age, gdp_per_capita, data_year, stat_effective_date) VALUES
('WORLD_2024', 'WORLD', NULL, 'WORLD', 8100000000, 4080000000, 4020000000, 18.5, 7.8, 0.0107, 1.01070000, 72.8, 30.5, 12500.00, 2024, '2024-01-01'),
('US_2024', 'NORTH_AMERICA', 'US', 'United States', 335000000, 165000000, 170000000, 12.4, 8.8, 0.0036, 1.00360000, 78.9, 38.5, 76000.00, 2024, '2024-01-01'),
('UK_2024', 'EUROPE', 'GB', 'United Kingdom', 67500000, 33300000, 34200000, 10.2, 9.3, 0.0009, 1.00090000, 81.3, 40.5, 46000.00, 2024, '2024-01-01'),
('CN_2024', 'ASIA', 'CN', 'China', 1425000000, 735000000, 690000000, 7.5, 7.2, 0.0003, 1.00030000, 77.1, 38.4, 12500.00, 2024, '2024-01-01'),
('JP_2024', 'ASIA', 'JP', 'Japan', 125000000, 61000000, 64000000, 6.4, 10.9, -0.0045, 0.99550000, 84.6, 49.1, 34000.00, 2024, '2024-01-01'),
('IN_2024', 'ASIA', 'IN', 'India', 1430000000, 740000000, 690000000, 17.1, 7.3, 0.0098, 1.00980000, 70.1, 28.7, 2600.00, 2024, '2024-01-01');

-- Insert sample GDP data
INSERT INTO gdp_tracking (gdp_id, country_iso2, country_name, reporting_period_start, reporting_period_end, nominal_gdp_usd, real_gdp_usd, gdp_starcoin_equivalent, gdp_growth_rate, gdp_per_capita_usd, gdp_per_capita_starcoin, sector_agriculture, sector_industry, sector_services, sector_technology, inflation_rate, unemployment_rate) VALUES
('GDP_US_2024', 'US', 'United States', '2024-01-01', '2024-12-31', 26950000000000.00, 26950000000000.00, 2695000000000000000.00, 2.1, 76000.00, 7600000000.00, 0.9, 18.7, 80.4, 10.2, 3.4, 3.8),
('GDP_GB_2024', 'GB', 'United Kingdom', '2024-01-01', '2024-12-31', 3130000000000.00, 3130000000000.00, 313000000000000000.00, 0.5, 46000.00, 4600000000.00, 0.7, 19.1, 80.2, 8.6, 4.0, 4.2),
('GDP_CN_2024', 'CN', 'China', '2024-01-01', '2024-12-31', 17700000000000.00, 17700000000000.00, 1770000000000000000.00, 5.2, 12500.00, 1250000000.00, 7.3, 39.5, 53.2, 39.1, 0.3, 5.2),
('GDP_JP_2024', 'JP', 'Japan', '2024-01-01', '2024-12-31', 4230000000000.00, 4230000000000.00, 423000000000000000.00, 1.8, 34000.00, 3400000000.00, 1.2, 29.1, 69.7, 32.5, 3.2, 2.6),
('GDP_IN_2024', 'IN', 'India', '2024-01-01', '2024-12-31', 3730000000000.00, 3730000000000.00, 373000000000000000.00, 6.3, 2600.00, 260000000.00, 16.4, 27.6, 56.0, 46.3, 5.4, 7.0);

-- Insert sample user accounts
INSERT INTO user_accounts (user_id, account_number, username, email_address, full_name, country_of_residence, nationality, account_type, total_balance_starcoin, available_balance_starcoin) VALUES
('USER_001', '87654321', 'john_doe', 'john.doe@example.com', 'John Doe', 'US', 'US', 'CUSTOMER', 50000000.00, 50000000.00),
('USER_002', '87654322', 'jane_smith', 'jane.smith@example.com', 'Jane Smith', 'GB', 'GB', 'CUSTOMER', 25000000.00, 25000000.00),
('STAFF_001', '87654323', 'staff_admin', 'staff@blackstar.com', 'Admin Staff', 'US', 'US', 'STAFF', 100000000.00, 100000000.00),
('EXEC_001', '87654324', 'exec_blackstar', 'executive@blackstar.com', 'Blackstar Executive', 'CH', 'CH', 'EXECUTIVE', 1000000000.00, 1000000000.00);

-- Insert sample account balances
INSERT INTO account_balances (balance_id, user_id, account_number, currency_code, balance, available_balance, starcoin_equivalent) VALUES
('BAL_001', 'USER_001', '87654321', 'STR', 50000000.00, 50000000.00, 50000000.00),
('BAL_002', 'USER_002', '87654322', 'STR', 25000000.00, 25000000.00, 25000000.00),
('BAL_003', 'STAFF_001', '87654323', 'STR', 100000000.00, 100000000.00, 100000000.00),
('BAL_004', 'EXEC_001', '87654324', 'STR', 1000000000.00, 1000000000.00, 1000000000.00);

-- ============================================================================
-- SECTION 15: SECURITY & PERMISSIONS
-- ============================================================================

-- MariaDB/MySQL User Roles
-- Note: These users should be created with appropriate passwords in production
-- Create users for different access levels (syntax for creation, not execution)

-- Customer user (read-only access + basic transaction operations)
-- CREATE USER 'blackstar_customer'@'%' IDENTIFIED BY 'customer_password';
-- GRANT SELECT ON blackstar_virtual_mint.* TO 'blackstar_customer'@'%';
-- GRANT INSERT, SELECT ON blackstar_virtual_mint.transactions TO 'blackstar_customer'@'%';

-- Staff user (read access + account management)
-- CREATE USER 'blackstar_staff'@'%' IDENTIFIED BY 'staff_password';
-- GRANT SELECT, INSERT, UPDATE ON blackstar_virtual_mint.* TO 'blackstar_staff'@'%';

-- Executive user (full administrative access)
-- CREATE USER 'blackstar_executive'@'%' IDENTIFIED BY 'executive_password';
-- GRANT ALL PRIVILEGES ON blackstar_virtual_mint.* TO 'blackstar_executive'@'%';
-- GRANT EXECUTE ON PROCEDURE blackstar_virtual_mint.* TO 'blackstar_executive'@'%';

-- Note: Uncomment and modify the above statements as needed for your environment
-- Replace passwords with strong, secure passwords in production

-- ============================================================================
-- END OF SCHEMA
-- ============================================================================

-- MariaDB/MySQL Comments:
-- Database: blackstar_virtual_mint - Global Single Currency System (Starcoin)
-- starcoin_configuration: Core Starcoin configuration with base index rate of 100,000
-- minting_operations: Minting operations for birth expansion, GDP salvage, and economic stimulus
-- burning_operations: Burning operations for mortality sustainment and supply control
-- gdp_salvage_operations: GDP salvage operations for economic stabilization
-- global_trade_operations: Global trade of goods and services in Starcoin
-- world_population_stats: World population statistics with birth and death rates
-- world_debt_exchange_rates: World debt exchange rates integrated with Starcoin

-- Final verification
SELECT COUNT(*) AS total_tables_created 
FROM information_schema.tables 
WHERE table_schema = DATABASE() AND table_type = 'BASE TABLE';

-- Database initialization complete
-- Blackstar Virtual Mint Base Starcoin Index Rate: 100,000
-- System ready for global currency operations