import os
import psycopg2
from dotenv import load_dotenv

load_dotenv()

def create_table():
    db_host = os.getenv("DB_HOST", "192.168.3.195")
    db_user = os.getenv("DB_USER", "value")
    db_pass = os.getenv("DB_PASSWORD", "Value609!")
    db_name = os.getenv("DB_NAME", "fa3")
    db_port = os.getenv("DB_PORT", "5432")

    try:
        conn = psycopg2.connect(
            host=db_host, user=db_user, password=db_pass, dbname=db_name, port=db_port
        )
        cur = conn.cursor()
        
        print("Creating stockcard_quarter table...")
        
        create_sql = """
        CREATE TABLE IF NOT EXISTS stockcard_quarter (
            id SERIAL PRIMARY KEY,
            company_code TEXT,
            indicator TEXT,
            value TEXT,
            currency TEXT,
            value_date DATE,
            update_date TIMESTAMP WITHOUT TIME ZONE,
            source TEXT
        );
        """
        cur.execute(create_sql)
        
        # Create indexes
        print("Creating indexes...")
        cur.execute("CREATE INDEX IF NOT EXISTS idx_stockcard_quarter_company_code ON stockcard_quarter(company_code);")
        cur.execute("CREATE INDEX IF NOT EXISTS idx_stockcard_quarter_value_date ON stockcard_quarter(value_date);")
        
        conn.commit()
        print("Done!")
        
        cur.close()
        conn.close()

    except Exception as e:
        print(f"Error: {e}")

if __name__ == "__main__":
    create_table()