package document

import (
	"archive/zip"
	"database/sql"
	"encoding/json"
	"fmt"
	"io"
	"net/http"
	"os"
	"path/filepath"
	"strings"
	"time"

	"github.com/google/uuid"
	"github.com/gorilla/mux"
)

// ── Model ─────────────────────────────────────────────────────────────────────

type Document struct {
	ID            string    `json:"id"`
	PropertyID    string    `json:"property_id"`
	TransactionID string    `json:"transaction_id,omitempty"`
	Filename      string    `json:"filename"`
	OriginalName  string    `json:"original_name"`
	FilePath      string    `json:"-"`
	MimeType      string    `json:"mime_type"`
	FiscalYear    int       `json:"fiscal_year"`
	Category      string    `json:"category"`
	UploadedBy    string    `json:"uploaded_by"`
	CreatedAt     time.Time `json:"created_at"`
	// Joint
	PropertyName string `json:"property_name,omitempty"`
}

// ── Store ─────────────────────────────────────────────────────────────────────

type Store struct{ db *sql.DB }

func NewStore(db *sql.DB) *Store { return &Store{db: db} }

// Migrate ajoute la colonne category si elle n'existe pas encore.
func (s *Store) Migrate() {
	s.db.Exec(`ALTER TABLE documents ADD COLUMN doc_month INTEGER NOT NULL DEFAULT 0`)
	s.db.Exec(`ALTER TABLE documents ADD COLUMN category TEXT NOT NULL DEFAULT ''`)
}

func (s *Store) List(propertyID string, fiscalYear int, category string) ([]Document, error) {
	query := `
		SELECT d.id, d.property_id, COALESCE(d.transaction_id,''), d.filename, d.original_name,
		       d.file_path, COALESCE(d.mime_type,''), COALESCE(d.fiscal_year,0),
		       COALESCE(d.category,''),
		       COALESCE(d.uploaded_by,''), d.created_at, p.name
		FROM documents d
		JOIN properties p ON p.id = d.property_id
		WHERE 1=1`
	args := []any{}
	if propertyID != "" {
		query += " AND d.property_id=?"
		args = append(args, propertyID)
	}
	if fiscalYear > 0 {
		query += " AND d.fiscal_year=?"
		args = append(args, fiscalYear)
	}
	if category != "" {
		query += " AND d.category=?"
		args = append(args, category)
	}
	query += " ORDER BY d.fiscal_year DESC, d.created_at DESC"

	rows, err := s.db.Query(query, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var docs []Document
	for rows.Next() {
		var d Document
		if err := rows.Scan(&d.ID, &d.PropertyID, &d.TransactionID, &d.Filename, &d.OriginalName,
			&d.FilePath, &d.MimeType, &d.FiscalYear, &d.Category,
			&d.UploadedBy, &d.CreatedAt, &d.PropertyName); err != nil {
			return nil, err
		}
		docs = append(docs, d)
	}
	return docs, nil
}

func (s *Store) Get(id string) (*Document, error) {
	var d Document
	err := s.db.QueryRow(`
		SELECT d.id, d.property_id, COALESCE(d.transaction_id,''), d.filename, d.original_name,
		       d.file_path, COALESCE(d.mime_type,''), COALESCE(d.fiscal_year,0),
		       COALESCE(d.category,''),
		       COALESCE(d.uploaded_by,''), d.created_at, p.name
		FROM documents d
		JOIN properties p ON p.id = d.property_id
		WHERE d.id=?`, id,
	).Scan(&d.ID, &d.PropertyID, &d.TransactionID, &d.Filename, &d.OriginalName,
		&d.FilePath, &d.MimeType, &d.FiscalYear, &d.Category,
		&d.UploadedBy, &d.CreatedAt, &d.PropertyName)
	return &d, err
}

func (s *Store) Create(d *Document) error {
	_, err := s.db.Exec(
		`INSERT INTO documents (id, property_id, transaction_id, filename, original_name, file_path, mime_type, fiscal_year, category, uploaded_by) VALUES (?,?,?,?,?,?,?,?,?,?)`,
		d.ID, d.PropertyID, nullStr(d.TransactionID), d.Filename, d.OriginalName, d.FilePath,
		d.MimeType, nullInt(d.FiscalYear), nullStr(d.Category), nullStr(d.UploadedBy),
	)
	return err
}

func (s *Store) Delete(id string) (string, error) {
	var path string
	s.db.QueryRow(`SELECT file_path FROM documents WHERE id=?`, id).Scan(&path)
	_, err := s.db.Exec(`DELETE FROM documents WHERE id=?`, id)
	return path, err
}

func (s *Store) GetByPropertyAndYear(propertyID string, year int) ([]Document, error) {
	return s.List(propertyID, year, "")
}

// ── Handler ───────────────────────────────────────────────────────────────────

type Handler struct {
	store   *Store
	dataDir string
}

func NewHandler(store *Store, dataDir string) *Handler {
	os.MkdirAll(dataDir, 0755)
	return &Handler{store: store, dataDir: dataDir}
}

func (h *Handler) List(w http.ResponseWriter, r *http.Request) {
	q := r.URL.Query()
	var year int
	fmt.Sscanf(q.Get("fiscal_year"), "%d", &year)
	docs, err := h.store.List(q.Get("property_id"), year, q.Get("category"))
	if err != nil {
		http.Error(w, err.Error(), http.StatusInternalServerError)
		return
	}
	if docs == nil {
		docs = []Document{}
	}
	respond(w, docs)
}

func (h *Handler) Get(w http.ResponseWriter, r *http.Request) {
	d, err := h.store.Get(mux.Vars(r)["id"])
	if err == sql.ErrNoRows {
		http.Error(w, "not found", http.StatusNotFound)
		return
	} else if err != nil {
		http.Error(w, err.Error(), http.StatusInternalServerError)
		return
	}
	respond(w, d)
}

func (h *Handler) Upload(w http.ResponseWriter, r *http.Request) {
	r.ParseMultipartForm(32 << 20)

	file, header, err := r.FormFile("file")
	if err != nil {
		http.Error(w, "fichier requis", http.StatusBadRequest)
		return
	}
	defer file.Close()

	id := uuid.NewString()
	ext := filepath.Ext(header.Filename)
	filename := id + ext

	propertyID := r.FormValue("property_id")
	year := r.FormValue("fiscal_year")
	dir := filepath.Join(h.dataDir, propertyID, year)
	os.MkdirAll(dir, 0755)

	destPath := filepath.Join(dir, filename)
	dest, err := os.Create(destPath)
	if err != nil {
		http.Error(w, "erreur création fichier", http.StatusInternalServerError)
		return
	}
	defer dest.Close()
	io.Copy(dest, file)

	var fiscalYear int
	fmt.Sscanf(year, "%d", &fiscalYear)

	d := &Document{
		ID:            id,
		PropertyID:    propertyID,
		TransactionID: r.FormValue("transaction_id"),
		Filename:      filename,
		OriginalName:  header.Filename,
		FilePath:      destPath,
		MimeType:      header.Header.Get("Content-Type"),
		FiscalYear:    fiscalYear,
		Category:      r.FormValue("category"),
		UploadedBy:    r.FormValue("uploaded_by"),
	}

	if err := h.store.Create(d); err != nil {
		http.Error(w, err.Error(), http.StatusInternalServerError)
		return
	}
	w.WriteHeader(http.StatusCreated)
	respond(w, d)
}

func (h *Handler) Download(w http.ResponseWriter, r *http.Request) {
	d, err := h.store.Get(mux.Vars(r)["id"])
	if err != nil {
		http.Error(w, "not found", http.StatusNotFound)
		return
	}
	w.Header().Set("Content-Disposition", fmt.Sprintf(`attachment; filename="%s"`, d.OriginalName))
	if d.MimeType != "" {
		w.Header().Set("Content-Type", d.MimeType)
	}
	http.ServeFile(w, r, d.FilePath)
}

func (h *Handler) Delete(w http.ResponseWriter, r *http.Request) {
	path, err := h.store.Delete(mux.Vars(r)["id"])
	if err != nil {
		http.Error(w, err.Error(), http.StatusInternalServerError)
		return
	}
	if path != "" {
		os.Remove(path)
	}
	w.WriteHeader(http.StatusNoContent)
}

// Export génère un ZIP avec la structure : année/catégorie/fichier
func (h *Handler) Export(w http.ResponseWriter, r *http.Request) {
	q := r.URL.Query()
	propertyID := q.Get("property_id")
	yearStr := q.Get("year")
	var year int
	fmt.Sscanf(yearStr, "%d", &year)
	if year == 0 {
		year = time.Now().Year()
		yearStr = fmt.Sprintf("%d", year)
	}

	docs, err := h.store.List(propertyID, year, "")
	if err != nil {
		http.Error(w, err.Error(), http.StatusInternalServerError)
		return
	}
	if len(docs) == 0 {
		http.Error(w, "aucun document pour ces critères", http.StatusNotFound)
		return
	}

	propLabel := "tous"
	if propertyID != "" {
		if docs[0].PropertyName != "" {
			propLabel = sanitizeName(docs[0].PropertyName)
		} else {
			propLabel = propertyID[:8]
		}
	}

	filename := fmt.Sprintf("documents_%s_%s.zip", propLabel, yearStr)
	w.Header().Set("Content-Type", "application/zip")
	w.Header().Set("Content-Disposition", fmt.Sprintf(`attachment; filename="%s"`, filename))

	zw := zip.NewWriter(w)
	defer zw.Close()

	for _, d := range docs {
		catDir := "Sans catégorie"
		if strings.TrimSpace(d.Category) != "" {
			catDir = sanitizeName(d.Category)
		}

		zipPath := filepath.Join(yearStr, catDir, d.OriginalName)
		zipPath = strings.ReplaceAll(zipPath, "\\", "/")

		f, err := os.Open(d.FilePath)
		if err != nil {
			continue
		}

		entry, err := zw.Create(zipPath)
		if err != nil {
			f.Close()
			continue
		}
		io.Copy(entry, f)
		f.Close()
	}
}

func sanitizeName(s string) string {
	replacer := strings.NewReplacer(
		"/", "-", "\\", "-", ":", "-", "*", "-",
		"?", "-", "\"", "-", "<", "-", ">", "-", "|", "-",
	)
	return strings.TrimSpace(replacer.Replace(s))
}

func respond(w http.ResponseWriter, v any) {
	w.Header().Set("Content-Type", "application/json")
	json.NewEncoder(w).Encode(v)
}

func nullStr(s string) any {
	if s == "" || strings.TrimSpace(s) == "" {
		return nil
	}
	return s
}

func nullInt(i int) any {
	if i == 0 {
		return nil
	}
	return i
}