import sqlite3
import pandas as pd
import pandas_ta as ta
import numpy as np
import json
import warnings

# Suppress warnings from pandas_ta when calculating indicators
warnings.filterwarnings('ignore', category=FutureWarning)
pd.set_option('mode.chained_assignment', None)

# --- CONFIGURATION ---
DB_PATH = '/content/gdrive/MyDrive/TradingBotLogs/ohlcv_data_BTC.db'
TABLE_NAME = 'btcusd_1h_data_12y'
TIME_COLUMN = 'timestamp' # Name of the column containing the datetime/timestamp
WINDOW_SIZE = 72
PREDICTION_HORIZON = 12

# --- STEP 1: LOAD AND INITIALIZE DATA ---
print(f"--- Attempting to load data from SQLite: {DB_PATH} - Table: {TABLE_NAME} ---")
try:
    conn = sqlite3.connect(DB_PATH)
    query = f"SELECT * FROM {TABLE_NAME}"
    df = pd.read_sql_query(query, conn)
    conn.close()
    print("--- Successfully loaded BTC data from SQLite ---")
except Exception as e:
    print(f"Error loading data: {e}")
    raise

# Ensure column names are lowercased for pandas_ta compatibility
df.columns = [col.lower() for col in df.columns]

# --- FIX IS HERE ---
# 1. Convert the string timestamp column to datetime WITHOUT specifying unit='ms'.
#    Pandas will infer the format from the string 'YYYY-MM-DD HH:MM:SS+00:00'.
df['datetime'] = pd.to_datetime(df[TIME_COLUMN]) 
# --------------------

# Set the datetime index and sort
df = df.set_index('datetime').sort_index()

# Check column names after lowercasing
print("Columns in loaded data:", df.columns.tolist())
# Check the index type to confirm conversion
print("New Index Type:", df.index.dtype)


# --- STEP 2: FEATURE ENGINEERING (ADD TECHNICAL INDICATORS) ---

print("--- Calculating Technical Indicators (SMA, EMA, RSI, Log Returns) ---")

# Simple Moving Average (SMA) - 20 periods
df['sma_20'] = df['close'].rolling(20).mean()

# Exponential Moving Average (EMA) - 50 periods
df['ema_50'] = df['close'].ewm(span=50, adjust=False).mean()

# Relative Strength Index (RSI) - 14 periods
df['rsi_14'] = ta.rsi(df['close'], length=14)

# Log Returns (Percentage change in price, then natural log)
df['log_return'] = df['close'].pct_change()
# Use np.log1p for numerical stability, though the original np.log(1+x) works if you handle zeros/negative carefully
df['log_return'] = df['log_return'].apply(lambda x: np.log(1 + x) if 1 + x > 0 else 0) 

# Drop initial NaN rows created by rolling window calculations
df = df.dropna()

print(f"Data ready. Remaining rows after cleaning: {len(df)}")

# --- STEP 3: INSTRUCTIONAL DATASET CREATION (SLIDING WINDOW) ---

def format_for_llm(data_window, full_df):
    """Generates a single instruction-tuning sample."""
    
    # --- 1. Define the Context (Input Features) ---
    context_data = data_window[['close', 'volume', 'rsi_14', 'sma_20']].tail(5) 
    context_table = context_data.to_markdown(numalign="left", stralign="left")
    
    # --- 2. Define the Instruction ---
    instruction = (
        f"Analyze the 5 most recent 1-hour BTC bars in the table below, focusing on the Close price, "
        f"Volume, RSI (Relative Strength Index), and 20-period SMA. "
        f"Predict the price direction (UP or DOWN) for the next {PREDICTION_HORIZON} hours and provide a brief technical rationale."
    )
    
    # --- 3. Define the Response (Ground Truth Label) ---
    try:
        current_close = data_window['close'].iloc[-1]
        
        # Look up the close price after the prediction horizon
        # Use .iloc[] index to safely get the bar (row) that is 12 steps after the end of the current window
        target_index_loc = full_df.index.get_loc(data_window.index[-1]) + PREDICTION_HORIZON
        target_close = full_df['close'].iloc[target_index_loc]

        # Calculate final movement
        movement = target_close - current_close
        direction = "UP" if movement > 0 else "DOWN"
        
        # Craft the detailed response
        response = (
            f"The {PREDICTION_HORIZON}-hour prediction is **{direction}**. "
            f"The final bar's RSI of {data_window['rsi_14'].iloc[-1]:.2f} suggests "
            f"{'overbought pressure' if data_window['rsi_14'].iloc[-1] > 70 else 'room to run'}. "
            f"The current Close is {'above' if current_close > data_window['sma_20'].iloc[-1] else 'below'} the 20-period SMA, "
            f"which supports a {direction} bias. The price ultimately moved ${movement:.2f}."
        )
        
    except IndexError:
        return None # Skip if there's not enough future data

    # Final Instruction-Tuning Format (Mistral/Llama standard)
    template = f"<s>[INST] {instruction}\n\n{context_table} [/INST] {response}</s>"
    return {'text': template}

# --- Generate the full dataset by sliding the window ---

print(f"--- Generating samples (Window: {WINDOW_SIZE}h, Horizon: {PREDICTION_HORIZON}h) ---")
fine_tuning_samples = []

# Iterate, leaving enough bars at the end for the prediction horizon
for i in range(WINDOW_SIZE, len(df) - PREDICTION_HORIZON):
    history_window = df.iloc[i - WINDOW_SIZE : i]
    
    # Pass the full df to the function for target lookup
    sample = format_for_llm(history_window, df) 
    
    if sample:
        fine_tuning_samples.append(sample)

# --- STEP 4: SAVE DATASET ---

output_file = 'btc_instruction_dataset.jsonl'
with open(output_file, 'w') as f:
    for sample in fine_tuning_samples:
        f.write(json.dumps(sample) + '\n')

print(f"\nSuccessfully generated {len(fine_tuning_samples)} hourly fine-tuning samples.")
if fine_tuning_samples:
    print(f"Example of one training sample (first entry):\n")
    print("="*80)
    print(fine_tuning_samples[0]['text'])
    print("="*80)
print(f"\nDataset saved to '{output_file}'. You are now ready for the fine-tuning stage (QLoRA/SFTTrainer).")

python

--- Attempting to load data from SQLite: /content/gdrive/MyDrive/TradingBotLogs/ohlcv_data_BTC.db - Table: btcusd_1h_data_12y ---
--- Successfully loaded BTC data from SQLite ---
Columns in loaded data: ['timestamp', 'open', 'high', 'low', 'close', 'volume']
New Index Type: datetime64[ns, UTC]
--- Calculating Technical Indicators (SMA, EMA, RSI, Log Returns) ---
Data ready. Remaining rows after cleaning: 89769
--- Generating samples (Window: 72h, Horizon: 12h) ---

Successfully generated 89685 hourly fine-tuning samples.
Example of one training sample (first entry):

================================================================================
<s>[INST] Analyze the 5 most recent 1-hour BTC bars in the table below, focusing on the Close price, Volume, RSI (Relative Strength Index), and 20-period SMA. Predict the price direction (UP or DOWN) for the next 12 hours and provide a brief technical rationale.

| datetime                  | close   | volume   | rsi_14   | sma_20   |
|:--------------------------|:--------|:---------|:---------|:---------|
| 2013-10-26 08:00:00+00:00 | 184.58  | 4        | 48.8856  | 187.968  |
| 2013-10-27 08:00:00+00:00 | 182.21  | 15       | 47.3294  | 186.801  |
| 2013-10-27 12:00:00+00:00 | 179.47  | 9        | 45.5251  | 186.232  |
| 2013-10-27 14:00:00+00:00 | 180.276 | 8        | 46.1755  | 186.75   |
| 2013-10-27 15:00:00+00:00 | 179.236 | 4        | 45.4222  | 185.771  | [/INST] The 12-hour prediction is **UP**. The final bar's RSI of 45.42 suggests room to run. The current Close is below the 20-period SMA, which supports a UP bias. The price ultimately moved $21.53.</s>
================================================================================

Dataset saved to 'btc_instruction_dataset.jsonl'. You are now ready for the fine-tuning stage (QLoRA/SFTTrainer).

Training Hyperparameters

python

import os
import torch
from datasets import load_dataset
from transformers import (
    AutoModelForCausalLM,
    AutoTokenizer,
    BitsAndBytesConfig,
    TrainingArguments,
)
from peft import LoraConfig
from trl import SFTTrainer

# --- 0. GPU SETUP ---
device = torch.device("cuda" if torch.cuda.is_available() else "cpu")
print(f"--- Running on device: {device} ---")
if device.type == 'cpu':
    raise RuntimeError("GPU not found. QLoRA fine-tuning is not feasible without a CUDA-enabled GPU.")

# --- 1. CONFIGURATION ---
MODEL_NAME = "mistralai/Mistral-7B-v0.1"
DATASET_PATH = "/content/gdrive/MyDrive/CryptoFT/dataset/btc_instruction_dataset.jsonl"
NEW_MODEL_NAME = "Mistral-7B-BTC-Expert"
OUTPUT_DIR = "/content/gdrive/MyDrive/CryptoFT/models/results_btc_finetune"
MAX_SEQ_LENGTH = 1024

# Create output directory if it doesn't exist
os.makedirs(OUTPUT_DIR, exist_ok=True)

# LoRA Configuration
peft_config = LoraConfig(
    lora_alpha=16,
    lora_dropout=0.1,
    r=64,
    bias="none",
    task_type="CAUSAL_LM",
    target_modules=[
        "q_proj", 
        "k_proj", 
        "v_proj", 
        "o_proj",
        "gate_proj",
        "up_proj",
        "down_proj"
    ],
)

# --- Training Arguments (Hyperparameters) ---
training_arguments = TrainingArguments(
    output_dir=OUTPUT_DIR,
    num_train_epochs=1,
    per_device_train_batch_size=4,
    gradient_accumulation_steps=4,
    optim="paged_adamw_8bit",
    save_steps=500,
    logging_steps=50,
    learning_rate=2e-4,
    weight_decay=0.001,
    fp16=True, 
    bf16=False,
    max_grad_norm=0.3,
    warmup_ratio=0.03,
    group_by_length=True,
    lr_scheduler_type="cosine",
    disable_tqdm=False,
    report_to="none",
    
    # CRITICAL ADDITIONS TO SHOW EVAL LOSS
    eval_strategy="steps", # Enable evaluation at specified step intervals
    eval_steps=500,              # Evaluate every 500 steps
    load_best_model_at_end=True, # Load the best checkpoint based on eval_loss at the end
    metric_for_best_model="eval_loss", # Use validation loss as the metric
)

Speeds, Sizes, Times [optional]

[More Information Needed]

Evaluation

python

# This code block successfully loads the model 

import torch
from peft import PeftModel
from transformers import AutoModelForCausalLM, AutoTokenizer, BitsAndBytesConfig

# Configuration
HUB_MODEL_ID = "frankmorales2020/Mistral-7B-BTC-Expert"
BASE_MODEL_ID = "mistralai/Mistral-7B-v0.1"
DEVICE = "cuda" if torch.cuda.is_available() else "cpu"

# --- 1. Load Model with Quantization ---
print(f"--- 1. Loading Model onto {DEVICE} ---")
try:
    bnb_config = BitsAndBytesConfig(
        load_in_4bit=True,
        bnb_4bit_quant_type="nf4",
        bnb_4bit_compute_dtype=torch.float16,
    )

    base_model = AutoModelForCausalLM.from_pretrained(
        BASE_MODEL_ID,
        quantization_config=bnb_config,
        device_map="auto",
    )

    tokenizer = AutoTokenizer.from_pretrained(HUB_MODEL_ID)
    tokenizer.pad_token = tokenizer.eos_token 

    model = PeftModel.from_pretrained(base_model, HUB_MODEL_ID).eval()
    print("✅ Model loaded successfully.")
except Exception as e:
    print(f"FATAL ERROR during model loading: {e}")
    raise


```python


### Results

```python

--- Starting Fine-Tuning --- [5550/5550 7:34:55, Epoch 1/1]

Step Training Loss Validation Loss Entropy Num Tokens Mean Token Accuracy

500 0.215800 0.215459 0.215031 8192000.000000 0.916472

1000 0.217400 0.214608 0.214947 16384000.000000 0.916857

1500 0.212100 0.212424 0.212158 24576000.000000 0.917407

2000 0.211400 0.211059 0.210042 32768000.000000 0.917807

2500 0.209900 0.210005 0.208038 40960000.000000 0.918461

3000 0.207400 0.208606 0.208269 49152000.000000 0.919172

3500 0.206600 0.207157 0.207384 57344000.000000 0.919553

4000 0.204900 0.205344 0.205466 65536000.000000 0.920340

4500 0.203300 0.203777 0.203547 73728000.000000 0.921309

5000 0.201200 0.202193 0.202084 81920000.000000 0.921827

5500 0.200400 0.201922 0.201656 90112000.000000 0.922047

Compute Infrastructure

python

Sat Oct  4 05:42:52 2025       
+-----------------------------------------------------------------------------------------+
| NVIDIA-SMI 550.54.15              Driver Version: 550.54.15      CUDA Version: 12.4     |
|-----------------------------------------+------------------------+----------------------+
| GPU  Name                 Persistence-M | Bus-Id          Disp.A | Volatile Uncorr. ECC |
| Fan  Temp   Perf          Pwr:Usage/Cap |           Memory-Usage | GPU-Util  Compute M. |
|                                         |                        |               MIG M. |
|=========================================+========================+======================|
|   0  NVIDIA A100-SXM4-80GB          Off |   00000000:00:05.0 Off |                    0 |
| N/A   34C    P0             56W /  400W |       0MiB /  81920MiB |      0%      Default |
|                                         |                        |             Disabled |
+-----------------------------------------+------------------------+----------------------+
                                                                                         
+-----------------------------------------------------------------------------------------+
| Processes:                                                                              |
|  GPU   GI   CI        PID   Type   Process name                              GPU Memory |
|        ID   ID                                                               Usage      |
|=========================================================================================|
|  No running processes found                                                             |
+-----------------------------------------------------------------------------------------+

PEFT 0.17.1