<?php
/**
 * Datenbank-Zugriffsschicht für die Angebote (Presupuestos).
 *
 * Speichert pro Angebot alle Eingabedaten (Kunde, Positionen, Summen,
 * Zahlungsart, Nummer, Datum) und verwaltet die fortlaufende Nummer
 * im Format YY_0000 (pro Jahr hochzählend).
 */

if ( ! defined( 'ABSPATH' ) ) {
    exit;
}

class AG_DB {

    public static function table() {
        global $wpdb;
        return $wpdb->prefix . 'ag_angebote';
    }

    /**
     * Tabelle bei Aktivierung anlegen/aktualisieren.
     */
    public static function install() {
        global $wpdb;
        require_once ABSPATH . 'wp-admin/includes/upgrade.php';

        $table           = self::table();
        $charset_collate = $wpdb->get_charset_collate();

        $sql = "CREATE TABLE $table (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            number VARCHAR(20) NOT NULL DEFAULT '',
            year_part SMALLINT UNSIGNED NOT NULL DEFAULT 0,
            seq_part INT UNSIGNED NOT NULL DEFAULT 0,
            offer_date DATE NULL,
            data LONGTEXT NULL,
            created_by BIGINT UNSIGNED NULL,
            created_at DATETIME NOT NULL,
            updated_at DATETIME NOT NULL,
            PRIMARY KEY  (id),
            UNIQUE KEY number (number),
            KEY year_seq (year_part, seq_part)
        ) $charset_collate;";

        dbDelta( $sql );
    }

    /**
     * Ermittelt die nächste fortlaufende Nummer für ein Jahr.
     * Format: "YY_0000" (z. B. "26_0001").
     *
     * @param int|null $year Vierstelliges Jahr (Standard: aktuelles Jahr).
     * @return array{number:string, year_part:int, seq_part:int}
     */
    public static function next_number( $year = null ) {
        global $wpdb;

        if ( null === $year ) {
            $year = (int) current_time( 'Y' );
        }

        $yy = (int) substr( (string) $year, -2 );

        $max = $wpdb->get_var(
            $wpdb->prepare(
                'SELECT MAX(seq_part) FROM ' . self::table() . ' WHERE year_part = %d',
                $yy
            )
        );

        $seq = ( null === $max ) ? 1 : ( (int) $max + 1 );

        return array(
            'number'    => self::format_number( $yy, $seq ),
            'year_part' => $yy,
            'seq_part'  => $seq,
        );
    }

    /**
     * Formatiert Jahr + Sequenz als "YY_0000".
     */
    public static function format_number( $yy, $seq ) {
        return sprintf( '%02d_%04d', (int) $yy, (int) $seq );
    }

    /**
     * Zerlegt eine Nummer "YY_0000" in Jahr- und Sequenzteil.
     *
     * @return array{year_part:int, seq_part:int}|null
     */
    public static function parse_number( $number ) {
        if ( preg_match( '/^(\d{2})[_\-](\d{1,6})$/', trim( (string) $number ), $m ) ) {
            return array(
                'year_part' => (int) $m[1],
                'seq_part'  => (int) $m[2],
            );
        }
        return null;
    }

    /**
     * Prüft, ob eine Nummer bereits vergeben ist (optional außer der eigenen ID).
     */
    public static function number_exists( $number, $exclude_id = 0 ) {
        global $wpdb;
        $id = $wpdb->get_var(
            $wpdb->prepare(
                'SELECT id FROM ' . self::table() . ' WHERE number = %s AND id != %d',
                $number,
                (int) $exclude_id
            )
        );
        return ! empty( $id );
    }

    /**
     * Speichert ein Angebot (neu oder Update).
     *
     * @param array    $data Eingabedaten (wird als JSON abgelegt).
     * @param string   $number
     * @param string   $offer_date  Y-m-d
     * @param int|null $id
     * @return int Angebots-ID
     */
    public static function save( array $data, $number, $offer_date, $id = null ) {
        global $wpdb;

        $parts = self::parse_number( $number );
        $now   = current_time( 'mysql' );

        $row = array(
            'number'     => $number,
            'year_part'  => $parts ? $parts['year_part'] : 0,
            'seq_part'   => $parts ? $parts['seq_part'] : 0,
            'offer_date' => $offer_date ? $offer_date : null,
            'data'       => wp_json_encode( $data ),
            'updated_at' => $now,
        );

        if ( $id ) {
            $wpdb->update( self::table(), $row, array( 'id' => (int) $id ) );
            return (int) $id;
        }

        $row['created_by'] = get_current_user_id();
        $row['created_at'] = $now;

        $wpdb->insert( self::table(), $row );
        return (int) $wpdb->insert_id;
    }

    public static function get( $id ) {
        global $wpdb;
        $row = $wpdb->get_row(
            $wpdb->prepare( 'SELECT * FROM ' . self::table() . ' WHERE id = %d', $id ),
            ARRAY_A
        );
        if ( ! $row ) {
            return null;
        }
        $row['data'] = json_decode( (string) $row['data'], true );
        if ( ! is_array( $row['data'] ) ) {
            $row['data'] = array();
        }
        return $row;
    }

    public static function get_by_number( $number ) {
        global $wpdb;
        $row = $wpdb->get_row(
            $wpdb->prepare( 'SELECT * FROM ' . self::table() . ' WHERE number = %s', $number ),
            ARRAY_A
        );
        if ( ! $row ) {
            return null;
        }
        $row['data'] = json_decode( (string) $row['data'], true );
        if ( ! is_array( $row['data'] ) ) {
            $row['data'] = array();
        }
        return $row;
    }

    /**
     * Liefert alle Angebote (neueste zuerst).
     */
    public static function get_all( $limit = 200 ) {
        global $wpdb;
        $rows = $wpdb->get_results(
            $wpdb->prepare(
                'SELECT id, number, offer_date, data, created_by, created_at, updated_at FROM ' . self::table() . ' ORDER BY year_part DESC, seq_part DESC LIMIT %d',
                (int) $limit
            ),
            ARRAY_A
        );
        if ( ! $rows ) {
            return array();
        }
        foreach ( $rows as &$row ) {
            $row['data'] = json_decode( (string) $row['data'], true );
            if ( ! is_array( $row['data'] ) ) {
                $row['data'] = array();
            }
        }
        return $rows;
    }

    public static function delete( $id ) {
        global $wpdb;
        $wpdb->delete( self::table(), array( 'id' => (int) $id ) );
    }
}
