-- Target: MySQL 8.0+ / MariaDB 10.6+, InnoDB, utf8mb4.
-- UUID values are generated by the application and stored as BINARY(16).
SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE users (
  id BINARY(16) NOT NULL,
  email VARCHAR(254) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  display_name VARCHAR(80) NOT NULL,
  status VARCHAR(24) NOT NULL DEFAULT 'pending_email',
  email_verified_at DATETIME(6) NULL,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id),
  UNIQUE KEY users_email_unique (email),
  CONSTRAINT users_status_check CHECK (status IN ('pending_email','active','suspended','closed')),
  CONSTRAINT users_email_normalized CHECK (email = LOWER(TRIM(email)))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE staff_roles (
  id BINARY(16) NOT NULL,
  name VARCHAR(80) NOT NULL,
  description VARCHAR(500) NOT NULL DEFAULT '',
  is_owner BOOLEAN NOT NULL DEFAULT FALSE,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id), UNIQUE KEY staff_roles_name_unique (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE permissions (
  code VARCHAR(120) NOT NULL, description VARCHAR(255) NOT NULL,
  PRIMARY KEY (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE staff_role_permissions (
  role_id BINARY(16) NOT NULL, permission_code VARCHAR(120) NOT NULL,
  PRIMARY KEY (role_id, permission_code),
  CONSTRAINT srp_role_fk FOREIGN KEY (role_id) REFERENCES staff_roles(id) ON DELETE CASCADE,
  CONSTRAINT srp_permission_fk FOREIGN KEY (permission_code) REFERENCES permissions(code) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE staff_memberships (
  user_id BINARY(16) NOT NULL, role_id BINARY(16) NOT NULL,
  active BOOLEAN NOT NULL DEFAULT TRUE, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (user_id), KEY staff_memberships_role_idx (role_id),
  CONSTRAINT staff_user_fk FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT,
  CONSTRAINT staff_role_fk FOREIGN KEY (role_id) REFERENCES staff_roles(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE sessions (
  id BINARY(16) NOT NULL, user_id BINARY(16) NOT NULL,
  token_hash BINARY(32) NOT NULL, csrf_secret_hash BINARY(32) NOT NULL,
  expires_at DATETIME(6) NOT NULL, rotated_from BINARY(16) NULL,
  revoked_at DATETIME(6) NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  last_seen_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id), UNIQUE KEY sessions_token_hash_unique (token_hash),
  KEY sessions_user_active_idx (user_id, revoked_at, expires_at),
  CONSTRAINT sessions_user_fk FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT sessions_rotated_fk FOREIGN KEY (rotated_from) REFERENCES sessions(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE seller_kyc_submissions (
  id BINARY(16) NOT NULL, seller_id BINARY(16) NOT NULL, sequence_no INT UNSIGNED NOT NULL,
  status VARCHAR(16) NOT NULL DEFAULT 'draft', challenge_date DATE NOT NULL,
  submitted_at DATETIME(6) NULL, reviewed_at DATETIME(6) NULL, reviewed_by BINARY(16) NULL,
  rejection_category VARCHAR(80) NULL, rejection_reason VARCHAR(500) NULL,
  pending_seller_id BINARY(16) GENERATED ALWAYS AS (CASE WHEN status = 'pending' THEN seller_id ELSE NULL END) STORED,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id), UNIQUE KEY seller_kyc_sequence_unique (seller_id, sequence_no),
  UNIQUE KEY seller_one_pending_kyc (pending_seller_id), KEY seller_kyc_reviewer_idx (reviewed_by),
  CONSTRAINT seller_kyc_seller_fk FOREIGN KEY (seller_id) REFERENCES users(id) ON DELETE RESTRICT,
  CONSTRAINT seller_kyc_reviewer_fk FOREIGN KEY (reviewed_by) REFERENCES users(id) ON DELETE RESTRICT,
  CONSTRAINT seller_kyc_status_check CHECK (status IN ('draft','pending','approved','rejected')),
  CONSTRAINT seller_kyc_sequence_check CHECK (sequence_no > 0),
  CONSTRAINT seller_kyc_review_shape CHECK (
    (status IN ('draft','pending') AND reviewed_at IS NULL AND reviewed_by IS NULL)
    OR (status = 'approved' AND reviewed_at IS NOT NULL AND reviewed_by IS NOT NULL AND rejection_reason IS NULL)
    OR (status = 'rejected' AND reviewed_at IS NOT NULL AND reviewed_by IS NOT NULL AND rejection_reason IS NOT NULL)
  )
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE seller_kyc_evidence (
  id BINARY(16) NOT NULL, submission_id BINARY(16) NOT NULL,
  evidence_type VARCHAR(32) NOT NULL, private_object_key VARCHAR(512) NOT NULL,
  sha256 BINARY(32) NOT NULL, media_type VARCHAR(32) NOT NULL, size_bytes BIGINT UNSIGNED NOT NULL,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id), UNIQUE KEY kyc_private_key_unique (private_object_key),
  UNIQUE KEY kyc_evidence_type_unique (submission_id, evidence_type),
  CONSTRAINT kyc_evidence_submission_fk FOREIGN KEY (submission_id) REFERENCES seller_kyc_submissions(id) ON DELETE RESTRICT,
  CONSTRAINT kyc_evidence_type_check CHECK (evidence_type IN ('id_front','id_back','challenge_selfie')),
  CONSTRAINT kyc_evidence_media_check CHECK (media_type IN ('image/jpeg','image/png')),
  CONSTRAINT kyc_evidence_size_check CHECK (size_bytes BETWEEN 1 AND 10485760)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE audit_events (
  id BINARY(16) NOT NULL, sequence_no BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  event_type VARCHAR(120) NOT NULL, actor_user_id BINARY(16) NULL,
  subject_type VARCHAR(80) NOT NULL, subject_id BINARY(16) NULL, request_id BINARY(16) NULL,
  data JSON NOT NULL, occurred_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (sequence_no), UNIQUE KEY audit_events_id_unique (id),
  KEY audit_subject_idx (subject_type, subject_id, occurred_at),
  CONSTRAINT audit_actor_fk FOREIGN KEY (actor_user_id) REFERENCES users(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE notification_outbox (
  id BINARY(16) NOT NULL, event_key VARCHAR(190) NOT NULL, recipient_user_id BINARY(16) NOT NULL,
  template VARCHAR(40) NOT NULL, payload JSON NOT NULL, attempts INT UNSIGNED NOT NULL DEFAULT 0,
  available_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), sent_at DATETIME(6) NULL,
  last_error_code VARCHAR(120) NULL, created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id), UNIQUE KEY outbox_event_key_unique (event_key),
  KEY outbox_pending_idx (sent_at, available_at),
  CONSTRAINT outbox_recipient_fk FOREIGN KEY (recipient_user_id) REFERENCES users(id) ON DELETE RESTRICT,
  CONSTRAINT outbox_template_check CHECK (template IN ('kyc_approved','kyc_rejected','new_sale'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO permissions(code, description) VALUES
('admin.dashboard.view','View restricted staff dashboard'),('admin.kyc.review','Review seller identity submissions'),
('admin.kyc.evidence.view','View private KYC evidence'),('admin.users.manage','Manage users'),
('admin.payments.manage','Manage payments'),('admin.payouts.manage','Manage payouts'),
('admin.listings.moderate','Moderate listings'),('admin.disputes.resolve','Resolve disputes'),
('admin.configuration.manage','Manage configuration'),('admin.staff.manage','Manage staff'),
('admin.roles.manage','Manage staff roles');