package storage

import (
	"database/sql"
	"encoding/json"
	"fmt"
	"log"
	"os"
	"path/filepath"
	"sync"
	"time"

	_ "modernc.org/sqlite"
)

type SQLiteDB struct {
	db *sql.DB
	mu sync.RWMutex
}

func NewSQLiteDB(path string) (*SQLiteDB, error) {
	// Ensure directory exists
	dir := filepath.Dir(path)
	if err := os.MkdirAll(dir, 0750); err != nil {
		return nil, err
	}

	db, err := sql.Open("sqlite", path)
	if err != nil {
		return nil, err
	}

	s := &SQLiteDB{db: db}
	if err := s.init(); err != nil {
		if closeErr := db.Close(); closeErr != nil {
			return nil, fmt.Errorf("init error: %v, close error: %v", err, closeErr)
		}
		return nil, err
	}

	return s, nil
}

func (s *SQLiteDB) init() error {
	s.mu.Lock()
	defer s.mu.Unlock()

	// Enable WAL mode
	_, err := s.db.Exec("PRAGMA journal_mode=WAL;")
	if err != nil {
		return err
	}

	queries := []string{
		`CREATE TABLE IF NOT EXISTS categories (
			id TEXT PRIMARY KEY,
			name TEXT,
			"order" INTEGER
		);`,
		`CREATE TABLE IF NOT EXISTS feeds (
			id TEXT PRIMARY KEY,
			title TEXT,
			categoryId TEXT,
			"order" INTEGER,
			enabled INTEGER,
			fetchInterval INTEGER,
			FOREIGN KEY(categoryId) REFERENCES categories(id)
		);`,
		`CREATE TABLE IF NOT EXISTS articles (
			id TEXT PRIMARY KEY,
			feedId TEXT,
			title TEXT,
			link TEXT,
			description TEXT,
			content TEXT,
			author TEXT,
			pubDate INTEGER,
			read INTEGER,
			saved INTEGER,
			imageUrl TEXT,
			readAt INTEGER,
			FOREIGN KEY(feedId) REFERENCES feeds(id)
		);`,
		`CREATE INDEX IF NOT EXISTS idx_articles_pubDate ON articles(pubDate);`,
		`CREATE INDEX IF NOT EXISTS idx_articles_readAt ON articles(readAt);`,
		`CREATE TABLE IF NOT EXISTS settings (
			key TEXT PRIMARY KEY,
			value TEXT
		);`,
		`CREATE TABLE IF NOT EXISTS caches (
			key TEXT PRIMARY KEY,
			value BLOB,
			expiresAt INTEGER
		);`,
		`CREATE INDEX IF NOT EXISTS idx_caches_expiresAt ON caches(expiresAt);`,
	}

	for _, q := range queries {
		if _, err := s.db.Exec(q); err != nil {
			return err
		}
	}

	return nil
}

func (s *SQLiteDB) Close() error {
	return s.db.Close()
}

// Database Stats and Operations

type DBStats struct {
	Size       int64  `json:"size"`
	Path       string `json:"path"`
	Articles   int    `json:"articles"`
	Feeds      int    `json:"feeds"`
	WALEnabled bool   `json:"walEnabled"`
}

func (s *SQLiteDB) GetStats() (DBStats, error) {
	var stats DBStats

	// Get file size
	var path string
	err := s.db.QueryRow("PRAGMA database_list").Scan(interface{}(nil), interface{}(nil), &path)
	if err != nil {
		// Fallback if PRAGMA fails
		return stats, err
	}
	stats.Path = path

	fi, err := os.Stat(path)
	if err == nil {
		stats.Size = fi.Size()
	}

	// Count articles
	if err := s.db.QueryRow("SELECT COUNT(*) FROM articles").Scan(&stats.Articles); err != nil {
		log.Printf("Error counting articles: %v", err)
	}

	// Count feeds
	if err := s.db.QueryRow("SELECT COUNT(*) FROM feeds").Scan(&stats.Feeds); err != nil {
		log.Printf("Error counting feeds: %v", err)
	}

	// Check WAL
	var mode string
	if err := s.db.QueryRow("PRAGMA journal_mode").Scan(&mode); err != nil {
		log.Printf("Error checking journal mode: %v", err)
	}
	stats.WALEnabled = mode == "wal"

	return stats, nil
}

func (s *SQLiteDB) Vacuum() error {
	_, err := s.db.Exec("VACUUM")
	return err
}

func (s *SQLiteDB) IntegrityCheck() (string, error) {
	var res string
	err := s.db.QueryRow("PRAGMA integrity_check").Scan(&res)
	return res, err
}

// Data Operations

func (s *SQLiteDB) SaveSettings(settingsJSON string) error {
	_, err := s.db.Exec("INSERT OR REPLACE INTO settings (key, value) VALUES ('main', ?)", settingsJSON)
	return err
}

func (s *SQLiteDB) GetSettings() (string, error) {
	var val string
	err := s.db.QueryRow("SELECT value FROM settings WHERE key = 'main'").Scan(&val)
	if err == sql.ErrNoRows {
		return "{}", nil
	}
	return val, err
}

func (s *SQLiteDB) SaveCategories(catsJSON string) error {
	var cats []struct {
		ID    string `json:"id"`
		Name  string `json:"name"`
		Order int    `json:"order"`
	}
	if err := json.Unmarshal([]byte(catsJSON), &cats); err != nil {
		return err
	}

	tx, err := s.db.Begin()
	if err != nil {
		return err
	}
	defer tx.Rollback()

	for _, c := range cats {
		_, err := tx.Exec("INSERT OR REPLACE INTO categories (id, name, \"order\") VALUES (?, ?, ?)", c.ID, c.Name, c.Order)
		if err != nil {
			return err
		}
	}
	return tx.Commit()
}

func (s *SQLiteDB) GetCategories() (string, error) {
	rows, err := s.db.Query("SELECT id, name, \"order\" FROM categories ORDER BY \"order\" ASC")
	if err != nil {
		return "[]", err
	}
	defer rows.Close()

	var cats []map[string]any = []map[string]any{}
	for rows.Next() {
		var id, name string
		var order int
		if err := rows.Scan(&id, &name, &order); err != nil {
			return "[]", err
		}
		cats = append(cats, map[string]any{"id": id, "name": name, "order": order})
	}

	b, _ := json.Marshal(cats)
	return string(b), nil
}

func (s *SQLiteDB) SaveFeeds(feedsJSON string) error {
	var feeds []struct {
		ID            string `json:"id"`
		Title         string `json:"title"`
		CategoryID    string `json:"categoryId"`
		Order         int    `json:"order"`
		Enabled       bool   `json:"enabled"`
		FetchInterval int    `json:"fetchInterval"`
	}
	if err := json.Unmarshal([]byte(feedsJSON), &feeds); err != nil {
		return err
	}

	tx, err := s.db.Begin()
	if err != nil {
		return err
	}
	defer tx.Rollback()

	for _, f := range feeds {
		enabled := 0
		if f.Enabled {
			enabled = 1
		}
		_, err := tx.Exec("INSERT OR REPLACE INTO feeds (id, title, categoryId, \"order\", enabled, fetchInterval) VALUES (?, ?, ?, ?, ?, ?)",
			f.ID, f.Title, f.CategoryID, f.Order, enabled, f.FetchInterval)
		if err != nil {
			return err
		}
	}
	return tx.Commit()
}

func (s *SQLiteDB) GetFeeds() (string, error) {
	rows, err := s.db.Query("SELECT id, title, categoryId, \"order\", enabled, fetchInterval FROM feeds ORDER BY \"order\" ASC")
	if err != nil {
		return "[]", err
	}
	defer rows.Close()

	var feeds []map[string]any = []map[string]any{}
	for rows.Next() {
		var id, title, categoryId string
		var order, enabled, fetchInterval int
		if err := rows.Scan(&id, &title, &categoryId, &order, &enabled, &fetchInterval); err != nil {
			return "[]", err
		}
		feeds = append(feeds, map[string]any{
			"id":            id,
			"title":         title,
			"categoryId":    categoryId,
			"order":         order,
			"enabled":       enabled == 1,
			"fetchInterval": fetchInterval,
		})
	}

	b, _ := json.Marshal(feeds)
	return string(b), nil
}

func (s *SQLiteDB) SaveArticles(articlesJSON string) error {
	var articles []map[string]any
	if err := json.Unmarshal([]byte(articlesJSON), &articles); err != nil {
		return err
	}

	tx, err := s.db.Begin()
	if err != nil {
		return err
	}
	defer tx.Rollback()

	for _, a := range articles {
		read := 0
		if r, ok := a["read"].(bool); ok && r {
			read = 1
		}
		saved := 0
		if sa, ok := a["saved"].(bool); ok && sa {
			saved = 1
		}

		_, err := tx.Exec(`INSERT OR REPLACE INTO articles 
			(id, feedId, title, link, description, content, author, pubDate, read, saved, imageUrl, readAt) 
			VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
			a["id"], a["feedId"], a["title"], a["link"], a["description"], a["content"], a["author"], a["pubDate"], read, saved, a["imageUrl"], a["readAt"])
		if err != nil {
			return err
		}
	}
	return tx.Commit()
}

func (s *SQLiteDB) GetArticles(feedId string, offset, limit int, categoryId string) (string, error) {
	var rows *sql.Rows
	var err error
	if feedId != "" {
		rows, err = s.db.Query("SELECT id, feedId, title, link, description, content, author, pubDate, read, saved, imageUrl, readAt FROM articles WHERE feedId = ? ORDER BY pubDate DESC LIMIT ? OFFSET ?", feedId, limit, offset)
	} else if categoryId != "" {
		rows, err = s.db.Query(`
			SELECT a.id, a.feedId, a.title, a.link, a.description, a.content, a.author, a.pubDate, a.read, a.saved, a.imageUrl, a.readAt 
			FROM articles a
			JOIN feeds f ON a.feedId = f.id
			WHERE f.categoryId = ? 
			ORDER BY a.pubDate DESC LIMIT ? OFFSET ?`, categoryId, limit, offset)
	} else {
		rows, err = s.db.Query("SELECT id, feedId, title, link, description, content, author, pubDate, read, saved, imageUrl, readAt FROM articles ORDER BY pubDate DESC LIMIT ? OFFSET ?", limit, offset)
	}

	if err != nil {
		return "[]", err
	}
	defer rows.Close()

	var articles []map[string]any = []map[string]any{}
	for rows.Next() {
		var id, feedId, title, link, description, content, author, imageUrl sql.NullString
		var pubDate int64
		var read, saved int
		var readAt sql.NullInt64

		err := rows.Scan(&id, &feedId, &title, &link, &description, &content, &author, &pubDate, &read, &saved, &imageUrl, &readAt)
		if err != nil {
			return "[]", err
		}

		a := map[string]any{
			"id":          id.String,
			"feedId":      feedId.String,
			"title":       title.String,
			"link":        link.String,
			"description": description.String,
			"content":     content.String,
			"author":      author.String,
			"pubDate":     pubDate,
			"read":        read == 1,
			"saved":       saved == 1,
			"imageUrl":    imageUrl.String,
		}
		if readAt.Valid {
			a["readAt"] = readAt.Int64
		}
		articles = append(articles, a)
	}

	b, _ := json.Marshal(articles)
	return string(b), nil
}

func (s *SQLiteDB) SearchArticles(query string, limit int) (string, error) {
	q := "%" + query + "%"
	rows, err := s.db.Query(`SELECT id, feedId, title, link, description, content, author, pubDate, read, saved, imageUrl, readAt 
		FROM articles 
		WHERE title LIKE ? OR description LIKE ? OR content LIKE ? 
		ORDER BY pubDate DESC LIMIT ?`, q, q, q, limit)

	if err != nil {
		return "[]", err
	}
	defer rows.Close()

	var articles []map[string]any = []map[string]any{}
	for rows.Next() {
		var id, feedId, title, link, description, content, author, imageUrl sql.NullString
		var pubDate int64
		var read, saved int
		var readAt sql.NullInt64

		err := rows.Scan(&id, &feedId, &title, &link, &description, &content, &author, &pubDate, &read, &saved, &imageUrl, &readAt)
		if err != nil {
			return "[]", err
		}

		a := map[string]any{
			"id":          id.String,
			"feedId":      feedId.String,
			"title":       title.String,
			"link":        link.String,
			"description": description.String,
			"content":     content.String,
			"author":      author.String,
			"pubDate":     pubDate,
			"read":        read == 1,
			"saved":       saved == 1,
			"imageUrl":    imageUrl.String,
		}
		if readAt.Valid {
			a["readAt"] = readAt.Int64
		}
		articles = append(articles, a)
	}

	b, _ := json.Marshal(articles)
	return string(b), nil
}

func (s *SQLiteDB) UpdateArticle(articleJSON string) error {
	var a map[string]any
	if err := json.Unmarshal([]byte(articleJSON), &a); err != nil {
		return err
	}

	read := 0
	if r, ok := a["read"].(bool); ok && r {
		read = 1
	}
	saved := 0
	if sa, ok := a["saved"].(bool); ok && sa {
		saved = 1
	}

	_, err := s.db.Exec(`UPDATE articles SET read = ?, saved = ?, readAt = ?, content = ? WHERE id = ?`,
		read, saved, a["readAt"], a["content"], a["id"])
	return err
}

func (s *SQLiteDB) MarkAsRead(id string) error {
	now := time.Now().UnixMilli()
	_, err := s.db.Exec(`UPDATE articles SET read = 1, readAt = ? WHERE id = ? AND read = 0`, now, id)
	return err
}

func (s *SQLiteDB) DeleteFeed(feedId string) error {
	tx, err := s.db.Begin()
	if err != nil {
		return err
	}
	defer tx.Rollback()

	if _, err := tx.Exec("DELETE FROM articles WHERE feedId = ?", feedId); err != nil {
		return err
	}
	if _, err := tx.Exec("DELETE FROM feeds WHERE id = ?", feedId); err != nil {
		return err
	}

	return tx.Commit()
}

func (s *SQLiteDB) PurgeOldContent(days int) (int64, error) {
	cutoff := time.Now().AddDate(0, 0, -days).UnixMilli()
	res, err := s.db.Exec("UPDATE articles SET content = NULL WHERE saved = 0 AND pubDate < ?", cutoff)
	if err != nil {
		return 0, err
	}
	return res.RowsAffected()
}

func (s *SQLiteDB) ClearAll() error {
	queries := []string{
		"DELETE FROM articles",
		"DELETE FROM feeds",
		"DELETE FROM categories",
		"DELETE FROM settings",
	}
	for _, q := range queries {
		if _, err := s.db.Exec(q); err != nil {
			return err
		}
	}
	return nil
}

func (s *SQLiteDB) GetReadingHistory(days int) (string, error) {
	cutoff := time.Now().AddDate(0, 0, -days).UnixMilli()
	rows, err := s.db.Query(`
		SELECT strftime('%Y-%m-%d', datetime(readAt/1000, 'unixepoch', 'localtime')) as date, COUNT(*) as count 
		FROM articles 
		WHERE read = 1 AND readAt > ? 
		GROUP BY date 
		ORDER BY date DESC`, cutoff)
	if err != nil {
		return "[]", err
	}
	defer rows.Close()

	var history []map[string]any = []map[string]any{}
	for rows.Next() {
		var date string
		var count int
		if err := rows.Scan(&date, &count); err != nil {
			continue
		}
		// Convert local date string back to local midnight timestamp for frontend
		t, err := time.ParseInLocation("2006-01-02", date, time.Local)
		if err != nil {
			continue
		}
		history = append(history, map[string]any{
			"date":  t.UnixMilli(),
			"count": count,
		})
	}

	b, _ := json.Marshal(history)
	return string(b), nil
}

func (s *SQLiteDB) SetCache(key string, value []byte, ttl time.Duration) error {
	expiresAt := time.Now().Add(ttl).UnixMilli()
	_, err := s.db.Exec("INSERT OR REPLACE INTO caches (key, value, expiresAt) VALUES (?, ?, ?)", key, value, expiresAt)
	return err
}

func (s *SQLiteDB) GetCache(key string) ([]byte, error) {
	var value []byte
	var expiresAt int64
	err := s.db.QueryRow("SELECT value, expiresAt FROM caches WHERE key = ?", key).Scan(&value, &expiresAt)
	if err == sql.ErrNoRows {
		return nil, nil
	}
	if err != nil {
		return nil, err
	}
	if time.Now().UnixMilli() > expiresAt {
		_, _ = s.db.Exec("DELETE FROM caches WHERE key = ?", key)
		return nil, nil
	}
	return value, nil
}

func (s *SQLiteDB) PurgeExpiredCaches() error {
	now := time.Now().UnixMilli()
	_, err := s.db.Exec("DELETE FROM caches WHERE expiresAt < ?", now)
	return err
}