<?php
use Orchard\Service\Ows\Carveouts;

$logger = Zend_Registry::get('applicationLoggerOA');
$carveoutsClient = new Carveouts($logger);
$physicalPricingTierValue = isset($physicalPricingTierValue) ? $physicalPricingTierValue : '-';
$sqlProductPhysical = 'SELECT pp.exclusive_for, pp.explicit, pp.wholesale_price, ppp.name as packaging,
                    ppp.id as packaging_id, pp.hmv_notes, pp.meeting_notes, pp.embargo_date, pp.pricing,
                    pp.discount, pp.box_lot, pp.initial_stock, pp.pline, pp.individual, pp.units_per_set,
                    pp.exportable, pp.manufacturing_obligation, pp.production_notes, pp.display_configuration,
                    pp.japan_distribution, pp.edition
                    FROM product_physical pp
                    LEFT JOIN product_physical_packaging ppp ON ppp.id = pp.packaging_id
                    WHERE pp.release_id = ?';

$productPhysicalQuery = $mysqli->prepare($sqlProductPhysical);
$productPhysicalQuery->bind_param('i', $release_id);
if ($productPhysicalQuery->execute()) {
    $productPhysicalResult = $productPhysicalQuery->get_result();
    $productPhysicalData  = $productPhysicalResult->fetch_assoc();
} else {
    showierror($productPhysicalQuery);
}
$sqlPhysicalOrderTarget = 'SELECT pt.type, pt.target
                            FROM physical_order_targets pt
                            INNER JOIN product_physical pp ON pp.id = pt.product_id
                            WHERE pp.release_id = ?';

$physicalOrderTargetQuery = $mysqli->prepare($sqlPhysicalOrderTarget);
$physicalOrderTargetQuery->bind_param('i', $release_id);
if ($physicalOrderTargetQuery->execute()) {
    $physicalOrderTargetResult = $physicalOrderTargetQuery->get_result();
    $physicalOrderTargetData   = $physicalOrderTargetResult->fetch_all(MYSQLI_ASSOC);
} else {
    showierror($physicalOrderQuery);
}

$USSupplyChainInfo = array();
$canadaSupplyChainInfo = array();
$sqlSupplyChainReturnInfo = 'SELECT returnability, return_disposition, store_id FROM product_physical_supply_chain_info WHERE product_id = ?';
$supplyChainReturnInfoQuery = $mysqli->prepare($sqlSupplyChainReturnInfo);
$supplyChainReturnInfoQuery->bind_param('i', $release_id);
if ($supplyChainReturnInfoQuery->execute()) {
    $supplyChainReturnInfoResult = $supplyChainReturnInfoQuery->get_result();
    $supplyChainReturnData = $supplyChainReturnInfoResult->fetch_all(MYSQLI_ASSOC);
    if ($supplyChainReturnInfoResult->num_rows > 0) {
        foreach ($supplyChainReturnData as $value) {
            if ($value['store_id'] == STORE_ID_FOR_US) {
                $USSupplyChainInfo = $value;
            } elseif ($value['store_id'] == STORE_ID_FOR_CANADA) {
                $canadaSupplyChainInfo = $value;
            }
        }
    }
} else {
    showierror($supplyChainReturnInfoQuery);
}


if ($oaPhysicalSupplyChainCarvedInEnabled) {
    $supplyChainMetadata = $owsProductPhysicalServiceClient->getSupplyChainMetadataByProduct($release_id, $_SESSION['S_USERID']);
    $supplyChainDatesData = $supplyChainMetadata['items'];
} else {
    $is_deleted = 0;
    $store_id_for_canada = STORE_ID_FOR_CANADA;
    $store_id_for_us = STORE_ID_FOR_US;
    $sqlSupplyChainDatesInfo = "SELECT ppscd.id, ppscd.returnability, ppscd.return_disposition, ppscd.store_id, ppscd.embargo_date, ppscd.release_date, ppscd.sale_start_date
                                 FROM (
                                   (SELECT ppscm.id, ppsci.returnability, ppsci.return_disposition, cmm.customer_master_master_id AS store_id,
                                     IF(ppscm.embargo_date, ppscm.embargo_date, '') AS embargo_date, IF(ppscm.release_date, ppscm.release_date, '') AS release_date, IF(ppscm.sale_start_date, ppscm.sale_start_date, '') AS sale_start_date
                                     FROM customer_master_master cmm
                                     LEFT JOIN product_physical_supply_chain_info ppsci ON ppsci.store_id = cmm.customer_master_master_id AND ppsci.product_id = ?
                                     LEFT JOIN product_physical_supply_chain_metadata ppscm ON ppscm.product_id = ppsci.product_id AND ppscm.store_id = ppsci.store_id
                                     WHERE cmm.customer_master_master_id IN (?, ?)
                                   )
                                   UNION
                                    (SELECT ppscmd.id, '' AS returnability, '' AS return_disposition , ppscmd.store_id, IF(ppscmd.embargo_date, ppscmd.embargo_date, '') AS embargo_date, IF(ppscmd.release_date, ppscmd.release_date, '') AS release_date, IF(ppscmd.sale_start_date, ppscmd.sale_start_date, '') AS sale_start_date
                                      FROM product_physical_supply_chain_metadata ppscmd
                                      WHERE product_id = ? AND is_deleted = ?
                                    )
                                ) As ppscd
                               GROUP BY ppscd.store_id
                               ORDER BY FIELD(ppscd.store_id, ? ,?) DESC, ppscd.id";
    $supplyChainDatesInfoQuery = $mysqli->prepare($sqlSupplyChainDatesInfo);
    $supplyChainDatesInfoQuery->bind_param('iiiiiii', $release_id, $store_id_for_canada, $store_id_for_us, $release_id, $is_deleted, $store_id_for_canada, $store_id_for_us);
    if ($supplyChainDatesInfoQuery->execute()) {
        $supplyChainDatesInfoResults = $supplyChainDatesInfoQuery->get_result();
        $supplyChainDatesData = $supplyChainDatesInfoResults->fetch_all(MYSQLI_ASSOC);
    }
}

if ($isCCMPhysicalSupplyChainInfoOaEnabled) {
    $storeResults = $carveoutsClient->getPhysicalStores();
    $productPhysicalSupplyChainMetadataInfo = getProductPhysicalSupplyChainInfo($wrapperObj, $release_id);
    foreach ($storeResults as $id => $name) {
        $storeResults[$id] = [
            'name' => $name,
            'sale_start_date' => '',
            'initial_stock' => ''
        ];
    }
    if (!empty($productPhysicalSupplyChainMetadataInfo)) {
        foreach ($productPhysicalSupplyChainMetadataInfo as $row) {
            $id = $row['store_id'];
            if (isset($storeResults[$id])) {
                $storeResults[$id]['sale_start_date'] = $row['sale_start_date'];
                $storeResults[$id]['initial_stock'] = $row['initial_stock'];
            }
        }
    }
    $carveoutResults = $carveoutsClient->getReleaseCarveoutResult($upc);
    $restrictedDms = array();
    foreach ($carveoutResults['store'] as $key => $dmsProperties) {
        $restrictedDms[$key] = $dmsProperties["dms_name"];
    }
    foreach ($storeResults as $key => &$value) {
        $value['restricted'] = isset($restrictedDms[$key]);

        if ($value['restricted']) {
            $value['sale_start_date'] = '';
            $value['initial_stock'] = '';
        }
    }
}

if ($isOaJapanPhysicalFieldsEnabled) {
    $phoneticTranslationsSql = 'SELECT field_name, phonetic_translation FROM release_phonetic_translations WHERE release_id = ? AND language_id = 24';
    $phoneticTranslationsQuery = $mysqli->prepare($phoneticTranslationsSql);
    $phoneticTranslationsQuery->bind_param('i', $release_id);
    $phoneticTranslationsData = array();

    if ($phoneticTranslationsQuery->execute()) {
        $phoneticTranslationsResult = $phoneticTranslationsQuery->get_result();
        $phoneticTranslationsData  = $phoneticTranslationsResult->fetch_all(MYSQLI_ASSOC);

        foreach ($phoneticTranslationsData as $item) {
            if ($item['field_name'] == 'release_name') {
                $japaneseReleaseName = $item['phonetic_translation'];
            } elseif ($item['field_name'] == 'artist_name') {
                $japaneseArtistName = $item['phonetic_translation'];
            }
        }
    } else {
        showierror($phoneticTranslationsQuery);
    }
}
$countryOfOriginSql = 'SELECT r.country_of_origin, c.`name` FROM releases r 
    LEFT JOIN country c ON c.id = r.country_of_origin
    WHERE r.release_id = ?';
$countryOfOriginQuery = $mysqli->prepare($countryOfOriginSql);
$countryOfOriginQuery->bind_param('i', $release_id);
$countryOfOriginQuery->execute();
$countryOfOriginResult = $countryOfOriginQuery->get_result();
$countryOfOriginData = $countryOfOriginResult->fetch_assoc();
?>
<div class="panelHeader">
    <h3>Physical Metadata</h3>
    <ul>
        <li><a href="edit_release.php?upc=<?=$upc?>" class="btn btn-link btn-sm">Edit</a></li>
    </ul>
</div>
<div class="panelContent">
    <?php if (empty($frontendSalesGoalsEnabled)) { ?>
        <div class="panelSummary">Physical Product Metadata</div>
    <?php } ?>
    <table class="form">
        <tr>
            <th>Product Type:</th>
            <td><?=$rel_row['distribution_format_media_name']?></td>
        </tr>
        <tr>
            <th>Format:</th>
            <td><?=$rel_row['distribution_format_media_format_name']?></td>
        </tr>
        <tr>
            <th>Units Per Set:</th>
            <td><?=$productPhysicalData['units_per_set']?></td>
        </tr>
        <tr>
            <th>Display Configuration:</th>
            <td><?=$productPhysicalData['display_configuration']?></td>
        </tr>
        <tr>
            <th>Packaging:</th>
            <td><?=$productPhysicalData['packaging']?></td>
        </tr>
        <tr>
            <th>Embargo Date:</th>
            <td><?=$productPhysicalData['embargo_date']?></td>
        </tr>
        <tr>
            <th>Orchard Pricing:</th>
            <td>
                <?=$physicalPricingTierValue?>
                <br />
                <a href="/pricing/product/<?= $rel_row['release_id'] ?>/override" >Manage Pricing</a>
            </td>
        </tr>
        <tr>
            <th>Discount Policy:</th>
            <td><?=$productPhysicalData['discount']?></td>
        </tr>
        <tr>
            <th>
                Projections:
            </th>
            <td>
                <a href="/goals/?release_id=<?= $rel_row["release_id"]; ?>">
                    View/Edit Projections
                </a>
            </td>
        </tr>

        <!-- Japan Physical Fields: Japan Distribution, Japanese Release Name -->
        <? if ($isOaJapanPhysicalFieldsEnabled) {
            $japanDistributionMapping = array(
                'no' => 'No',
                'yes_only' => 'Yes, Japan Only',
                'yes_other' => 'Yes, Japan and other territories'
            );
        ?>
        <tr>
            <th>
                Japan Distribution:
            </th>
            <td>
                <?= $japanDistributionMapping[$productPhysicalData['japan_distribution']] ?>
            </td>
        </tr>
        <tr>
            <th>
                Japanese Release Name:
            </th>
            <td>
                <?=($japaneseReleaseName ? $japaneseReleaseName : 'N/A')?>
            </td>
        </tr>
        <? } ?>

    </table>
    <table class="form">
        <tr>
            <th>Box Lot:</th>
            <td><?=$productPhysicalData['box_lot']?></td>
        </tr>
        <tr>
            <th>Initial Stock:</th>
            <td><?=$productPhysicalData['initial_stock']?></td>
        </tr>
        <tr>
            <th>Explicit:</th>
            <td><?=($productPhysicalData['explicit'] === 'Y') ? 'Yes' : 'No'?></td>
        </tr>
        <tr>
            <th>Exclusive For:</th>
            <td><?=$productPhysicalData['exclusive_for']?></td>
        </tr>
        <tr>
            <th>P-Line:</th>
            <td><?=$productPhysicalData['pline']?></td>
        </tr>
        <tr>
            <th>Artist Individual?:</th>
               <td><?=(($productPhysicalData['individual'] === null) ? '' : (($productPhysicalData['individual'] === 'Y') ? 'Yes' : 'No'))?></td>
        </tr>
        <tr>
            <th>Exportable:</th>
            <td><?=(($productPhysicalData['exportable'] == null) ? '' : (($productPhysicalData['exportable'] === 'Y') ? 'Yes' : 'No'))?></td>
        </tr>
        <tr>
            <th>Manufacturing Obligation:</th>
            <td><?=(($productPhysicalData['manufacturing_obligation'] == null) ? '' : (($productPhysicalData['manufacturing_obligation'] === 'Y') ? 'Yes' : 'No'))?></td>
        </tr>
        <tr>
            <th>Country of Origin:</th>
            <td><?=(!empty($countryOfOriginData['country_of_origin']) ? $countryOfOriginData['name'] : '')?></td>
        </tr>
        <tr>
            <th>Production Notes:</th>
            <td><?=$productPhysicalData['production_notes']?></td>
        </tr>

        <!-- Japan Physical Fields: Edition, Japanese Artist Name -->
        <? if ($isOaJapanPhysicalFieldsEnabled) {
            $editionMapping = array(
                'normal_edition' => 'Normal Edition',
                'first_run_limited_edition' => 'First Run Limited Edition',
                'initial_limited_edition' => 'Inital Limited Edition',
                'limited_edition' => 'Limited Edition',
                'period_limited_edition' => 'Period Limited Edition'
            );
        ?>
        <tr>
            <th>
                Edition:
            </th>
            <td>
                <?= $productPhysicalData['edition'] == null ? 'N/A' : $editionMapping[$productPhysicalData['edition']] ?>
            </td>
        </tr>
        <tr>
            <th>
                Japanese Artist Name:
            </th>
            <td>
                <?=($japaneseArtistName ? $japaneseArtistName : 'N/A')?>
            </td>
        </tr>
        <? } ?>

    </table>
</div>
<div class="panelContent top-margin">
    <div class="panelSummary">Supply Chain Return Info</div>
    <table class="form">
        <tr>
            <th>Returnability</th>
        </tr>
        <tr>
            <th>United States:</th>
            <td><?=(($USSupplyChainInfo['returnability'] == null) ? '' : (($USSupplyChainInfo['returnability'] == 'Y') ? 'Yes' : 'No'))?></td>
        </tr>
        <tr>
            <th>Canada:</th>
            <td><?=(($canadaSupplyChainInfo['returnability'] == null) ? '' : (($canadaSupplyChainInfo['returnability'] == 'Y') ? 'Yes' : 'No'))?></td>
        </tr>
    </table>
    <table class="form">
        <tr>
            <th>Return Disposition</th>
        </tr>
        <tr>
            <th>United States:</th>
            <td><?=$USSupplyChainInfo['return_disposition']?></td>
        </tr>
        <tr>
            <th>Canada:</th>
            <td><?=$canadaSupplyChainInfo['return_disposition']?></td>
        </tr>
    </table>
</div>

<?php if ($isCCMPhysicalSupplyChainInfoOaEnabled) { ?>
<div class="panelContent top-margin supply-chain-info">
    <h5 class="panelHeader supply-chain">Supply Chain Specific Info</h5>
    <table class="form">
        <tr class="headers">
            <th>Name</th>
            <th>Sale Start Date</th>
            <th>Initial Stock</th>
        </tr>

       <?php foreach ($storeResults as $store): ?>
        <tr>
            <th><?= $store['name']; ?>:</th>
            <td>
                <?= !empty($store['sale_start_date'])
                    ? $store['sale_start_date'] 
                    : ($store['restricted'] ? '-' : '-'); ?>
            </td>
            <td>
                <?= !empty($store['initial_stock'])
                    ? $store['initial_stock'] 
                    : ($store['restricted'] ? '-' : '-'); ?>
            </td>
        </tr>
        <?php endforeach; ?>
    </table>
</div>
<?php } ?>