<?php

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

class AIEO_Attribution_Cost {

	const TABLE_NAME     = 'aieo_attribution_cost';
	const SCHEMA_OPTION  = 'aieo_attribution_cost_schema_version';
	const SCHEMA_VERSION = '1';

	
	const DIMENSIONS = array( 'channel', 'campaign', 'creative' );
	const SOURCES    = array( 'manual', 'csv', 'google_ads', 'meta', 'tiktok' );

	public static function init() {
		add_action( 'admin_init', array( __CLASS__, 'maybe_install' ) );
	}

	public static function table_name() {
		global $wpdb;
		return $wpdb->prefix . self::TABLE_NAME;
	}

	public static function table_exists() {
		global $wpdb;
		$t = self::table_name();
		return (string) $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $t ) ) === $t;
	}

	public static function maybe_install() {
		if ( get_option( self::SCHEMA_OPTION, '' ) === self::SCHEMA_VERSION && self::table_exists() ) {
			return;
		}
		if ( ! AIEO_Schema_Lock::acquire( self::SCHEMA_OPTION ) ) {
			return;
		}
		global $wpdb;
		$t       = self::table_name();
		$charset = aieo_get_charset_collate();
		
		
		$wpdb->query(
			"CREATE TABLE IF NOT EXISTS `{$t}` (
				`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
				`dimension_type` VARCHAR(20) NOT NULL DEFAULT 'channel',
				`dimension_value` VARCHAR(191) NOT NULL DEFAULT '',
				`period_start` DATE NOT NULL,
				`period_end` DATE NOT NULL,
				`amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
				`currency` VARCHAR(8) NOT NULL DEFAULT 'EUR',
				`source` VARCHAR(20) NOT NULL DEFAULT 'manual',
				`note` VARCHAR(255) NOT NULL DEFAULT '',
				`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
				PRIMARY KEY (`id`),
				UNIQUE KEY `uq_dim_period_source` (`dimension_type`,`dimension_value`,`period_start`,`period_end`,`source`),
				KEY `idx_dim` (`dimension_type`,`dimension_value`),
				KEY `idx_period` (`period_start`,`period_end`)
			) {$charset}"
		);
		update_option( self::SCHEMA_OPTION, self::SCHEMA_VERSION, false );
	}

	
	public static function upsert( $dimension_type, $dimension_value, $period_start, $period_end, $amount, $source = 'manual', $note = '', $currency = 'EUR' ) {
		global $wpdb;
		if ( ! in_array( $dimension_type, self::DIMENSIONS, true ) ) {
			return false;
		}
		if ( ! in_array( $source, self::SOURCES, true ) ) {
			$source = 'manual';
		}
		if ( ! self::valid_date( $period_start ) || ! self::valid_date( $period_end ) ) {
			return false;
		}
		$t = self::table_name();
		
		return false !== $wpdb->query( $wpdb->prepare(
			"INSERT INTO `{$t}` (dimension_type, dimension_value, period_start, period_end, amount, currency, source, note, updated_at)
			 VALUES (%s,%s,%s,%s,%f,%s,%s,%s,UTC_TIMESTAMP())
			 ON DUPLICATE KEY UPDATE amount=VALUES(amount), currency=VALUES(currency), note=VALUES(note), updated_at=UTC_TIMESTAMP()",
			$dimension_type, (string) $dimension_value, $period_start, $period_end, (float) $amount, $currency, $source, (string) $note
		) );
	}

	
	public static function cost_for( $dimension_type, $dimension_value, $win_start, $win_end ) {
		global $wpdb;
		if ( ! self::table_exists() ) {
			return 0.0;
		}
		$t = self::table_name();
		return (float) $wpdb->get_var( $wpdb->prepare(
			"SELECT COALESCE(SUM(amount),0) FROM `{$t}`
			 WHERE dimension_type=%s AND dimension_value=%s
			   AND period_start <= %s AND period_end >= %s",
			$dimension_type, (string) $dimension_value, $win_end, $win_start
		) );
	}

	
	public static function rows( $dimension_type = '', $limit = 2000, $win_start = '', $win_end = '', $value = '' ) {
		global $wpdb;
		if ( ! self::table_exists() ) {
			return array();
		}
		$t     = self::table_name();
		$limit = max( 1, min( 10000, (int) $limit ) );

		$where = array( '1=1' );
		$args  = array();
		if ( '' !== $dimension_type && in_array( $dimension_type, self::DIMENSIONS, true ) ) {
			$where[] = 'dimension_type = %s';
			$args[]  = $dimension_type;
		}
		if ( '' !== (string) $value ) {
			$where[] = 'dimension_value = %s';
			$args[]  = (string) $value;
		}
		
		if ( self::valid_date( $win_start ) && self::valid_date( $win_end ) ) {
			$where[] = 'period_start <= %s AND period_end >= %s';
			$args[]  = $win_end;
			$args[]  = $win_start;
		}
		$sql    = "SELECT * FROM `{$t}` WHERE " . implode( ' AND ', $where )
			. ' ORDER BY period_end DESC, period_start DESC, dimension_value LIMIT %d';
		$args[] = $limit;
		
		return $wpdb->get_results( $wpdb->prepare( $sql, $args ), ARRAY_A );
	}

	
	public static function delete( $id ) {
		global $wpdb;
		return (bool) $wpdb->delete( self::table_name(), array( 'id' => (int) $id ), array( '%d' ) );
	}

	private static function valid_date( $d ) {
		return is_string( $d ) && (bool) preg_match( '/^\d{4}-\d{2}-\d{2}$/', $d );
	}
}