package com.example.myapplication6;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Locale;

public class bizimdb extends SQLiteOpenHelper {

    public static final String SHELF_MEDIA = "media";
    public static final String SHELF_READING = "reading";
    public static final String SHELF_WISHLIST = "wishlist";
    public static final String SHELF_TRAVEL = "travel";

    public bizimdb(Context context) {
        super(context, "KoleksiyonDB", null, 3);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(
                "CREATE TABLE koleksiyon (id INTEGER PRIMARY KEY AUTOINCREMENT, shelf TEXT NOT NULL DEFAULT '"
                        + SHELF_MEDIA
                        + "', baslik TEXT, tur TEXT, puan TEXT, yorum TEXT, tarih TEXT)");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        if (oldVersion < 3) {
            try {
                db.execSQL("ALTER TABLE koleksiyon ADD COLUMN shelf TEXT DEFAULT '" + SHELF_MEDIA + "'");
            } catch (Exception ignored) {
                db.execSQL("DROP TABLE IF EXISTS koleksiyon");
                onCreate(db);
            }
        }
    }

    public void ekle(String shelf, String baslik, String typeKey, String puan, String yorum) {
        SQLiteDatabase db = this.getWritableDatabase();
        String tarih = new SimpleDateFormat("dd/MM/yyyy", Locale.getDefault()).format(new Date());

        ContentValues cv = new ContentValues();
        cv.put("shelf", shelf != null ? shelf : SHELF_MEDIA);
        cv.put("baslik", baslik);
        cv.put("tur", typeKey);
        cv.put("puan", puan);
        cv.put("yorum", yorum != null ? yorum : "");
        cv.put("tarih", tarih);

        db.insert("koleksiyon", null, cv);
        db.close();
    }

    public void guncelle(long id, String baslik, String typeKey, String puan, String yorum) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues cv = new ContentValues();
        cv.put("baslik", baslik);
        cv.put("tur", typeKey);
        cv.put("puan", puan);
        cv.put("yorum", yorum != null ? yorum : "");
        db.update("koleksiyon", cv, "id = ?", new String[] {String.valueOf(id)});
        db.close();
    }

    public Item getItem(long id) {
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor c = db.rawQuery("SELECT * FROM koleksiyon WHERE id = ?", new String[] {String.valueOf(id)});
        Item out = null;
        if (c.moveToFirst()) {
            out = rowFromCursor(c);
        }
        c.close();
        return out;
    }

    public List<Item> getItems(String shelf) {
        SQLiteDatabase db = this.getReadableDatabase();
        String target = shelf != null ? shelf : SHELF_MEDIA;
        Cursor c =
                db.rawQuery(
                        "SELECT * FROM koleksiyon WHERE IFNULL(shelf, ?) = ? ORDER BY id DESC",
                        new String[] {SHELF_MEDIA, target});
        List<Item> list = new ArrayList<>();
        while (c.moveToNext()) {
            list.add(rowFromCursor(c));
        }
        c.close();
        return list;
    }

    private static Item rowFromCursor(Cursor c) {
        long id = c.getLong(c.getColumnIndexOrThrow("id"));
        int shelfIdx = c.getColumnIndex("shelf");
        String shelf = shelfIdx >= 0 ? c.getString(shelfIdx) : SHELF_MEDIA;
        if (shelf == null || shelf.isEmpty()) {
            shelf = SHELF_MEDIA;
        }
        String baslik = c.getString(c.getColumnIndexOrThrow("baslik"));
        String tur = c.getString(c.getColumnIndexOrThrow("tur"));
        String puan = c.getString(c.getColumnIndexOrThrow("puan"));
        String yorum = c.getString(c.getColumnIndexOrThrow("yorum"));
        String tarih = c.getString(c.getColumnIndexOrThrow("tarih"));
        return new Item(id, shelf, baslik, tur, puan, yorum, tarih);
    }

    public String verileriListele(Context context, String shelf) {
        SQLiteDatabase db = this.getReadableDatabase();
        String target = shelf != null ? shelf : SHELF_MEDIA;
        Cursor c =
                db.rawQuery(
                        "SELECT * FROM koleksiyon WHERE IFNULL(shelf, ?) = ? ORDER BY id DESC",
                        new String[] {SHELF_MEDIA, target});
        StringBuilder sb = new StringBuilder();

        while (c.moveToNext()) {
            String b = c.getString(c.getColumnIndexOrThrow("baslik"));
            String t = normalizeStoredType(c.getString(c.getColumnIndexOrThrow("tur")));
            String p = c.getString(c.getColumnIndexOrThrow("puan"));
            String y = c.getString(c.getColumnIndexOrThrow("yorum"));
            String trh = c.getString(c.getColumnIndexOrThrow("tarih"));

            String icon = iconForTypeKey(t);
            String typeLabel = labelForTypeKey(context, t);

            sb.append(icon)
                    .append(" ")
                    .append(b != null ? b.toUpperCase(Locale.getDefault()) : "")
                    .append("\n")
                    .append("📅 ")
                    .append(trh)
                    .append(" | ⭐ ")
                    .append(p)
                    .append("/10\n")
                    .append(context.getString(R.string.label_type))
                    .append(": ")
                    .append(typeLabel)
                    .append("\n")
                    .append("💬 ")
                    .append(y != null ? y : "")
                    .append("\n")
                    .append("────────────────────\n\n");
        }
        c.close();
        return sb.toString();
    }

    /** Maps legacy labels and stored keys to a canonical type key for the current shelf spinners. */
    public static String normalizeStoredType(String raw) {
        if (raw == null || raw.isEmpty()) {
            return "website";
        }
        String r = raw.trim();

        if (isCanonicalKey(r)) {
            return r;
        }

        if (r.equalsIgnoreCase("Oyun") || r.equalsIgnoreCase("Game") || r.equalsIgnoreCase("Spiel")) {
            return "game";
        }
        if (r.equalsIgnoreCase("Websitesi") || r.equalsIgnoreCase("Website")) {
            return "website";
        }
        if (r.equalsIgnoreCase("YouTube")) {
            return "youtube";
        }

        return r;
    }

    private static boolean isCanonicalKey(String r) {
        switch (r) {
            case "game":
            case "website":
            case "youtube":
            case "book":
            case "article":
            case "audiobook":
            case "magazine":
            case "product":
            case "gift":
            case "experience":
            case "subscription":
            case "city":
            case "restaurant":
            case "hotel":
            case "activity":
                return true;
            default:
                return false;
        }
    }

    public static String entryEmoji(String rawTur) {
        return iconForTypeKey(normalizeStoredType(rawTur));
    }

    public static String entryTypeLabel(Context context, String rawTur) {
        return labelForTypeKey(context, normalizeStoredType(rawTur));
    }

    private static String iconForTypeKey(String key) {
        switch (key) {
            case "game":
                return "🎮";
            case "youtube":
                return "📺";
            case "book":
                return "📖";
            case "article":
                return "📄";
            case "audiobook":
                return "🎧";
            case "magazine":
                return "📰";
            case "product":
                return "🛍️";
            case "gift":
                return "🎁";
            case "experience":
                return "✨";
            case "subscription":
                return "💳";
            case "city":
                return "🏙️";
            case "restaurant":
                return "🍽️";
            case "hotel":
                return "🏨";
            case "activity":
                return "🧭";
            case "website":
            default:
                return "🌐";
        }
    }

    private static String labelForTypeKey(Context context, String key) {
        String k = normalizeStoredType(key);
        switch (k) {
            case "game":
                return context.getString(R.string.type_game);
            case "youtube":
                return context.getString(R.string.type_youtube);
            case "website":
                return context.getString(R.string.type_website);
            case "book":
                return context.getString(R.string.type_book);
            case "article":
                return context.getString(R.string.type_article);
            case "audiobook":
                return context.getString(R.string.type_audiobook);
            case "magazine":
                return context.getString(R.string.type_magazine);
            case "product":
                return context.getString(R.string.type_product);
            case "gift":
                return context.getString(R.string.type_gift);
            case "experience":
                return context.getString(R.string.type_experience);
            case "subscription":
                return context.getString(R.string.type_subscription);
            case "city":
                return context.getString(R.string.type_city);
            case "restaurant":
                return context.getString(R.string.type_restaurant);
            case "hotel":
                return context.getString(R.string.type_hotel);
            case "activity":
                return context.getString(R.string.type_activity);
            default:
                return context.getString(R.string.type_other);
        }
    }

    public void tumunuSil(String shelf) {
        SQLiteDatabase db = this.getWritableDatabase();
        String target = shelf != null ? shelf : SHELF_MEDIA;
        db.delete("koleksiyon", "IFNULL(shelf, ?) = ?", new String[] {SHELF_MEDIA, target});
        db.close();
    }

    public static final class Item {
        public final long id;
        public final String shelf;
        public final String baslik;
        public final String tur;
        public final String puan;
        public final String yorum;
        public final String tarih;

        public Item(
                long id,
                String shelf,
                String baslik,
                String tur,
                String puan,
                String yorum,
                String tarih) {
            this.id = id;
            this.shelf = shelf;
            this.baslik = baslik;
            this.tur = tur;
            this.puan = puan;
            this.yorum = yorum;
            this.tarih = tarih;
        }
    }
}
