================================================================================
                     CARBONCORE DEVELOPER GUIDE #08
                   REST API & DATABASE ARCHITECTURE
================================================================================

PRINCIPLE: Production-ready serverless backend with JWT authentication

================================================================================
INFRASTRUCTURE OVERVIEW
================================================================================

Database: AWS RDS PostgreSQL
    Host: carboncore-prod-db.c6jeoyea0fof.us-east-1.rds.amazonaws.com
    Port: 5432
    Database: carboncore_db
    User: carboncore_admin
    Password: CarbonCore2025

Lambda Function: carboncore-get-projects
    Runtime: Python 3.9
    Region: US-East-1
    Timeout: 30 seconds
    Memory: 512 MB

API Gateway: 9pat3ov0nk.execute-api.us-east-1.amazonaws.com
    Custom Domain: api.carboncore.earth
    Protocol: HTTPS only
    Stage: Production

Authentication: JWT with 8-hour sessions
    Secret: Production-grade key
    Algorithm: HS256
    Expiration: 28,800 seconds (8 hours)

================================================================================
DATABASE SCHEMA
================================================================================

Table 1: carbon_projects
    id SERIAL PRIMARY KEY
    project_name VARCHAR(255) NOT NULL
    project_initiator VARCHAR(255) NOT NULL
    project_manager_account VARCHAR(255)
    project_type_id INTEGER REFERENCES project_types(id)
    country VARCHAR(100) NOT NULL
    region VARCHAR(100) NOT NULL
    coordinates JSONB                    -- GeoJSON polygons
    vintage_year INTEGER NOT NULL
    land_type VARCHAR(100)
    tree_types JSONB                     -- ["Pinus oocarpa", "oak"]
    total_area NUMERIC(12,2)             -- Hectares
    co2_absorption NUMERIC(15,2)         -- kg/year
    created_date TIMESTAMP DEFAULT NOW()
    updated_date TIMESTAMP DEFAULT NOW()
    validation_date TIMESTAMP
    validation_protocol_url TEXT
    document_library JSONB               -- Array of documents
    standards_list JSONB                 -- Standards applied
    total_emission NUMERIC(15,2)
    retired_credits NUMERIC(15,2) DEFAULT 0
    available_credits NUMERIC(15,2) DEFAULT 0
    minimum_purchase_volume NUMERIC(10,2) DEFAULT 1
    notes TEXT

Table 2: project_types
    id SERIAL PRIMARY KEY
    code VARCHAR(10) NOT NULL UNIQUE     -- 'FC', 'WR', 'MC'
    name VARCHAR(255) NOT NULL           -- 'Forest Conservation'
    created_at TIMESTAMP DEFAULT NOW()
    updated_at TIMESTAMP DEFAULT NOW()

Table 3: transactions
    id SERIAL PRIMARY KEY
    transaction_type VARCHAR(20) NOT NULL    -- 'purchase', 'retirement'
    from_user_id INTEGER REFERENCES users(id)
    to_user_id INTEGER REFERENCES users(id)
    project_id INTEGER REFERENCES carbon_projects(id)
    quantity NUMERIC(10,2) NOT NULL
    price_per_credit NUMERIC(8,2)
    total_amount NUMERIC(10,2)
    stripe_payment_intent_id VARCHAR(255)
    guest_email VARCHAR(255)
    retirement_certificate_url TEXT
    created_at TIMESTAMP DEFAULT NOW()
    status VARCHAR(20) DEFAULT 'pending'
    processing_time_seconds INTEGER
    user_agent TEXT
    ip_address INET
    refund_amount NUMERIC(10,2) DEFAULT 0
    refund_reason TEXT

Additional Tables:
    - standards
    - standard_validators
    - users
    - credit_prices
    - user_credits
    - administrators

Indexes:
    CREATE INDEX idx_project_types_code ON project_types(code);
    CREATE INDEX idx_carbon_projects_type_id ON carbon_projects(project_type_id);
    CREATE INDEX idx_transactions_status ON transactions(status);
    CREATE INDEX idx_transactions_created_at ON transactions(created_at);
    CREATE INDEX idx_transactions_project_id ON transactions(project_id);

================================================================================
JWT AUTHENTICATION
================================================================================

Secret Key:
    JWT_SECRET = "carboncore-super-secret-jwt-key-2025-production-ready"
    Environment: os.getenv('JWT_SECRET', ...)

Token Structure:
    Header:
        {
            "alg": "HS256",
            "typ": "JWT"
        }

    Payload:
        {
            "email": "admin@carboncore.info",
            "permissions": {
                "projects": true,
                "standards": true,
                "transactions": true,
                "types": true,
                "delete": false
            },
            "exp": 1234567890,  // Unix timestamp (8 hours from iat)
            "iat": 1234567800,  // Issued at
            "iss": "carboncore-admin"
        }

Token Creation:
    def create_simple_jwt(payload):
        header = {"alg": "HS256", "typ": "JWT"}

        header_b64 = base64.urlsafe_b64encode(
            json.dumps(header).encode()
        ).decode().rstrip('=')

        payload_b64 = base64.urlsafe_b64encode(
            json.dumps(payload).encode()
        ).decode().rstrip('=')

        message = f"{header_b64}.{payload_b64}"
        signature = hmac.new(
            JWT_SECRET.encode(),
            message.encode(),
            hashlib.sha256
        ).digest()

        signature_b64 = base64.urlsafe_b64encode(
            signature
        ).decode().rstrip('=')

        return f"{header_b64}.{payload_b64}.{signature_b64}"

Token Verification:
    def verify_simple_jwt(token):
        parts = token.split('.')
        if len(parts) != 3:
            return {'valid': False, 'error': 'Invalid format'}

        header_b64, payload_b64, signature_b64 = parts

        # Verify signature
        message = f"{header_b64}.{payload_b64}"
        expected_signature = hmac.new(
            JWT_SECRET.encode(),
            message.encode(),
            hashlib.sha256
        ).digest()
        expected_signature_b64 = base64.urlsafe_b64encode(
            expected_signature
        ).decode().rstrip('=')

        if signature_b64 != expected_signature_b64:
            return {'valid': False, 'error': 'Invalid signature'}

        # Decode payload
        payload_b64 += '=' * (4 - len(payload_b64) % 4)
        payload = json.loads(
            base64.urlsafe_b64decode(payload_b64).decode()
        )

        # Check expiration
        if 'exp' in payload and time.time() > payload['exp']:
            return {'valid': False, 'error': 'Token expired'}

        return {
            'valid': True,
            'email': payload.get('email'),
            'permissions': payload.get('permissions', {})
        }

================================================================================
ADMIN CREDENTIALS
================================================================================

Production Admins:
    sb@carboncore.info:
        Password: CarbonCore2025Admin!
        Permissions: {
            projects: true,
            standards: true,
            transactions: true,
            types: true,
            delete: false
        }

    olof@carboncore.info:
        Password: CarbonCore2025Admin!
        Permissions: {
            projects: true,
            standards: true,
            transactions: true,
            types: true,
            delete: false
        }

    ph@carboncore.info:
        Password: CarbonCore2025Admin!
        Permissions: {
            projects: true,
            standards: true,
            transactions: true,
            types: true,
            delete: false
        }

    info@carboncore.info (Super Admin):
        Password: CarbonCore2025Admin!
        Permissions: {
            projects: true,
            standards: true,
            users: true,
            transactions: true,
            types: true,
            delete: true
        }

Password Storage:
    Hashed: SHA256
    Example: hashlib.sha256('CarbonCore2025Admin!'.encode()).hexdigest()

Rate Limiting:
    Max Attempts: 5 per 15 minutes per email
    Storage: In-memory dictionary (lambda warm state)
    Reset: Automatic after 15 minutes

================================================================================
REST API ENDPOINTS
================================================================================

Authentication Endpoints (No JWT Required):

POST /admin/login
    Request:
        {
            "email": "admin@carboncore.info",
            "password": "CarbonCore2025Admin!"
        }

    Response (Success):
        {
            "success": true,
            "token": "eyJhbGc...",
            "permissions": {...},
            "user": {
                "email": "admin@carboncore.info",
                "role": "SuperAdmin"
            }
        }

    Response (Failure):
        {
            "success": false,
            "message": "Invalid credentials"
        }

    Rate Limit: 5 attempts per 15 minutes

GET /admin/validate
    Headers:
        Authorization: Bearer {token}

    Response (Valid):
        {
            "valid": true,
            "email": "admin@carboncore.info",
            "permissions": {...}
        }

    Response (Invalid):
        {
            "valid": false,
            "error": "Token expired"
        }

Public Endpoints (No JWT Required):

GET /projects
    Description: List all carbon projects
    Parameters: None
    Response: Array of project objects

GET /projects/{id}
    Description: Get project details
    Parameters: id (path parameter)
    Response: Single project object

Protected Endpoints (JWT Required):

GET /admin/projects
    Description: Admin project list with full details
    Headers: Authorization: Bearer {token}
    Response: Array of projects with admin fields

POST /admin/projects
    Description: Create new project
    Headers: Authorization: Bearer {token}
    Body:
        {
            "project_name": "...",
            "project_initiator": "...",
            "project_type_id": 1,
            "country": "...",
            "region": "...",
            "vintage_year": 2025,
            "total_area": 1000,
            "co2_absorption": 50000,
            "available_credits": 50000,
            "standards_list": ["Paris Agreement", "VCS"],
            ...
        }
    Response:
        {
            "success": true,
            "project_id": 123
        }

PUT /admin/projects/{id}
    Description: Update project
    Headers: Authorization: Bearer {token}
    Body: Partial project object with fields to update
    Response:
        {
            "success": true,
            "message": "Project updated"
        }

DELETE /admin/projects/{id}
    Description: Delete project (Super Admin only)
    Headers: Authorization: Bearer {token}
    Permission Required: delete: true
    Response:
        {
            "success": true,
            "message": "Project deleted"
        }

GET /admin/transactions
    Description: View all transactions with analytics
    Headers: Authorization: Bearer {token}
    Response: Array of transaction objects

GET /admin/project-types
    Description: List project types with usage count
    Headers: Authorization: Bearer {token}
    Response: Array of project type objects

POST /admin/project-types
    Description: Create new project type
    Headers: Authorization: Bearer {token}
    Body:
        {
            "code": "FC",
            "name": "Forest Conservation"
        }

PUT /admin/project-types/{id}
    Description: Update project type
    Headers: Authorization: Bearer {token}

DELETE /admin/project-types/{id}
    Description: Delete project type (if not in use)
    Headers: Authorization: Bearer {token}
    Permission Required: delete: true

================================================================================
ERROR HANDLING
================================================================================

HTTP Status Codes:
    200 - Success
    401 - Unauthorized (invalid/expired token)
    403 - Forbidden (insufficient permissions)
    404 - Not Found
    500 - Internal Server Error

Error Response Format:
    {
        "message": "Error description",
        "error": "Detailed error message"
    }

Lambda Error Handling:
    try:
        # Operation
    except Exception as e:
        error_message = str(e)
        print(f"❌ Error: {error_message}")
        traceback.print_exc()

        # Special handling for auth errors
        if 'Unauthorized' in error_message:
            return {
                'statusCode': 401,
                'headers': {
                    'Access-Control-Allow-Origin': '*',
                    'Content-Type': 'application/json'
                },
                'body': json.dumps({
                    'message': 'Unauthorized',
                    'error': error_message
                })
            }

        # Generic error
        return {
            'statusCode': 500,
            'headers': {
                'Access-Control-Allow-Origin': '*',
                'Content-Type': 'application/json'
            },
            'body': json.dumps({
                'message': 'Internal server error',
                'error': error_message
            })
        }

================================================================================
CORS CONFIGURATION
================================================================================

CORS Headers (All Responses):
    Access-Control-Allow-Origin: *
    Access-Control-Allow-Methods: GET,POST,PUT,DELETE,OPTIONS
    Access-Control-Allow-Headers: Content-Type,Authorization,X-Amz-Date,
                                  X-Api-Key,X-Amz-Security-Token

OPTIONS Preflight:
    if event.get('httpMethod') == 'OPTIONS':
        return {
            'statusCode': 200,
            'headers': {
                'Access-Control-Allow-Origin': '*',
                'Access-Control-Allow-Methods': 'GET,POST,PUT,DELETE,OPTIONS',
                'Access-Control-Allow-Headers': 'Content-Type,Authorization,...'
            },
            'body': ''
        }

================================================================================
FRONTEND INTEGRATION
================================================================================

API Base URL:
    const API_BASE = 'https://api.carboncore.earth';

Login Example:
    async function adminLogin(email, password) {
        const response = await fetch(`${API_BASE}/admin/login`, {
            method: 'POST',
            headers: {
                'Content-Type': 'application/json'
            },
            body: JSON.stringify({ email, password })
        });

        const data = await response.json();

        if (data.success) {
            localStorage.setItem('adminToken', data.token);
            localStorage.setItem('adminPermissions',
                                JSON.stringify(data.permissions));
            return data;
        } else {
            throw new Error(data.message);
        }
    }

Authenticated Request:
    async function getAdminProjects() {
        const token = localStorage.getItem('adminToken');

        const response = await fetch(`${API_BASE}/admin/projects`, {
            method: 'GET',
            headers: {
                'Authorization': `Bearer ${token}`,
                'Content-Type': 'application/json'
            }
        });

        if (response.status === 401) {
            // Token expired, redirect to login
            window.location.href = '/admin-login.html';
            return;
        }

        const projects = await response.json();
        return projects;
    }

Token Refresh Strategy:
    // Check token validity on page load
    async function validateSession() {
        const token = localStorage.getItem('adminToken');
        if (!token) return false;

        const response = await fetch(`${API_BASE}/admin/validate`, {
            headers: {
                'Authorization': `Bearer ${token}`
            }
        });

        const result = await response.json();
        return result.valid;
    }

    // Call on protected pages
    if (!await validateSession()) {
        window.location.href = '/admin-login.html';
    }

================================================================================
PERFORMANCE OPTIMIZATION
================================================================================

Database Connection Pooling:
    conn = psycopg2.connect(
        host='...',
        database='...',
        user='...',
        password='...',
        port=5432,
        connect_timeout=10,
        keepalives=1,
        keepalives_idle=30,
        keepalives_interval=10,
        keepalives_count=5
    )

Lambda Warm State:
    - Reuse database connections
    - Cache token validation results (in-memory)
    - Rate limit storage in lambda memory
    - Connection pooling

Query Optimization:
    - Use indexes for frequent filters
    - Limit result sets
    - Use EXPLAIN ANALYZE for slow queries
    - Denormalize where appropriate

Batch Operations:
    - Bulk inserts for multiple records
    - Transaction batching
    - Prepared statements

================================================================================
DEPLOYMENT CHECKLIST
================================================================================

Pre-Deployment:
    [ ] Test all endpoints in staging
    [ ] Verify JWT expiration times
    [ ] Check database indexes
    [ ] Review CORS configuration
    [ ] Test rate limiting
    [ ] Validate error handling
    [ ] Check Lambda memory limits
    [ ] Test with production data volume

Production Deployment:
    [ ] Update Lambda function code
    [ ] Set environment variables
    [ ] Configure API Gateway
    [ ] Update DNS (custom domain)
    [ ] Enable CloudWatch logging
    [ ] Set up alarms
    [ ] Test all endpoints
    [ ] Monitor error rates

Post-Deployment:
    [ ] Monitor Lambda metrics
    [ ] Check database performance
    [ ] Review CloudWatch logs
    [ ] Test frontend integration
    [ ] Verify JWT authentication
    [ ] Check rate limiting

================================================================================
MONITORING & LOGGING
================================================================================

CloudWatch Metrics:
    - Lambda invocations
    - Error count
    - Duration
    - Throttles
    - Concurrent executions

Custom Logging:
    print(f"✅ Success: {message}")
    print(f"❌ Error: {message}")
    print(f"🔐 Auth: {message}")
    print(f"📊 Query: {message}")

Log Levels:
    INFO: Normal operations
    WARNING: Rate limit hits, validation errors
    ERROR: Exceptions, database failures
    DEBUG: Detailed request/response data

Alarms:
    - Error rate > 5%
    - Duration > 10 seconds
    - Database connection failures
    - Failed authentications > threshold

================================================================================