#!/usr/bin/env python3
"""
Update static_data table with expanded astronomical data
"""
import asyncio
import sys
import os

# Add parent directory to path
sys.path.insert(0, os.path.dirname(os.path.dirname(os.path.abspath(__file__))))

from app.database import get_db
from app.services.db_service import static_data_service
from app.models.db import StaticData
from sqlalchemy import select, update, insert
from sqlalchemy.dialects.postgresql import insert as pg_insert


# Expanded constellation data (15 constellations)
CONSTELLATIONS = [
    {
        "name": "Orion",
        "name_zh": "猎户座",
        "data": {
            "stars": [
                {"name": "Betelgeuse", "ra": 88.79, "dec": 7.41},
                {"name": "Bellatrix", "ra": 81.28, "dec": 6.35},
                {"name": "Alnitak", "ra": 85.19, "dec": -1.94},
                {"name": "Alnilam", "ra": 84.05, "dec": -1.20},
                {"name": "Mintaka", "ra": 83.00, "dec": -0.30},
                {"name": "Saiph", "ra": 86.94, "dec": -9.67},
                {"name": "Rigel", "ra": 78.63, "dec": -8.20}
            ],
            "lines": [[0, 1], [1, 2], [2, 3], [3, 4], [2, 5], [5, 6]]
        }
    },
    {
        "name": "Ursa Major",
        "name_zh": "大熊座",
        "data": {
            "stars": [
                {"name": "Dubhe", "ra": 165.93, "dec": 61.75},
                {"name": "Merak", "ra": 165.46, "dec": 56.38},
                {"name": "Phecda", "ra": 178.46, "dec": 53.69},
                {"name": "Megrez", "ra": 183.86, "dec": 57.03},
                {"name": "Alioth", "ra": 193.51, "dec": 55.96},
                {"name": "Mizar", "ra": 200.98, "dec": 54.93},
                {"name": "Alkaid", "ra": 206.89, "dec": 49.31}
            ],
            "lines": [[0, 1], [1, 2], [2, 3], [3, 4], [4, 5], [5, 6]]
        }
    },
    {
        "name": "Cassiopeia",
        "name_zh": "仙后座",
        "data": {
            "stars": [
                {"name": "Caph", "ra": 2.29, "dec": 59.15},
                {"name": "Schedar", "ra": 10.13, "dec": 56.54},
                {"name": "Navi", "ra": 14.18, "dec": 60.72},
                {"name": "Ruchbah", "ra": 21.45, "dec": 60.24},
                {"name": "Segin", "ra": 25.65, "dec": 63.67}
            ],
            "lines": [[0, 1], [1, 2], [2, 3], [3, 4]]
        }
    },
    {
        "name": "Leo",
        "name_zh": "狮子座",
        "data": {
            "stars": [
                {"name": "Regulus", "ra": 152.09, "dec": 11.97},
                {"name": "Denebola", "ra": 177.26, "dec": 14.57},
                {"name": "Algieba", "ra": 154.99, "dec": 19.84},
                {"name": "Zosma", "ra": 168.53, "dec": 20.52},
                {"name": "Chertan", "ra": 173.95, "dec": 15.43}
            ],
            "lines": [[0, 2], [2, 3], [3, 4], [4, 1], [1, 0]]
        }
    },
    {
        "name": "Scorpius",
        "name_zh": "天蝎座",
        "data": {
            "stars": [
                {"name": "Antares", "ra": 247.35, "dec": -26.43},
                {"name": "Shaula", "ra": 263.40, "dec": -37.10},
                {"name": "Sargas", "ra": 264.33, "dec": -43.00},
                {"name": "Dschubba", "ra": 240.08, "dec": -22.62},
                {"name": "Lesath", "ra": 262.69, "dec": -37.29}
            ],
            "lines": [[3, 0], [0, 1], [1, 4], [1, 2]]
        }
    },
    {
        "name": "Cygnus",
        "name_zh": "天鹅座",
        "data": {
            "stars": [
                {"name": "Deneb", "ra": 310.36, "dec": 45.28},
                {"name": "Sadr", "ra": 305.56, "dec": 40.26},
                {"name": "Albireo", "ra": 292.68, "dec": 27.96},
                {"name": "Delta Cygni", "ra": 296.24, "dec": 45.13},
                {"name": "Gienah", "ra": 314.29, "dec": 33.97}
            ],
            "lines": [[0, 1], [1, 2], [1, 3], [1, 4]]
        }
    },
    {
        "name": "Aquila",
        "name_zh": "天鹰座",
        "data": {
            "stars": [
                {"name": "Altair", "ra": 297.70, "dec": 8.87},
                {"name": "Tarazed", "ra": 296.56, "dec": 10.61},
                {"name": "Alshain", "ra": 298.83, "dec": 6.41},
                {"name": "Deneb el Okab", "ra": 304.48, "dec": 15.07}
            ],
            "lines": [[1, 0], [0, 2], [0, 3]]
        }
    },
    {
        "name": "Lyra",
        "name_zh": "天琴座",
        "data": {
            "stars": [
                {"name": "Vega", "ra": 279.23, "dec": 38.78},
                {"name": "Sheliak", "ra": 282.52, "dec": 33.36},
                {"name": "Sulafat", "ra": 284.74, "dec": 32.69},
                {"name": "Delta Lyrae", "ra": 283.82, "dec": 36.90}
            ],
            "lines": [[0, 3], [3, 1], [1, 2], [2, 0]]
        }
    },
    {
        "name": "Pegasus",
        "name_zh": "飞马座",
        "data": {
            "stars": [
                {"name": "Markab", "ra": 346.19, "dec": 15.21},
                {"name": "Scheat", "ra": 345.94, "dec": 28.08},
                {"name": "Algenib", "ra": 3.31, "dec": 15.18},
                {"name": "Enif", "ra": 326.05, "dec": 9.88}
            ],
            "lines": [[0, 1], [1, 2], [2, 0], [0, 3]]
        }
    },
    {
        "name": "Andromeda",
        "name_zh": "仙女座",
        "data": {
            "stars": [
                {"name": "Alpheratz", "ra": 2.10, "dec": 29.09},
                {"name": "Mirach", "ra": 17.43, "dec": 35.62},
                {"name": "Almach", "ra": 30.97, "dec": 42.33},
                {"name": "Delta Andromedae", "ra": 8.78, "dec": 30.86}
            ],
            "lines": [[0, 3], [3, 1], [1, 2]]
        }
    },
    {
        "name": "Taurus",
        "name_zh": "金牛座",
        "data": {
            "stars": [
                {"name": "Aldebaran", "ra": 68.98, "dec": 16.51},
                {"name": "Elnath", "ra": 81.57, "dec": 28.61},
                {"name": "Alcyone", "ra": 56.87, "dec": 24.11},
                {"name": "Zeta Tauri", "ra": 84.41, "dec": 21.14}
            ],
            "lines": [[0, 1], [0, 2], [1, 3]]
        }
    },
    {
        "name": "Gemini",
        "name_zh": "双子座",
        "data": {
            "stars": [
                {"name": "Pollux", "ra": 116.33, "dec": 28.03},
                {"name": "Castor", "ra": 113.65, "dec": 31.89},
                {"name": "Alhena", "ra": 99.43, "dec": 16.40},
                {"name": "Mebsuta", "ra": 100.98, "dec": 25.13}
            ],
            "lines": [[0, 1], [0, 2], [1, 3], [3, 2]]
        }
    },
    {
        "name": "Virgo",
        "name_zh": "室女座",
        "data": {
            "stars": [
                {"name": "Spica", "ra": 201.30, "dec": -11.16},
                {"name": "Porrima", "ra": 190.42, "dec": 1.76},
                {"name": "Vindemiatrix", "ra": 195.54, "dec": 10.96},
                {"name": "Heze", "ra": 211.67, "dec": -0.67}
            ],
            "lines": [[2, 1], [1, 0], [0, 3]]
        }
    },
    {
        "name": "Sagittarius",
        "name_zh": "人马座",
        "data": {
            "stars": [
                {"name": "Kaus Australis", "ra": 276.04, "dec": -34.38},
                {"name": "Nunki", "ra": 283.82, "dec": -26.30},
                {"name": "Ascella", "ra": 290.97, "dec": -29.88},
                {"name": "Kaus Media", "ra": 276.99, "dec": -29.83},
                {"name": "Kaus Borealis", "ra": 279.23, "dec": -25.42}
            ],
            "lines": [[0, 3], [3, 4], [4, 1], [1, 2]]
        }
    },
    {
        "name": "Capricornus",
        "name_zh": "摩羯座",
        "data": {
            "stars": [
                {"name": "Deneb Algedi", "ra": 326.76, "dec": -16.13},
                {"name": "Dabih", "ra": 305.25, "dec": -14.78},
                {"name": "Nashira", "ra": 325.02, "dec": -16.66},
                {"name": "Algedi", "ra": 304.51, "dec": -12.51}
            ],
            "lines": [[3, 1], [1, 2], [2, 0]]
        }
    }
]

# Expanded galaxy data (12 galaxies)
GALAXIES = [
    {
        "name": "Andromeda Galaxy",
        "name_zh": "仙女座星系",
        "data": {
            "type": "spiral",
            "distance_mly": 2.537,
            "ra": 10.68,
            "dec": 41.27,
            "magnitude": 3.44,
            "diameter_kly": 220,
            "color": "#CCDDFF"
        }
    },
    {
        "name": "Triangulum Galaxy",
        "name_zh": "三角座星系",
        "data": {
            "type": "spiral",
            "distance_mly": 2.73,
            "ra": 23.46,
            "dec": 30.66,
            "magnitude": 5.72,
            "diameter_kly": 60,
            "color": "#AACCEE"
        }
    },
    {
        "name": "Large Magellanic Cloud",
        "name_zh": "大麦哲伦云",
        "data": {
            "type": "irregular",
            "distance_mly": 0.163,
            "ra": 80.89,
            "dec": -69.76,
            "magnitude": 0.9,
            "diameter_kly": 14,
            "color": "#DDCCFF"
        }
    },
    {
        "name": "Small Magellanic Cloud",
        "name_zh": "小麦哲伦云",
        "data": {
            "type": "irregular",
            "distance_mly": 0.197,
            "ra": 12.80,
            "dec": -73.15,
            "magnitude": 2.7,
            "diameter_kly": 7,
            "color": "#CCBBEE"
        }
    },
    {
        "name": "Milky Way Center",
        "name_zh": "银河系中心",
        "data": {
            "type": "galactic_center",
            "distance_mly": 0.026,
            "ra": 266.42,
            "dec": -29.01,
            "magnitude": -1,
            "diameter_kly": 100,
            "color": "#FFFFAA"
        }
    },
    {
        "name": "Whirlpool Galaxy",
        "name_zh": "漩涡星系",
        "data": {
            "type": "spiral",
            "distance_mly": 23,
            "ra": 202.47,
            "dec": 47.20,
            "magnitude": 8.4,
            "diameter_kly": 76,
            "color": "#AADDFF"
        }
    },
    {
        "name": "Sombrero Galaxy",
        "name_zh": "草帽星系",
        "data": {
            "type": "spiral",
            "distance_mly": 29.3,
            "ra": 189.99,
            "dec": -11.62,
            "magnitude": 8.0,
            "diameter_kly": 50,
            "color": "#FFDDAA"
        }
    },
    {
        "name": "Pinwheel Galaxy",
        "name_zh": "风车星系",
        "data": {
            "type": "spiral",
            "distance_mly": 21,
            "ra": 210.80,
            "dec": 54.35,
            "magnitude": 7.9,
            "diameter_kly": 170,
            "color": "#BBDDFF"
        }
    },
    {
        "name": "Bode's Galaxy",
        "name_zh": "波德星系",
        "data": {
            "type": "spiral",
            "distance_mly": 11.8,
            "ra": 148.97,
            "dec": 69.07,
            "magnitude": 6.9,
            "diameter_kly": 90,
            "color": "#CCDDFF"
        }
    },
    {
        "name": "Cigar Galaxy",
        "name_zh": "雪茄星系",
        "data": {
            "type": "starburst",
            "distance_mly": 11.5,
            "ra": 148.97,
            "dec": 69.68,
            "magnitude": 8.4,
            "diameter_kly": 37,
            "color": "#FFCCAA"
        }
    },
    {
        "name": "Centaurus A",
        "name_zh": "半人马座A",
        "data": {
            "type": "elliptical",
            "distance_mly": 13.7,
            "ra": 201.37,
            "dec": -43.02,
            "magnitude": 6.8,
            "diameter_kly": 60,
            "color": "#FFDDCC"
        }
    },
    {
        "name": "Sculptor Galaxy",
        "name_zh": "玉夫座星系",
        "data": {
            "type": "spiral",
            "distance_mly": 11.4,
            "ra": 15.15,
            "dec": -25.29,
            "magnitude": 7.2,
            "diameter_kly": 90,
            "color": "#CCDDEE"
        }
    }
]

# Nebula data (12 nebulae)
NEBULAE = [
    {
        "name": "Orion Nebula",
        "name_zh": "猎户座大星云",
        "data": {
            "type": "emission",
            "distance_ly": 1344,
            "ra": 83.82,
            "dec": -5.39,
            "magnitude": 4.0,
            "diameter_ly": 24,
            "color": "#FF6B9D"
        }
    },
    {
        "name": "Eagle Nebula",
        "name_zh": "鹰状星云",
        "data": {
            "type": "emission",
            "distance_ly": 7000,
            "ra": 274.70,
            "dec": -13.80,
            "magnitude": 6.0,
            "diameter_ly": 70,
            "color": "#FF8B7D"
        }
    },
    {
        "name": "Crab Nebula",
        "name_zh": "蟹状星云",
        "data": {
            "type": "supernova_remnant",
            "distance_ly": 6500,
            "ra": 83.63,
            "dec": 22.01,
            "magnitude": 8.4,
            "diameter_ly": 11,
            "color": "#FFAA66"
        }
    },
    {
        "name": "Ring Nebula",
        "name_zh": "环状星云",
        "data": {
            "type": "planetary",
            "distance_ly": 2300,
            "ra": 283.40,
            "dec": 33.03,
            "magnitude": 8.8,
            "diameter_ly": 1,
            "color": "#66DDFF"
        }
    },
    {
        "name": "Helix Nebula",
        "name_zh": "螺旋星云",
        "data": {
            "type": "planetary",
            "distance_ly": 700,
            "ra": 337.41,
            "dec": -20.84,
            "magnitude": 7.6,
            "diameter_ly": 2.5,
            "color": "#88CCFF"
        }
    },
    {
        "name": "Lagoon Nebula",
        "name_zh": "礁湖星云",
        "data": {
            "type": "emission",
            "distance_ly": 4100,
            "ra": 270.93,
            "dec": -24.38,
            "magnitude": 6.0,
            "diameter_ly": 55,
            "color": "#FF99AA"
        }
    },
    {
        "name": "Horsehead Nebula",
        "name_zh": "马头星云",
        "data": {
            "type": "dark",
            "distance_ly": 1500,
            "ra": 85.30,
            "dec": -2.46,
            "magnitude": 10.0,
            "diameter_ly": 3.5,
            "color": "#886655"
        }
    },
    {
        "name": "Eta Carinae Nebula",
        "name_zh": "船底座η星云",
        "data": {
            "type": "emission",
            "distance_ly": 7500,
            "ra": 161.26,
            "dec": -59.87,
            "magnitude": 3.0,
            "diameter_ly": 300,
            "color": "#FFAACC"
        }
    },
    {
        "name": "North America Nebula",
        "name_zh": "北美洲星云",
        "data": {
            "type": "emission",
            "distance_ly": 1600,
            "ra": 312.95,
            "dec": 44.32,
            "magnitude": 4.0,
            "diameter_ly": 50,
            "color": "#FF7788"
        }
    },
    {
        "name": "Trifid Nebula",
        "name_zh": "三叶星云",
        "data": {
            "type": "emission",
            "distance_ly": 5200,
            "ra": 270.36,
            "dec": -23.03,
            "magnitude": 6.3,
            "diameter_ly": 25,
            "color": "#FF99DD"
        }
    },
    {
        "name": "Dumbbell Nebula",
        "name_zh": "哑铃星云",
        "data": {
            "type": "planetary",
            "distance_ly": 1360,
            "ra": 299.90,
            "dec": 22.72,
            "magnitude": 7.5,
            "diameter_ly": 1.44,
            "color": "#77DDFF"
        }
    },
    {
        "name": "Veil Nebula",
        "name_zh": "面纱星云",
        "data": {
            "type": "supernova_remnant",
            "distance_ly": 2400,
            "ra": 312.92,
            "dec": 30.72,
            "magnitude": 7.0,
            "diameter_ly": 110,
            "color": "#AADDFF"
        }
    }
]


async def update_static_data():
    """Update static_data table with expanded astronomical data"""
    print("=" * 60)
    print("Updating static_data table")
    print("=" * 60)

    async for session in get_db():
        # Update constellations
        print(f"\nUpdating {len(CONSTELLATIONS)} constellations...")
        for const in CONSTELLATIONS:
            stmt = pg_insert(StaticData).values(
                category="constellation",
                name=const["name"],
                name_zh=const["name_zh"],
                data=const["data"]
            )
            stmt = stmt.on_conflict_do_update(
                index_elements=['category', 'name'],
                set_={
                    'name_zh': const["name_zh"],
                    'data': const["data"]
                }
            )
            await session.execute(stmt)
            print(f"  ✓ {const['name']} ({const['name_zh']})")

        # Update galaxies
        print(f"\nUpdating {len(GALAXIES)} galaxies...")
        for galaxy in GALAXIES:
            stmt = pg_insert(StaticData).values(
                category="galaxy",
                name=galaxy["name"],
                name_zh=galaxy["name_zh"],
                data=galaxy["data"]
            )
            stmt = stmt.on_conflict_do_update(
                index_elements=['category', 'name'],
                set_={
                    'name_zh': galaxy["name_zh"],
                    'data': galaxy["data"]
                }
            )
            await session.execute(stmt)
            print(f"  ✓ {galaxy['name']} ({galaxy['name_zh']})")

        # Insert nebulae
        print(f"\nInserting {len(NEBULAE)} nebulae...")
        for nebula in NEBULAE:
            stmt = pg_insert(StaticData).values(
                category="nebula",
                name=nebula["name"],
                name_zh=nebula["name_zh"],
                data=nebula["data"]
            )
            stmt = stmt.on_conflict_do_update(
                index_elements=['category', 'name'],
                set_={
                    'name_zh': nebula["name_zh"],
                    'data': nebula["data"]
                }
            )
            await session.execute(stmt)
            print(f"  ✓ {nebula['name']} ({nebula['name_zh']})")

        await session.commit()
        break  # Only use first session

    print("\n" + "=" * 60)
    print("✓ Static data update complete!")
    print("=" * 60)


if __name__ == "__main__":
    asyncio.run(update_static_data())