-- PostgreSQL schema for the Marketing Department module.
-- Requires the shared campuses(id, name, description) table.
BEGIN;

CREATE TABLE IF NOT EXISTS mkt_service_centers (
  id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  campus_id INTEGER NOT NULL REFERENCES campuses(id) ON DELETE RESTRICT,
  center_name VARCHAR(150) NOT NULL,
  center_code VARCHAR(30) NOT NULL UNIQUE,
  location VARCHAR(180),
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  created_by BIGINT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE (campus_id, center_name)
);

CREATE TABLE IF NOT EXISTS mkt_agents (
  id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  center_id BIGINT NOT NULL REFERENCES mkt_service_centers(id) ON DELETE RESTRICT,
  agent_name VARCHAR(150) NOT NULL,
  employee_number VARCHAR(40),
  email VARCHAR(180),
  contact_number VARCHAR(30),
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  created_by BIGINT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE (center_id, employee_number)
);

CREATE TABLE IF NOT EXISTS mkt_agent_phones (
  id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  agent_id BIGINT NOT NULL REFERENCES mkt_agents(id) ON DELETE RESTRICT,
  phone_number VARCHAR(30) NOT NULL UNIQUE,
  device_model VARCHAR(100),
  asset_tag VARCHAR(50) UNIQUE,
  issued_on DATE NOT NULL DEFAULT CURRENT_DATE,
  returned_on DATE,
  status VARCHAR(20) NOT NULL DEFAULT 'assigned' CHECK (status IN ('assigned','returned','repair','retired')),
  notes TEXT,
  created_by BIGINT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CHECK (returned_on IS NULL OR returned_on >= issued_on)
);

CREATE TABLE IF NOT EXISTS mkt_monthly_performance (
  id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  center_id BIGINT NOT NULL REFERENCES mkt_service_centers(id) ON DELETE RESTRICT,
  report_year SMALLINT NOT NULL CHECK (report_year BETWEEN 2000 AND 2200),
  report_month SMALLINT NOT NULL CHECK (report_month BETWEEN 1 AND 12),
  student_inquiries INTEGER NOT NULL DEFAULT 0 CHECK (student_inquiries >= 0),
  applications_started INTEGER NOT NULL DEFAULT 0 CHECK (applications_started >= 0),
  students_enrolled INTEGER NOT NULL DEFAULT 0 CHECK (students_enrolled >= 0),
  enrollment_target INTEGER NOT NULL DEFAULT 0 CHECK (enrollment_target >= 0),
  outreach_activities INTEGER NOT NULL DEFAULT 0 CHECK (outreach_activities >= 0),
  notes TEXT,
  created_by BIGINT,
  updated_by BIGINT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE (center_id, report_year, report_month)
);

CREATE TABLE IF NOT EXISTS mkt_monthly_reports (
  id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  report_year SMALLINT NOT NULL,
  report_month SMALLINT NOT NULL CHECK (report_month BETWEEN 1 AND 12),
  report_data JSONB NOT NULL,
  generated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE (report_year, report_month)
);

CREATE INDEX IF NOT EXISTS mkt_centers_campus_idx ON mkt_service_centers(campus_id, is_active);
CREATE INDEX IF NOT EXISTS mkt_agents_center_idx ON mkt_agents(center_id, is_active);
CREATE INDEX IF NOT EXISTS mkt_phones_agent_status_idx ON mkt_agent_phones(agent_id, status);
CREATE INDEX IF NOT EXISTS mkt_performance_period_idx ON mkt_monthly_performance(report_year DESC, report_month DESC, center_id);


-- Called by pg_cron on the last day of the month (optional, see README).
-- The API also creates the prior month's snapshot on the first request next month.
CREATE OR REPLACE FUNCTION mkt_close_current_month_report()
RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
  report_year_value SMALLINT := EXTRACT(YEAR FROM CURRENT_DATE)::SMALLINT;
  report_month_value SMALLINT := EXTRACT(MONTH FROM CURRENT_DATE)::SMALLINT;
BEGIN
  IF date_trunc('month', CURRENT_DATE + 1) = date_trunc('month', CURRENT_DATE) THEN
    RETURN;
  END IF;

  WITH center_rows AS (
    SELECT c.id AS center_id, c.center_name, c.center_code, cp.name AS campus_name,
           COALESCE(p.student_inquiries,0)::INTEGER AS student_inquiries,
           COALESCE(p.applications_started,0)::INTEGER AS applications_started,
           COALESCE(p.students_enrolled,0)::INTEGER AS students_enrolled,
           COALESCE(p.enrollment_target,0)::INTEGER AS enrollment_target,
           COALESCE(p.outreach_activities,0)::INTEGER AS outreach_activities,
           CASE WHEN COALESCE(p.enrollment_target,0)=0 THEN NULL
                ELSE ROUND(100.0*p.students_enrolled/NULLIF(p.enrollment_target,0),1) END AS target_attainment
    FROM mkt_service_centers c
    JOIN campuses cp ON cp.id=c.campus_id
    LEFT JOIN mkt_monthly_performance p ON p.center_id=c.id
      AND p.report_year=report_year_value AND p.report_month=report_month_value
    WHERE c.is_active=TRUE
  ), totals AS (
    SELECT COUNT(*)::INTEGER AS center_count,
           COALESCE(SUM(student_inquiries),0)::INTEGER AS student_inquiries,
           COALESCE(SUM(applications_started),0)::INTEGER AS applications_started,
           COALESCE(SUM(students_enrolled),0)::INTEGER AS students_enrolled,
           COALESCE(SUM(enrollment_target),0)::INTEGER AS enrollment_target,
           COALESCE(SUM(outreach_activities),0)::INTEGER AS outreach_activities
    FROM center_rows
  ), payload AS (
    SELECT jsonb_build_object(
      'summary', jsonb_build_object(
        'year',report_year_value,'month',report_month_value,'center_count',t.center_count,
        'student_inquiries',t.student_inquiries,'applications_started',t.applications_started,
        'students_enrolled',t.students_enrolled,'enrollment_target',t.enrollment_target,
        'outreach_activities',t.outreach_activities,
        'target_attainment',CASE WHEN t.enrollment_target=0 THEN NULL ELSE ROUND(100.0*t.students_enrolled/t.enrollment_target,1) END,
        'top_center',(SELECT jsonb_build_object('id',r.center_id,'name',r.center_name,'campus',r.campus_name,
                       'students_enrolled',r.students_enrolled,'target_attainment',r.target_attainment)
                      FROM center_rows r
                      WHERE t.student_inquiries+t.applications_started+t.students_enrolled+t.enrollment_target+t.outreach_activities>0
                      ORDER BY r.students_enrolled DESC,r.center_name LIMIT 1)
      ),
      'centers',COALESCE((SELECT jsonb_agg(to_jsonb(r) ORDER BY r.students_enrolled DESC,r.center_name) FROM center_rows r),'[]'::jsonb)
    ) AS report_data
    FROM totals t
  )
  INSERT INTO mkt_monthly_reports(report_year,report_month,report_data)
  SELECT report_year_value,report_month_value,report_data FROM payload
  ON CONFLICT(report_year,report_month)
  DO UPDATE SET report_data=EXCLUDED.report_data,generated_at=CURRENT_TIMESTAMP;
END;
$$;

COMMIT;
