/*
 * Send sample for a market to the pubs database.  It will delete existing 
 * sample and replace it with whatever is determined to be the best available
 * samble for each aparmtent in the market.
 *
 * XXX: assume 'asof' defines the period for which we are replacing data. 
 * Should that assumption be invalidated, we will need to accept an argument
 * overriding the period.
 *
 * XXX: AsOf controls the extraction date but if a properites 'good' sample
 * as after asof, no sample is pulled for the property. 
 */
SELECT DropFunction('PushMarketSample');

CREATE OR REPLACE FUNCTION PushMarketSample(
  _MarketID TEXT,
  _AsOf DATE DEFAULT now()::DATE,
  _MaxSampleDays INT DEFAULT 90,
  Result OUT TEXT) RETURNS TEXT AS
$$
DECLARE
  _OutputFile TEXT;
BEGIN
  _OutputFile := format('/tmp/apartment_sample_%s_%s.sql',
    _MarketId,
    to_char(_AsOf, 'YYYYMMDD'));

  DROP TABLE IF EXISTS AptSample;

  CREATE TEMP TABLE AptSample ON COMMIT DROP AS 
    SELECT 
      ApartmentId,
      FloorplanNumber,
      msasubmkt AS SubmarketId,
      (SELECT Period FROM DataEntryPeriod) AS Sampled,
      Vacant,
      DiscountedLowRent,
      DiscountedHighRent,
      LowRent,
      HighRent,
      LowDiscountRate,
      HighDiscountRate,
      Remarks, /* Is there a spot for remarks in MPF? */
      TotalUnits,
      UnitCount,
      IncludeInAggregations,
      Rent,
      Occupancy
    FROM vw_ApartmentSample aq
    WHERE 
      Current
      AND Sampled::DATE BETWEEN (_AsOf - _MaxSampleDays) AND _AsOf
      AND _MarketID = msa
      AND DataSource = 'Survey';

  /* XXX: write out the sql script because we are stupidly prohibited from 
   * using pl/pgsql variables embedded into sql statements when using COPY.
   */
  DROP TABLE IF EXISTS Script;
  CREATE TEMP TABLE Script ON COMMIT DROP AS 
    SELECT 'begin transaction'
    UNION ALL SELECT 
      format(
        'DELETE tblAptDet '
        'FROM tblAptDet d '
        'JOIN tblApt a ON a.id = d.id '
        'WHERE '
        '  a.msa = %s '
        '  AND d.Period = %s',
        quote_literal(_MarketID),
        (SELECT Period FROM DataEntryPeriod))
    UNION ALL SELECT format(
      'INSERT INTO tblaptdet('
        'Id, PlanNumber, Period, Vacant, DLoRate, DHiRate, '
        'FLoRate, FHiRate, LoDiscount, HiDiscount, Remarks, Quantity, '
        'UpDtoHistory) '
      'VALUES('
        '%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)',
        quote_nullable(ApartmentId),
        quote_nullable(FloorplanNumber),
        quote_nullable(Sampled),
        quote_nullable(Vacant),
        quote_nullable(DiscountedLowRent),
        quote_nullable(DiscountedHighRent),
        quote_nullable(LowRent),
        quote_nullable(HighRent),
        quote_nullable(LowDiscountRate),
        quote_nullable(HighDiscountRate),
        quote_nullable(Remarks),
        quote_nullable(UnitCount),
        CASE WHEN IncludeInAggregations THEN 1 ELSE 0 END)
    FROM AptSample
    UNION ALL SELECT 'commit transaction'
    UNION ALL SELECT 'go';

  EXECUTE 'COPY Script TO ' || quote_literal(_OutputFile);

  /* push in the new sample! */
  SELECT sqshf(ConnectTo('MPFUI'), _OutputFile) INTO Result;

  PERFORM shexec('rm -f ' || _OutputFile);

  DELETE FROM MarketPublishTracking WHERE 
    MarketId = _MarketID
    AND Period =(SELECT Period FROM DataEntryPeriod);

  DELETE FROM SubmarketPublishTracking WHERE 
    MarketId = _MarketID
    AND Period = (SELECT Period FROM DataEntryPeriod);

  DROP TABLE IF EXISTS ApartmentPublish;
  CREATE TEMP TABLE ApartmentPublish ON COMMIT DROP AS
  SELECT  
    ApartmentId,
    TotalUnits,
    SUM(Rent * UnitCount) / NULLIF(SUM(UnitCount), 0) AS Rent,
    SUM(Occupancy * UnitCount) / NULLIF(SUM(UnitCount), 0) AS Occupancy
  FROM AptSample
  GROUP BY 1, 2;

  DROP TABLE IF EXISTS ApartmentPublishBySubmkt;
  CREATE TEMP TABLE ApartmentPublishBySubmkt ON COMMIT DROP AS
  SELECT  
    ApartmentId,
    TotalUnits,
    SubmarketId,
    SUM(Rent * UnitCount) / NULLIF(SUM(UnitCount), 0) AS Rent,
    SUM(Occupancy * UnitCount) / NULLIF(SUM(UnitCount), 0) AS Occupancy
  FROM AptSample
  GROUP BY 1, 2, 3;

  INSERT INTO MarketPublishTracking(Period, MarketId, PropertyCount, UnitCount) 
  SELECT
    (SELECT Period FROM DataEntryPeriod),
    _MarketId,
    PropertyCount,
    UnitCount
  FROM
  (
    SELECT 
      SUM(TotalUnits) AS UnitCount,
      COUNT(*) AS PropertyCount
    FROM ApartmentPublish  
  ) q;

  INSERT INTO SubmarketPublishTracking(Period, MarketId, SubmarketId, PropertyCount, UnitCount) 
  SELECT
    (SELECT Period FROM DataEntryPeriod),
    _MarketId,
    SubmarketId,
    PropertyCount,
    UnitCount
  FROM
  (
    SELECT 
      SubmarketId,
      SUM(TotalUnits) AS UnitCount,
      COUNT(*) AS PropertyCount
    FROM ApartmentPublishBySubmkt
    GROUP BY 1
  ) q;

  INSERT INTO MarketPublishApartmentTracking(Period, MarketId, ApartmentId,
    TotalUnits, Rent, Occupancy)
  SELECT
    (SELECT Period FROM DataEntryPeriod),
    _MarketId,    
    ApartmentId,
    TotalUnits,
    Rent,
    Occupancy
  FROM ApartmentPublish;

  INSERT INTO SubmarketPublishApartmentTracking(Period, MarketId, SubmarketId, ApartmentId,
    TotalUnits, Rent, Occupancy)
    SELECT
      (SELECT Period FROM DataEntryPeriod),
      _MarketId::TEXT AS MarketId,  
      SubmarketId,  
      ApartmentId,
      TotalUnits,
      Rent,
      Occupancy
    FROM ApartmentPublishBySubmkt;

  UPDATE MarketPublishTracking SET
    AnnualRentGrowth = q.AnnualRentGrowth,
    AnnualOccupancyGrowth = q.AnnualOccupancyGrowth,
    QuarterlyRentGrowth = q.QuarterlyRentGrowth,
    QuarterlyOccupancyGrowth = q.QuarterlyOccupancyGrowth,
    AnnualUnitCount = q.AnnualUnitCount,
    QuarterlyUnitCount = q.QuarterlyUnitCount,
    AnnualPropertyCount = q.AnnualPropertyCount,
    QuarterlyPropertyCount = q.QuarterlyPropertyCount
  FROM
  (
    SELECT 
      (a.Rent / a.PrevRent::FLOAT8) - 1.0 AS AnnualRentGrowth,
      (a.Occupancy - a.PrevOccupancy::FLOAT8) * 100  AS AnnualOccupancyGrowth,
      (q.Rent / q.PrevRent::FLOAT8) - 1.0 AS QuarterlyRentGrowth,
      (q.Occupancy - q.PrevOccupancy::FLOAT8) * 100 AS QuarterlyOccupancyGrowth,
      a.PropertyCount AS AnnualPropertyCount,
      q.PropertyCount AS QuarterlyPropertyCount,
      a.UnitCount AS AnnualUnitCount,
      q.UnitCount AS QuarterlyUnitCount
    FROM
    (
      SELECT
        SUM(cur.Rent * cur.TotalUnits) 
          / NULLIF(SUM(cur.TotalUnits), 0) AS Rent,
        SUM(prev.Rent * cur.TotalUnits) 
          / NULLIF(SUM(cur.TotalUnits), 0) AS PrevRent,
        SUM(cur.Occupancy * cur.TotalUnits) 
          / NULLIF(SUM(cur.TotalUnits), 0) AS Occupancy,
        SUM(prev.Occupancy * cur.TotalUnits) 
          / NULLIF(SUM(cur.TotalUnits), 0) AS PrevOccupancy,
        COUNT(*) AS PropertyCount,
        SUM(cur.TotalUnits) AS UnitCount
      FROM MarketPublishApartmentTracking cur
      JOIN MarketPublishApartmentTracking prev ON 
        cur.MarketId = prev.MarketId 
        AND LastPeriodYear(cur.Period) = prev.Period 
        AND cur.ApartmentId = prev.ApartmentId
      WHERE 
        cur.MarketId = _MarketId
        AND cur.Period = (SELECT Period FROM DataEntryPeriod)
    ) a,
    (
      SELECT 
        SUM(cur.Rent * cur.TotalUnits) 
          / NULLIF(SUM(cur.TotalUnits), 0) AS Rent,
        SUM(prev.Rent * cur.TotalUnits) 
          / NULLIF(SUM(cur.TotalUnits), 0) AS PrevRent,
        SUM(cur.Occupancy * cur.TotalUnits) 
          / NULLIF(SUM(cur.TotalUnits), 0) AS Occupancy,
        SUM(prev.Occupancy * cur.TotalUnits) 
          / NULLIF(SUM(cur.TotalUnits), 0) AS PrevOccupancy,
        COUNT(*) AS PropertyCount,
        SUM(cur.TotalUnits) AS UnitCount
      FROM MarketPublishApartmentTracking cur
      JOIN MarketPublishApartmentTracking prev ON 
        cur.MarketId = prev.MarketId 
        AND LastPeriod(cur.Period) = prev.Period 
        AND cur.ApartmentId = prev.ApartmentId
      WHERE 
        cur.MarketId = _MarketId
        AND cur.Period = (SELECT Period FROM DataEntryPeriod)
    ) q  
  ) q
  WHERE 
    Period = (SELECT Period FROM DataEntryPeriod)
    AND MarketId = _MarketId;

  UPDATE SubmarketPublishTracking SET
    AnnualRentGrowth = q.AnnualRentGrowth,
    AnnualOccupancyGrowth = q.AnnualOccupancyGrowth,
    QuarterlyRentGrowth = q.QuarterlyRentGrowth,
    QuarterlyOccupancyGrowth = q.QuarterlyOccupancyGrowth,
    AnnualUnitCount = q.AnnualUnitCount,
    QuarterlyUnitCount = q.QuarterlyUnitCount,
    AnnualPropertyCount = q.AnnualPropertyCount,
    QuarterlyPropertyCount = q.QuarterlyPropertyCount
  FROM
  (
    SELECT 
      a.SubmarketId,
      (a.Rent / a.PrevRent::FLOAT8) - 1.0 AS AnnualRentGrowth,
      (a.Occupancy - a.PrevOccupancy::FLOAT8) * 100  AS AnnualOccupancyGrowth,
      (q.Rent / q.PrevRent::FLOAT8) - 1.0 AS QuarterlyRentGrowth,
      (q.Occupancy - q.PrevOccupancy::FLOAT8) * 100 AS QuarterlyOccupancyGrowth,
      a.PropertyCount AS AnnualPropertyCount,
      q.PropertyCount AS QuarterlyPropertyCount,
      a.UnitCount AS AnnualUnitCount,
      q.UnitCount AS QuarterlyUnitCount
    FROM
    (
      SELECT
        cur.SubmarketId,
        SUM(cur.Rent * cur.TotalUnits) 
          / NULLIF(SUM(cur.TotalUnits), 0) AS Rent,
        SUM(prev.Rent * cur.TotalUnits) 
          / NULLIF(SUM(cur.TotalUnits), 0) AS PrevRent,
        SUM(cur.Occupancy * cur.TotalUnits) 
          / NULLIF(SUM(cur.TotalUnits), 0) AS Occupancy,
        SUM(prev.Occupancy * cur.TotalUnits) 
          / NULLIF(SUM(cur.TotalUnits), 0) AS PrevOccupancy,
        COUNT(*) AS PropertyCount,
        SUM(cur.TotalUnits) AS UnitCount
      FROM SubmarketPublishApartmentTracking cur
      JOIN SubmarketPublishApartmentTracking prev ON 
        cur.MarketId = prev.MarketId 
        AND cur.SubmarketId = prev.SubmarketId
        AND LastPeriodYear(cur.Period) = prev.Period 
        AND cur.ApartmentId = prev.ApartmentId
      WHERE 
        cur.MarketId = _MarketId
        AND cur.Period = (SELECT Period FROM DataEntryPeriod)
      GROUP BY cur.SubmarketId
    ) a,
    (
      SELECT
        cur.SubmarketId, 
        SUM(cur.Rent * cur.TotalUnits) 
          / NULLIF(SUM(cur.TotalUnits), 0) AS Rent,
        SUM(prev.Rent * cur.TotalUnits) 
          / NULLIF(SUM(cur.TotalUnits), 0) AS PrevRent,
        SUM(cur.Occupancy * cur.TotalUnits) 
          / NULLIF(SUM(cur.TotalUnits), 0) AS Occupancy,
        SUM(prev.Occupancy * cur.TotalUnits) 
          / NULLIF(SUM(cur.TotalUnits), 0) AS PrevOccupancy,
        COUNT(*) AS PropertyCount,
        SUM(cur.TotalUnits) AS UnitCount
      FROM SubmarketPublishApartmentTracking cur
      JOIN SubmarketPublishApartmentTracking prev ON 
        cur.MarketId = prev.MarketId
        AND cur.SubmarketId = prev.SubmarketId
        AND LastPeriod(cur.Period) = prev.Period 
        AND cur.ApartmentId = prev.ApartmentId
      WHERE 
        cur.MarketId = _MarketId
        AND cur.Period = (SELECT Period FROM DataEntryPeriod)
      GROUP BY cur.SubmarketId
    ) q 
    WHERE a.SubmarketId = q.SubmarketId 
  ) q
  WHERE 
    Period = (SELECT Period FROM DataEntryPeriod)
    AND MarketId = _MarketId
    AND SubmarketPublishTracking.SubmarketId = q.SubmarketId;

EXCEPTION
  WHEN OTHERS THEN 
    PERFORM shexec('rm -f ' || _OutputFile);
    RAISE;
END;
$$ LANGUAGE PLPGSQL;

