#!/usr/bin/env python3
"""
Import store_itms.csv into pos_db.store_items
Usage: python3 import_store_items.py
"""

import csv
import mysql.connector
import sys
import os

# ── Config ────────────────────────────────────────────────
CSV_FILE = os.path.expanduser('~/Downloads/store_itms.csv')

DB_CONFIG = {
    'host':     'localhost',
    'port':     3306,
    'user':     'root',
    'password': input('Enter MySQL root password: '),
    'database': 'pos_db',
}

# ── Connect ───────────────────────────────────────────────
try:
    conn = mysql.connector.connect(**DB_CONFIG)
    cursor = conn.cursor()
    print('✅ Connected to MySQL')
except mysql.connector.Error as e:
    print(f'❌ Connection failed: {e}')
    sys.exit(1)

# ── Create table ──────────────────────────────────────────
cursor.execute('DROP TABLE IF EXISTS store_items')
cursor.execute('''
    CREATE TABLE store_items (
        item_id   INT AUTO_INCREMENT PRIMARY KEY,
        itemname  VARCHAR(200),
        barcode   VARCHAR(100),
        bprice    DECIMAL(10,2),
        sspprice  DECIMAL(10,2),
        minprice  DECIMAL(10,2),
        wsprice   DECIMAL(10,2),
        retprice  DECIMAL(10,2),
        groupname VARCHAR(100),
        drawer    VARCHAR(50),
        modelname VARCHAR(100)
    )
''')
print('✅ Table store_items created')

# ── Import CSV ────────────────────────────────────────────
def clean(val):
    """Return None for empty strings and MySQL \\N nulls."""
    v = val.strip()
    return None if v in ('', r'\N', 'NULL') else v

insert_sql = '''
    INSERT INTO store_items
        (itemname, barcode, bprice, sspprice, minprice, wsprice, retprice, groupname, drawer, modelname)
    VALUES
        (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
'''

rows_inserted = 0
rows_skipped  = 0

with open(CSV_FILE, newline='', encoding='utf-8') as f:
    reader = csv.DictReader(f)
    for row in reader:
        try:
            values = (
                clean(row['itemname']),
                clean(row['barcode']),
                clean(row['bprice']),
                clean(row['sspprice']),
                clean(row['minprice']),
                clean(row['wsprice']),
                clean(row['retprice']),
                clean(row['groupname']),
                clean(row['drawer']),
                clean(row['modelname']),
            )
            cursor.execute(insert_sql, values)
            rows_inserted += 1
        except Exception as e:
            print(f'  ⚠️  Skipped row ({row.get("itemname", "?")}): {e}')
            rows_skipped += 1

conn.commit()
cursor.close()
conn.close()

print(f'\n✅ Done! {rows_inserted} rows imported, {rows_skipped} skipped.')
print('   Table: pos_db.store_items')