package com.davos.assessment.project;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.nio.charset.StandardCharsets;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

@Service
public class ProjectDataExportService {
  private static final ZoneId SHANGHAI = ZoneId.of("Asia/Shanghai");
  private static final DateTimeFormatter STAMP = DateTimeFormatter.ofPattern("yyyyMMdd-HHmmss");

  private final JdbcTemplate jdbcTemplate;

  public ProjectDataExportService(JdbcTemplate jdbcTemplate) {
    this.jdbcTemplate = jdbcTemplate;
  }

  public byte[] exportProject(Project project) throws IOException {
    Long projectId = project.getId();
    try (ByteArrayOutputStream out = new ByteArrayOutputStream();
         ZipOutputStream zip = new ZipOutputStream(out, StandardCharsets.UTF_8)) {
      addManifest(zip, project);
      addCsv(zip, "projects.csv",
          List.of("id", "slug", "name", "landing_title", "landing_description", "report_title", "logo_url", "enabled", "created_by", "created_by_email", "created_at", "updated_at"),
          """
          select p.id, p.slug, p.name, p.landing_title, p.landing_description, p.report_title, p.logo_url, p.enabled,
                 p.created_by, creator.email as created_by_email, p.created_at, p.updated_at
          from projects p
          left join users creator on creator.id = p.created_by
          where p.id = ?
          order by p.id
          """,
          projectId);
      addCsv(zip, "users.csv",
          List.of("id", "email", "password_hash", "global_role", "user_enabled", "user_created_at", "project_role", "membership_enabled", "membership_created_at"),
          """
          select u.id, u.email, '[redacted]' as password_hash, u.role as global_role, u.enabled as user_enabled, u.created_at as user_created_at,
                 pm.role as project_role, pm.enabled as membership_enabled, pm.created_at as membership_created_at
          from project_memberships pm
          join users u on u.id = pm.user_id
          where pm.project_id = ?
          order by pm.role, u.email
          """,
          projectId);
      addCsv(zip, "project_memberships.csv",
          List.of("id", "project_id", "project_slug", "user_id", "email", "global_role", "user_enabled", "project_role", "membership_enabled", "created_at"),
          """
          select pm.id, pm.project_id, p.slug as project_slug, pm.user_id, u.email, u.role as global_role, u.enabled as user_enabled,
                 pm.role as project_role, pm.enabled as membership_enabled, pm.created_at
          from project_memberships pm
          join projects p on p.id = pm.project_id
          join users u on u.id = pm.user_id
          where pm.project_id = ?
          order by pm.role, u.email
          """,
          projectId);
      addCsv(zip, "project_assessments.csv",
          List.of("id", "project_id", "project_slug", "assessment_id", "test_paper_id", "title", "description", "assessment_active", "project_assessment_active"),
          """
          select pa.id, pa.project_id, p.slug as project_slug, pa.assessment_id, a.test_paper_id, a.title, a.description,
                 a.active as assessment_active, pa.active as project_assessment_active
          from project_assessments pa
          join projects p on p.id = pa.project_id
          join assessments a on a.id = pa.assessment_id
          where pa.project_id = ?
          order by a.test_paper_id
          """,
          projectId);
      addCsv(zip, "assessment_questions.csv",
          List.of("question_id", "project_id", "project_slug", "assessment_id", "test_paper_id", "assessment_title", "question_code", "dimension", "prompt", "sort_order", "assessment_active", "project_assessment_active"),
          """
          select q.id as question_id, pa.project_id, p.slug as project_slug, a.id as assessment_id, a.test_paper_id,
                 a.title as assessment_title, q.code as question_code, q.dimension, q.prompt, q.sort_order,
                 a.active as assessment_active, pa.active as project_assessment_active
          from project_assessments pa
          join projects p on p.id = pa.project_id
          join assessments a on a.id = pa.assessment_id
          join questions q on q.assessment_id = a.id
          where pa.project_id = ?
          order by a.test_paper_id, q.sort_order
          """,
          projectId);
      addCsv(zip, "profiles.csv",
          List.of("id", "project_id", "project_slug", "user_id", "email", "name", "gender", "id_card", "phone", "birth_month", "education", "school_major", "organization", "title", "industry", "city", "attendee_type", "updated_at"),
          """
          select pr.id, pr.project_id, p.slug as project_slug, pr.user_id, u.email, pr.name, pr.gender, pr.id_card, pr.phone,
                 pr.birth_month, pr.education, pr.school_major, pr.organization, pr.title, pr.industry, pr.city, pr.attendee_type, pr.updated_at
          from profiles pr
          join projects p on p.id = pr.project_id
          join users u on u.id = pr.user_id
          where pr.project_id = ?
          order by pr.updated_at desc, pr.id
          """,
          projectId);
      addCsv(zip, "invitation_codes.csv",
          List.of("id", "project_id", "project_slug", "code", "max_uses", "used_count", "enabled", "expires_at", "created_at"),
          """
          select ic.id, ic.project_id, p.slug as project_slug, ic.code, ic.max_uses, ic.used_count, ic.enabled, ic.expires_at, ic.created_at
          from invitation_codes ic
          join projects p on p.id = ic.project_id
          where ic.project_id = ?
          order by ic.created_at desc, ic.id
          """,
          projectId);
      addCsv(zip, "submissions.csv",
          List.of("id", "project_id", "project_slug", "user_id", "email", "assessment_id", "test_paper_id", "assessment_title", "raw_score", "level", "dimension_scores", "submitted_at"),
          """
          select s.id, s.project_id, p.slug as project_slug, s.user_id, u.email, s.assessment_id, a.test_paper_id,
                 a.title as assessment_title, s.raw_score, s.level, s.dimension_scores, s.submitted_at
          from submissions s
          join projects p on p.id = s.project_id
          join users u on u.id = s.user_id
          join assessments a on a.id = s.assessment_id
          where s.project_id = ?
          order by s.submitted_at desc, s.id
          """,
          projectId);
      addCsv(zip, "answers.csv",
          List.of("id", "project_id", "project_slug", "submission_id", "user_id", "email", "assessment_id", "test_paper_id", "question_id", "question_code", "dimension", "prompt", "sort_order", "answer_value", "submitted_at"),
          """
          select ans.id, s.project_id, p.slug as project_slug, ans.submission_id, s.user_id, u.email, s.assessment_id,
                 a.test_paper_id, ans.question_id, q.code as question_code, q.dimension, q.prompt, q.sort_order,
                 ans.answer_value, s.submitted_at
          from answers ans
          join submissions s on s.id = ans.submission_id
          join projects p on p.id = s.project_id
          join users u on u.id = s.user_id
          join assessments a on a.id = s.assessment_id
          join questions q on q.id = ans.question_id
          where s.project_id = ?
          order by s.submitted_at desc, ans.submission_id, q.sort_order
          """,
          projectId);
      addCsv(zip, "reports.csv",
          List.of("id", "project_id", "project_slug", "submission_id", "user_id", "email", "assessment_id", "test_paper_id", "summary", "suggestions", "created_at"),
          """
          select r.id, r.project_id, p.slug as project_slug, r.submission_id, r.user_id, u.email, s.assessment_id,
                 a.test_paper_id, r.summary, r.suggestions, r.created_at
          from reports r
          join projects p on p.id = r.project_id
          join users u on u.id = r.user_id
          left join submissions s on s.id = r.submission_id
          left join assessments a on a.id = s.assessment_id
          where r.project_id = ?
          order by r.created_at desc, r.id
          """,
          projectId);
      addCsv(zip, "assessment_progress.csv",
          List.of("id", "project_id", "project_slug", "user_id", "email", "assessment_id", "test_paper_id", "answers_json", "updated_at"),
          """
          select ap.id, ap.project_id, p.slug as project_slug, ap.user_id, u.email, ap.assessment_id, a.test_paper_id,
                 ap.answers_json, ap.updated_at
          from assessment_progress ap
          join projects p on p.id = ap.project_id
          join users u on u.id = ap.user_id
          join assessments a on a.id = ap.assessment_id
          where ap.project_id = ?
          order by ap.updated_at desc, ap.id
          """,
          projectId);
      addCsv(zip, "user_assessment_access.csv",
          List.of("id", "project_id", "project_slug", "user_id", "email", "assessment_id", "test_paper_id", "can_retake", "reopened_at", "reopen_note"),
          """
          select ua.id, ua.project_id, p.slug as project_slug, ua.user_id, u.email, ua.assessment_id, a.test_paper_id,
                 ua.can_retake, ua.reopened_at, ua.reopen_note
          from user_assessment_access ua
          join projects p on p.id = ua.project_id
          join users u on u.id = ua.user_id
          join assessments a on a.id = ua.assessment_id
          where ua.project_id = ?
          order by ua.reopened_at desc nulls last, ua.id
          """,
          projectId);
      addCsv(zip, "legal_documents.csv",
          List.of("id", "project_id", "project_slug", "doc_type", "version", "title", "content_md", "updated_by", "updated_by_email", "updated_at"),
          """
          select ld.id, ld.project_id, p.slug as project_slug, ld.doc_type, ld.version, ld.title, ld.content_md,
                 ld.updated_by, u.email as updated_by_email, ld.updated_at
          from legal_documents ld
          join projects p on p.id = ld.project_id
          left join users u on u.id = ld.updated_by
          where ld.project_id = ?
          order by ld.doc_type, ld.updated_at desc
          """,
          projectId);
      addCsv(zip, "consent_logs.csv",
          List.of("id", "project_id", "project_slug", "user_id", "email", "consent_type", "consent_version", "granted", "ip_address", "user_agent", "created_at"),
          """
          select cl.id, cl.project_id, p.slug as project_slug, cl.user_id, u.email, cl.consent_type, cl.consent_version,
                 cl.granted, cl.ip_address, cl.user_agent, cl.created_at
          from consent_logs cl
          join projects p on p.id = cl.project_id
          join users u on u.id = cl.user_id
          where cl.project_id = ?
          order by cl.created_at desc, cl.id
          """,
          projectId);
      addCsv(zip, "admin_audit_logs.csv",
          List.of("id", "project_id", "project_slug", "actor_user_id", "actor_email", "action", "target_type", "target_id", "created_at"),
          """
          select log.id, log.project_id, p.slug as project_slug, log.actor_user_id, actor.email as actor_email,
                 log.action, log.target_type, log.target_id, log.created_at
          from admin_audit_logs log
          left join projects p on p.id = log.project_id
          left join users actor on actor.id = log.actor_user_id
          where log.project_id = ?
             or (log.project_id is null and log.target_type = 'PROJECT' and log.target_id = (select slug from projects where id = ?))
          order by log.created_at desc, log.id
          """,
          projectId, projectId);
      zip.finish();
      return out.toByteArray();
    }
  }

  public String filename(Project project) {
    String stamp = LocalDateTime.now(SHANGHAI).format(STAMP);
    return safeSlug(project.getSlug()) + "-all-data-" + stamp + ".zip";
  }

  private void addManifest(ZipOutputStream zip, Project project) throws IOException {
    zip.putNextEntry(new ZipEntry("README.txt"));
    String text = """
        Project all-data export
        Project: %s (%s)
        Generated at: %s

        This archive contains project-scoped CSV files exported from the platform database.
        Account password_hash values are intentionally redacted.
        CSV files are UTF-8 encoded with BOM for spreadsheet compatibility.
        """.formatted(project.getName(), project.getSlug(), LocalDateTime.now(SHANGHAI));
    zip.write(text.getBytes(StandardCharsets.UTF_8));
    zip.closeEntry();
  }

  private void addCsv(ZipOutputStream zip, String filename, List<String> headers, String sql, Object... args) throws IOException {
    zip.putNextEntry(new ZipEntry(filename));
    StringBuilder builder = new StringBuilder();
    builder.append('\ufeff');
    builder.append(headers.stream().map(ProjectDataExportService::csv).reduce((a, b) -> a + "," + b).orElse(""));
    builder.append("\n");
    List<Map<String, Object>> rows = jdbcTemplate.queryForList(sql, args);
    for (Map<String, Object> row : rows) {
      for (int i = 0; i < headers.size(); i++) {
        if (i > 0) builder.append(",");
        builder.append(csv(row.get(headers.get(i))));
      }
      builder.append("\n");
    }
    zip.write(builder.toString().getBytes(StandardCharsets.UTF_8));
    zip.closeEntry();
  }

  private static String csv(Object value) {
    String text = value == null ? "" : value.toString();
    return "\"" + text.replace("\"", "\"\"") + "\"";
  }

  private static String safeSlug(String slug) {
    String value = slug == null ? "project" : slug.toLowerCase(Locale.ROOT).replaceAll("[^a-z0-9-]", "-");
    return value.isBlank() ? "project" : value;
  }
}
