package db

import (
	"database/sql"
	"encoding/base64"
	"time"

	_ "github.com/mattn/go-sqlite3"
)

type Form struct {
	ID          int64
	Title       string
	PDFName     string
	PDFData     []byte
	PDFBase64   string
	Markdown    string
	Conversion  string
	CreatedAt   time.Time
	UpdatedAt   time.Time
}

type DB struct {
	db *sql.DB
}

func New(path string) (*DB, error) {
	sqldb, err := sql.Open("sqlite3", path)
	if err != nil {
		return nil, err
	}

	sqldb.Exec(`CREATE TABLE IF NOT EXISTS forms (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		title TEXT NOT NULL DEFAULT '',
		pdf_name TEXT NOT NULL DEFAULT '',
		pdf_data BLOB NOT NULL,
		markdown TEXT NOT NULL DEFAULT '',
		conversion TEXT NOT NULL DEFAULT 'pending',
		created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
		updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
	)`)

	return &DB{db: sqldb}, nil
}

func (d *DB) Close() error {
	return d.db.Close()
}

func (d *DB) CreateForm(title string, pdfName string, pdfData []byte) (*Form, error) {
	res, err := d.db.Exec(
		"INSERT INTO forms (title, pdf_name, pdf_data) VALUES (?, ?, ?)",
		title, pdfName, pdfData,
	)
	if err != nil {
		return nil, err
	}

	id, _ := res.LastInsertId()
	return d.GetForm(id)
}

func (d *DB) GetForm(id int64) (*Form, error) {
	row := d.db.QueryRow("SELECT id, title, pdf_name, pdf_data, markdown, conversion, created_at, updated_at FROM forms WHERE id = ?", id)
	var f Form
	var pdfData *[]byte
	err := row.Scan(&f.ID, &f.Title, &f.PDFName, &pdfData, &f.Markdown, &f.Conversion, &f.CreatedAt, &f.UpdatedAt)
	if err != nil {
		return nil, err
	}
	if pdfData != nil {
		f.PDFData = *pdfData
		f.PDFBase64 = "data:application/pdf;base64," + base64.StdEncoding.EncodeToString(*pdfData)
	}
	return &f, nil
}

func (d *DB) ListForms() ([]Form, error) {
	rows, err := d.db.Query("SELECT id, title, pdf_name, markdown, conversion, created_at, updated_at FROM forms ORDER BY created_at DESC")
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	forms := make([]Form, 0)
	for rows.Next() {
		var f Form
		err := rows.Scan(&f.ID, &f.Title, &f.PDFName, &f.Markdown, &f.Conversion, &f.CreatedAt, &f.UpdatedAt)
		if err != nil {
			return nil, err
		}
		forms = append(forms, f)
	}
	return forms, nil
}

func (d *DB) UpdateMarkdown(id int64, markdown string) error {
	_, err := d.db.Exec("UPDATE forms SET markdown = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?", markdown, id)
	return err
}

func (d *DB) UpdateConversion(id int64, conversion string) error {
	_, err := d.db.Exec("UPDATE forms SET conversion = ? WHERE id = ?", conversion, id)
	return err
}

func (d *DB) UpdateTitle(id int64, title string) error {
	_, err := d.db.Exec("UPDATE forms SET title = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?", title, id)
	return err
}

func (d *DB) DeleteForm(id int64) error {
	_, err := d.db.Exec("DELETE FROM forms WHERE id = ?", id)
	return err
}