import UIKit
import UniformTypeIdentifiers
import GRDB

// MARK: - Database Records
struct UrlRecord: Codable, FetchableRecord, PersistableRecord {
    static let databaseTableName = "urls"

    var id: String
    var url: String
    var created_at: String
    var updated_at: String
    var deleted_at: String?
}

struct TagRecord: Codable, FetchableRecord, PersistableRecord {
    static let databaseTableName = "tags"

    var id: Int64?
    var name: String
    var frequency: Int
    var lastUsed: String
    var frecencyScore: Double
    var createdAt: String
    var updatedAt: String
}

struct UrlTagRecord: Codable, FetchableRecord, PersistableRecord {
    static let databaseTableName = "url_tags"

    var url_id: String
    var tag_id: Int64
    var created_at: String
}

// MARK: - View Models (for UI)
struct TagStats {
    var name: String
    var frequency: Int
    var lastUsed: String
    var frecencyScore: Double
}

struct SavedUrl {
    var id: String
    var url: String
    var tags: [String]
    var saved_at: String
}

// MARK: - Database Manager
class DatabaseManager {
    static let shared = DatabaseManager()

    private var dbQueue: DatabaseQueue?

    private init() {
        setupDatabase()
    }

    private func getAppGroupContainerPath() -> URL? {
        return FileManager.default.containerURL(forSecurityApplicationGroupIdentifier: "group.com.dietrich.peek-mobile")
    }

    private func setupDatabase() {
        guard let containerURL = getAppGroupContainerPath() else {
            print("[DB] Failed to get App Group container")
            return
        }

        let dbPath = containerURL.appendingPathComponent("peek.db")
        print("[DB] Opening database at: \(dbPath.path)")

        do {
            var config = Configuration()
            config.prepareDatabase { db in
                // Enable WAL mode for concurrent access
                try db.execute(sql: "PRAGMA journal_mode=WAL")
            }

            dbQueue = try DatabaseQueue(path: dbPath.path, configuration: config)
            try createTables()
            print("[DB] Database initialized successfully")
        } catch {
            print("[DB] Failed to setup database: \(error)")
        }
    }

    private func createTables() throws {
        try dbQueue?.write { db in
            try db.execute(sql: """
                CREATE TABLE IF NOT EXISTS urls (
                    id TEXT PRIMARY KEY,
                    url TEXT NOT NULL UNIQUE,
                    created_at TEXT NOT NULL,
                    updated_at TEXT NOT NULL,
                    deleted_at TEXT
                );

                CREATE TABLE IF NOT EXISTS tags (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    name TEXT NOT NULL UNIQUE,
                    frequency INTEGER NOT NULL DEFAULT 0,
                    lastUsed TEXT NOT NULL,
                    frecencyScore REAL NOT NULL DEFAULT 0.0,
                    createdAt TEXT NOT NULL,
                    updatedAt TEXT NOT NULL
                );

                CREATE TABLE IF NOT EXISTS url_tags (
                    url_id TEXT NOT NULL,
                    tag_id INTEGER NOT NULL,
                    created_at TEXT NOT NULL,
                    PRIMARY KEY (url_id, tag_id),
                    FOREIGN KEY (url_id) REFERENCES urls(id) ON DELETE CASCADE,
                    FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
                );

                CREATE INDEX IF NOT EXISTS idx_urls_url ON urls(url);
                CREATE INDEX IF NOT EXISTS idx_urls_deleted ON urls(deleted_at);
                CREATE INDEX IF NOT EXISTS idx_tags_name ON tags(name);
                CREATE INDEX IF NOT EXISTS idx_tags_frecency ON tags(frecencyScore DESC);
            """)
        }
    }

    func loadTags() -> [TagStats] {
        do {
            return try dbQueue?.read { db in
                let records = try TagRecord.order(Column("frecencyScore").desc).fetchAll(db)
                return records.map { TagStats(name: $0.name, frequency: $0.frequency, lastUsed: $0.lastUsed, frecencyScore: $0.frecencyScore) }
            } ?? []
        } catch {
            print("[DB] Failed to load tags: \(error)")
            return []
        }
    }

    func findExistingUrl(_ url: String) -> SavedUrl? {
        do {
            return try dbQueue?.read { db in
                guard let record = try UrlRecord
                    .filter(Column("url") == url && Column("deleted_at") == nil)
                    .fetchOne(db) else {
                    return nil
                }

                // Get tags for this URL
                let tagNames = try String.fetchAll(db, sql: """
                    SELECT t.name FROM tags t
                    JOIN url_tags ut ON t.id = ut.tag_id
                    WHERE ut.url_id = ?
                    ORDER BY t.name
                """, arguments: [record.id])

                return SavedUrl(id: record.id, url: record.url, tags: tagNames, saved_at: record.created_at)
            }
        } catch {
            print("[DB] Failed to find URL: \(error)")
            return nil
        }
    }

    func saveUrl(url: String, tags: [String], existingId: String?, existingSavedAt: String?) {
        let now = ISO8601DateFormatter.shared.string(from: Date())

        do {
            try dbQueue?.write { db in
                let urlId: String

                if let existingId = existingId {
                    // Update existing URL
                    try db.execute(sql: "UPDATE urls SET updated_at = ? WHERE id = ?", arguments: [now, existingId])

                    // Remove old tag associations
                    try db.execute(sql: "DELETE FROM url_tags WHERE url_id = ?", arguments: [existingId])

                    urlId = existingId
                    print("[DB] Updated existing URL: \(existingId)")
                } else {
                    // Insert new URL
                    let newId = UUID().uuidString
                    let record = UrlRecord(id: newId, url: url, created_at: now, updated_at: now, deleted_at: nil)
                    try record.insert(db)
                    urlId = newId
                    print("[DB] Inserted new URL: \(newId)")
                }

                // Add tags
                for tagName in tags {
                    // Get or create tag
                    var tagId: Int64

                    if let existingTag = try TagRecord.filter(Column("name") == tagName).fetchOne(db) {
                        // Update existing tag
                        let newFrequency = existingTag.frequency + 1
                        let frecency = calculateFrecency(frequency: newFrequency, lastUsed: now)

                        try db.execute(sql: """
                            UPDATE tags SET frequency = ?, lastUsed = ?, frecencyScore = ?, updatedAt = ?
                            WHERE id = ?
                        """, arguments: [newFrequency, now, frecency, now, existingTag.id!])

                        tagId = existingTag.id!
                    } else {
                        // Create new tag
                        let frecency = calculateFrecency(frequency: 1, lastUsed: now)
                        var newTag = TagRecord(id: nil, name: tagName, frequency: 1, lastUsed: now, frecencyScore: frecency, createdAt: now, updatedAt: now)
                        try newTag.insert(db)
                        tagId = db.lastInsertedRowID
                    }

                    // Create URL-tag association
                    let urlTag = UrlTagRecord(url_id: urlId, tag_id: tagId, created_at: now)
                    try urlTag.insert(db, onConflict: .ignore)
                }

                print("[DB] Saved URL with \(tags.count) tags")
            }
        } catch {
            print("[DB] Failed to save URL: \(error)")
        }
    }

    private func calculateFrecency(frequency: Int, lastUsed: String) -> Double {
        guard let lastUsedDate = ISO8601DateFormatter.shared.date(from: lastUsed) else {
            return Double(frequency) * 10.0
        }

        let daysSinceUse = Date().timeIntervalSince(lastUsedDate) / 86400.0
        let decayFactor = 1.0 / (1.0 + daysSinceUse / 7.0)

        return Double(frequency) * 10.0 * decayFactor
    }
}

// MARK: - ISO8601 Formatter Extension
extension ISO8601DateFormatter {
    static let shared: ISO8601DateFormatter = {
        let formatter = ISO8601DateFormatter()
        formatter.formatOptions = [.withInternetDateTime, .withFractionalSeconds]
        return formatter
    }()
}

// MARK: - ShareViewController
class ShareViewController: UIViewController {
    var sharedURL: String?
    var selectedTags: Set<String> = []
    var availableTags: [TagStats] = []
    var existingSavedUrl: SavedUrl?

    let urlLabel = UILabel()
    let statusLabel = UILabel()
    let tagsCollectionView: UICollectionView = {
        let layout = UICollectionViewFlowLayout()
        layout.estimatedItemSize = UICollectionViewFlowLayout.automaticSize
        layout.minimumInteritemSpacing = 8
        layout.minimumLineSpacing = 8
        return UICollectionView(frame: .zero, collectionViewLayout: layout)
    }()
    let newTagTextField = UITextField()
    let saveButton = UIButton(type: .system)
    let cancelButton = UIButton(type: .system)

    override func viewDidLoad() {
        super.viewDidLoad()

        view.backgroundColor = .systemBackground

        setupUI()
        loadSharedURL()
        loadTags()
    }

    func setupUI() {
        // URL Label
        urlLabel.font = .systemFont(ofSize: 14)
        urlLabel.numberOfLines = 2
        urlLabel.textColor = .secondaryLabel
        urlLabel.translatesAutoresizingMaskIntoConstraints = false
        view.addSubview(urlLabel)

        // Status Label
        statusLabel.font = .systemFont(ofSize: 13)
        statusLabel.numberOfLines = 0
        statusLabel.textColor = .systemBlue
        statusLabel.translatesAutoresizingMaskIntoConstraints = false
        statusLabel.isHidden = true
        view.addSubview(statusLabel)

        // Tags Collection View
        tagsCollectionView.backgroundColor = .systemBackground
        tagsCollectionView.delegate = self
        tagsCollectionView.dataSource = self
        tagsCollectionView.register(TagCell.self, forCellWithReuseIdentifier: "TagCell")
        tagsCollectionView.translatesAutoresizingMaskIntoConstraints = false
        view.addSubview(tagsCollectionView)

        // New Tag TextField
        newTagTextField.placeholder = "Add new tag..."
        newTagTextField.borderStyle = .roundedRect
        newTagTextField.translatesAutoresizingMaskIntoConstraints = false
        newTagTextField.delegate = self
        view.addSubview(newTagTextField)

        // Buttons
        saveButton.setTitle("Save", for: .normal)
        saveButton.titleLabel?.font = .boldSystemFont(ofSize: 16)
        saveButton.addTarget(self, action: #selector(savePressed), for: .touchUpInside)
        saveButton.translatesAutoresizingMaskIntoConstraints = false
        view.addSubview(saveButton)

        cancelButton.setTitle("Cancel", for: .normal)
        cancelButton.addTarget(self, action: #selector(cancelPressed), for: .touchUpInside)
        cancelButton.translatesAutoresizingMaskIntoConstraints = false
        view.addSubview(cancelButton)

        // Layout
        NSLayoutConstraint.activate([
            urlLabel.topAnchor.constraint(equalTo: view.safeAreaLayoutGuide.topAnchor, constant: 16),
            urlLabel.leadingAnchor.constraint(equalTo: view.leadingAnchor, constant: 16),
            urlLabel.trailingAnchor.constraint(equalTo: view.trailingAnchor, constant: -16),

            statusLabel.topAnchor.constraint(equalTo: urlLabel.bottomAnchor, constant: 8),
            statusLabel.leadingAnchor.constraint(equalTo: view.leadingAnchor, constant: 16),
            statusLabel.trailingAnchor.constraint(equalTo: view.trailingAnchor, constant: -16),

            tagsCollectionView.topAnchor.constraint(equalTo: statusLabel.bottomAnchor, constant: 8),
            tagsCollectionView.leadingAnchor.constraint(equalTo: view.leadingAnchor, constant: 16),
            tagsCollectionView.trailingAnchor.constraint(equalTo: view.trailingAnchor, constant: -16),
            tagsCollectionView.heightAnchor.constraint(equalToConstant: 200),

            newTagTextField.topAnchor.constraint(equalTo: tagsCollectionView.bottomAnchor, constant: 16),
            newTagTextField.leadingAnchor.constraint(equalTo: view.leadingAnchor, constant: 16),
            newTagTextField.trailingAnchor.constraint(equalTo: view.trailingAnchor, constant: -16),

            cancelButton.leadingAnchor.constraint(equalTo: view.leadingAnchor, constant: 16),
            cancelButton.bottomAnchor.constraint(equalTo: view.safeAreaLayoutGuide.bottomAnchor, constant: -16),

            saveButton.trailingAnchor.constraint(equalTo: view.trailingAnchor, constant: -16),
            saveButton.bottomAnchor.constraint(equalTo: view.safeAreaLayoutGuide.bottomAnchor, constant: -16)
        ])
    }

    func loadSharedURL() {
        guard let extensionItem = extensionContext?.inputItems.first as? NSExtensionItem,
              let itemProviders = extensionItem.attachments else {
            return
        }

        for provider in itemProviders {
            if provider.canLoadObject(ofClass: URL.self) {
                provider.loadObject(ofClass: URL.self) { [weak self] (url, error) in
                    if let url = url {
                        DispatchQueue.main.async {
                            self?.sharedURL = url.absoluteString
                            self?.urlLabel.text = url.absoluteString
                            self?.checkIfURLExists()
                        }
                    }
                }
                return
            }
        }
    }

    func checkIfURLExists() {
        guard let url = sharedURL else { return }

        if let existing = DatabaseManager.shared.findExistingUrl(url) {
            existingSavedUrl = existing
            selectedTags = Set(existing.tags)
            tagsCollectionView.reloadData()

            let tagsList = existing.tags.joined(separator: ", ")
            statusLabel.text = "Already saved with tags: \(tagsList)"
            statusLabel.isHidden = false
            saveButton.setTitle("Update Tags", for: .normal)
        }
    }

    func loadTags() {
        availableTags = DatabaseManager.shared.loadTags()
        tagsCollectionView.reloadData()
    }

    @objc func savePressed() {
        guard let url = sharedURL, !url.isEmpty else {
            showAlert("Please wait for URL to load")
            return
        }

        // If there's text in the new tag field, add it first
        if let newTagText = newTagTextField.text?.trimmingCharacters(in: .whitespaces),
           !newTagText.isEmpty {
            selectedTags.insert(newTagText)
            newTagTextField.text = ""
        }

        guard !selectedTags.isEmpty else {
            showAlert("Please select at least one tag")
            return
        }

        // Merge tags if URL already exists
        let finalTags: [String]
        if let existing = existingSavedUrl {
            let mergedTags = Set(existing.tags).union(selectedTags)
            finalTags = Array(mergedTags).sorted()
        } else {
            finalTags = Array(selectedTags).sorted()
        }

        DatabaseManager.shared.saveUrl(
            url: url,
            tags: finalTags,
            existingId: existingSavedUrl?.id,
            existingSavedAt: existingSavedUrl?.saved_at
        )

        extensionContext?.completeRequest(returningItems: [], completionHandler: nil)
    }

    @objc func cancelPressed() {
        extensionContext?.completeRequest(returningItems: [], completionHandler: nil)
    }

    func showAlert(_ message: String) {
        let alert = UIAlertController(title: nil, message: message, preferredStyle: .alert)
        alert.addAction(UIAlertAction(title: "OK", style: .default))
        present(alert, animated: true)
    }
}

// MARK: - UICollectionView
extension ShareViewController: UICollectionViewDelegate, UICollectionViewDataSource {
    func collectionView(_ collectionView: UICollectionView, numberOfItemsInSection section: Int) -> Int {
        return availableTags.count
    }

    func collectionView(_ collectionView: UICollectionView, cellForItemAt indexPath: IndexPath) -> UICollectionViewCell {
        let cell = collectionView.dequeueReusableCell(withReuseIdentifier: "TagCell", for: indexPath) as! TagCell
        let tag = availableTags[indexPath.item]
        cell.configure(with: tag.name, isSelected: selectedTags.contains(tag.name))
        return cell
    }

    func collectionView(_ collectionView: UICollectionView, didSelectItemAt indexPath: IndexPath) {
        let tag = availableTags[indexPath.item]
        if selectedTags.contains(tag.name) {
            selectedTags.remove(tag.name)
        } else {
            selectedTags.insert(tag.name)
        }
        collectionView.reloadItems(at: [indexPath])
    }
}

// MARK: - UITextFieldDelegate
extension ShareViewController: UITextFieldDelegate {
    func textFieldShouldReturn(_ textField: UITextField) -> Bool {
        // Save when Return is pressed
        savePressed()
        return true
    }
}

// MARK: - TagCell
class TagCell: UICollectionViewCell {
    let label = UILabel()

    override init(frame: CGRect) {
        super.init(frame: frame)

        label.font = .systemFont(ofSize: 14)
        label.translatesAutoresizingMaskIntoConstraints = false
        contentView.addSubview(label)

        contentView.layer.cornerRadius = 8
        contentView.layer.borderWidth = 1

        NSLayoutConstraint.activate([
            label.topAnchor.constraint(equalTo: contentView.topAnchor, constant: 8),
            label.leadingAnchor.constraint(equalTo: contentView.leadingAnchor, constant: 12),
            label.trailingAnchor.constraint(equalTo: contentView.trailingAnchor, constant: -12),
            label.bottomAnchor.constraint(equalTo: contentView.bottomAnchor, constant: -8)
        ])
    }

    required init?(coder: NSCoder) {
        fatalError("init(coder:) has not been implemented")
    }

    func configure(with text: String, isSelected: Bool) {
        label.text = text

        if isSelected {
            contentView.backgroundColor = .systemBlue
            contentView.layer.borderColor = UIColor.systemBlue.cgColor
            label.textColor = .white
        } else {
            contentView.backgroundColor = .systemBackground
            contentView.layer.borderColor = UIColor.systemGray4.cgColor
            label.textColor = .label
        }
    }
}