database_save.py

import os
from dotenv import load_dotenv
import datetime
import requests
import pandas as pd
from sqlalchemy import create_engine, text
from sqlalchemy.exc import SQLAlchemyError

load_dotenv()  # loads variables from .env automatically
POLYGON_API_KEY = os.getenv("POLYGON_API_KEY")

# Database connection info
DATABASE_URL = "postgresql://postgres:123456789@localhost/trader_master"
engine = create_engine(DATABASE_URL)

BASE_URL = "https://api.polygon.io/v2/aggs/ticker"

def get_table_name(symbol: str) -> str:
    return f'candle_{symbol.lower()}_minute'

def create_new_table(symbol: str):
    table_name = get_table_name(symbol)
    create_table_query = f"""
    CREATE TABLE IF NOT EXISTS {table_name} (
        id SERIAL PRIMARY KEY,
        ts INTEGER CHECK (ts >= 1000000000 AND ts <= 9999999999),
        o REAL,
        h REAL,
        l REAL,
        c REAL,
        v INTEGER,
        vw REAL
    );
    """
    try:
        # 🔥 begin() auto-commits the transaction
        with engine.begin() as connection:
            connection.execute(text(create_table_query))
            print(f"Table '{table_name}' created or already exists.")
    except SQLAlchemyError as e:
        print(f"Error creating table: {e}")

def save_last_n_minutes(symbol: str, input_len: int):
    all_candles = []

    symbol = symbol.upper()

    days_back = 0
    while len(all_candles) < input_len:
        day = (datetime.datetime.now() - datetime.timedelta(days=days_back)).strftime("%Y-%m-%d")
        url = f"{BASE_URL}/{symbol}/range/1/minute/{day}/{day}"
        params = {
            "adjusted": "true",
            "sort": "desc",
            "limit": 5000,
            "apiKey": POLYGON_API_KEY
        }

        r = requests.get(url, params=params)
        r.raise_for_status()

        data = r.json()

        if "results" in data and data["results"]:
            all_candles.extend(data["results"])

        days_back += 1
        if days_back > 30:
            break

    table_name = get_table_name(symbol)

    if all_candles:
        df = pd.DataFrame(all_candles)[['t', 'o', 'h', 'l', 'c', 'v', 'vw']]
        df.columns = ['ts', 'o', 'h', 'l', 'c', 'v', 'vw']

        # 🔥 FIX: convert milliseconds → seconds (fits INTEGER)
        df['ts'] = (df['ts'] // 1000).astype('int64')

        # FIX: index=False so pandas does NOT try to insert "index" column
        df.to_sql(table_name, engine, if_exists='append', index=False)

        print(f"Saved {len(df)} candles to '{table_name}'.")

    return all_candles[-min(len(all_candles), input_len):] if all_candles else []

def save_last_5_yeaers(symbol: str):
    all_candles = []
    symbol = symbol.upper()

    today = datetime.datetime.now()
    start_date = today - datetime.timedelta(days=5*365)

    current_date = today

    while current_date >= start_date:
        day = current_date.strftime("%Y-%m-%d")
        url = f"{BASE_URL}/{symbol}/range/1/minute/{day}/{day}"
        params = {
            "adjusted": "true",
            "sort": "desc",
            "limit": 5000,
            "apiKey": POLYGON_API_KEY
        }

        r = requests.get(url, params=params)
        r.raise_for_status()
        data = r.json()

        if "results" in data and data["results"]:
            all_candles.extend(data["results"])
            print(f"Fetched {len(data['results'])} candles from {day}")
        else:
            print(f"No data on {day}")

        current_date -= datetime.timedelta(days=1)

    table_name = get_table_name(symbol)

    if all_candles:
        # *** FIXED — removed the bad line ***
        df = pd.DataFrame(all_candles)[['t', 'o', 'h', 'l', 'c', 'v', 'vw']]
        df.columns = ['ts', 'o', 'h', 'l', 'c', 'v', 'vw']

        df['ts'] = (df['ts'] // 1000).astype('int64')

        df.to_sql(table_name, engine, if_exists='append', index=False)
        print(f"Saved {len(df)} candles to '{table_name}'.")

    return all_candles



# Example usage
if __name__ == "__main__":
    symbol = "SNDK"
    create_new_table(symbol)  # Create the new table
    input_len = 2000000  # Adjust this based on the actual number of available candles
    # save_last_n_minutes(symbol, input_len)

    save_last_5_yeaers(symbol)

    # # Load the data and convert the timestamp column
    # df = pd.read_sql_table(f'candle_{symbol.lower()}_minute', engine)
    # df['time'] = pd.to_datetime(df['ts']/100, unit='s')