-- トライアル用キーコード配布機能 テーブル作成
-- 実行タイミング: 機能リリース時

-- ============================================
-- 1. trial_settings（代理店トライアル設定）
-- ============================================
CREATE TABLE trial_settings (
    trial_setting_id SERIAL PRIMARY KEY,
    med_no INTEGER NOT NULL,
    trial_name VARCHAR(255) NOT NULL,
    trial_start_date DATE NOT NULL,
    trial_end_date DATE NOT NULL,
    max_distribution INTEGER DEFAULT 0,
    follow_email_enabled BOOLEAN DEFAULT TRUE,
    licence_type_cd SMALLINT NOT NULL DEFAULT 1,
    purchase_url VARCHAR(500) DEFAULT '',
    install_url VARCHAR(500) DEFAULT '',
    product_logo_url VARCHAR(500) DEFAULT '',
    agent_logo_url VARCHAR(500) DEFAULT '',
    product_description TEXT DEFAULT '',
    status INTEGER DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- インデックス
CREATE INDEX idx_trial_settings_med_no ON trial_settings(med_no);
CREATE INDEX idx_trial_settings_status ON trial_settings(status);
CREATE INDEX idx_trial_settings_date_range ON trial_settings(trial_start_date, trial_end_date);

-- コメント
COMMENT ON TABLE trial_settings IS '代理店トライアル設定';
COMMENT ON COLUMN trial_settings.trial_setting_id IS '設定ID（主キー）';
COMMENT ON COLUMN trial_settings.med_no IS '代理店番号';
COMMENT ON COLUMN trial_settings.trial_name IS 'トライアル名';
COMMENT ON COLUMN trial_settings.trial_start_date IS '開始日';
COMMENT ON COLUMN trial_settings.trial_end_date IS '終了日';
COMMENT ON COLUMN trial_settings.max_distribution IS '配布上限（0=無制限）';
COMMENT ON COLUMN trial_settings.follow_email_enabled IS 'フォローメール有効';
COMMENT ON COLUMN trial_settings.licence_type_cd IS 'ライセンス種別コード';
COMMENT ON COLUMN trial_settings.purchase_url IS '購入ページURL';
COMMENT ON COLUMN trial_settings.install_url IS 'インストールURL';
COMMENT ON COLUMN trial_settings.product_logo_url IS '製品ロゴ画像URL';
COMMENT ON COLUMN trial_settings.agent_logo_url IS '代理店ロゴ画像URL';
COMMENT ON COLUMN trial_settings.product_description IS '製品紹介文';
COMMENT ON COLUMN trial_settings.status IS 'ステータス（1:有効, 0:無効）';
COMMENT ON COLUMN trial_settings.created_at IS '作成日時';
COMMENT ON COLUMN trial_settings.updated_at IS '更新日時';


-- ============================================
-- 2. trial_requests（配布リクエスト履歴）
-- ============================================
CREATE TABLE trial_requests (
    trial_request_id SERIAL PRIMARY KEY,
    trial_setting_id INTEGER NOT NULL REFERENCES trial_settings(trial_setting_id),
    med_no INTEGER NOT NULL,
    email VARCHAR(255) NOT NULL,
    licence_cd VARCHAR(50) NOT NULL,
    follow_email_flg BOOLEAN DEFAULT TRUE,
    unsubscribe_token VARCHAR(64) NOT NULL,
    ip_address VARCHAR(45),
    user_agent TEXT,
    agree_ts TIMESTAMP,
    requested_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- インデックス
CREATE INDEX idx_trial_requests_trial_setting_id ON trial_requests(trial_setting_id);
CREATE INDEX idx_trial_requests_med_no ON trial_requests(med_no);
CREATE INDEX idx_trial_requests_email ON trial_requests(email);
CREATE UNIQUE INDEX idx_trial_requests_licence_cd ON trial_requests(licence_cd);
CREATE UNIQUE INDEX idx_trial_requests_unsubscribe_token ON trial_requests(unsubscribe_token);
CREATE INDEX idx_trial_requests_requested_at ON trial_requests(requested_at);

-- コメント
COMMENT ON TABLE trial_requests IS 'トライアルキーコード配布リクエスト履歴';
COMMENT ON COLUMN trial_requests.trial_request_id IS 'リクエストID（主キー）';
COMMENT ON COLUMN trial_requests.trial_setting_id IS 'トライアル設定ID';
COMMENT ON COLUMN trial_requests.med_no IS '代理店番号';
COMMENT ON COLUMN trial_requests.email IS 'メールアドレス';
COMMENT ON COLUMN trial_requests.licence_cd IS '配布キーコード';
COMMENT ON COLUMN trial_requests.follow_email_flg IS 'フォローメール配信受信フラグ';
COMMENT ON COLUMN trial_requests.unsubscribe_token IS '配信停止トークン';
COMMENT ON COLUMN trial_requests.ip_address IS 'IPアドレス';
COMMENT ON COLUMN trial_requests.user_agent IS 'ユーザーエージェント';
COMMENT ON COLUMN trial_requests.agree_ts IS '利用規約同意日時';
COMMENT ON COLUMN trial_requests.requested_at IS 'リクエスト日時';


-- ============================================
-- 3. trial_email_templates（フォローメールテンプレート）
-- ============================================
CREATE TABLE trial_email_templates (
    template_id SERIAL PRIMARY KEY,
    med_no INTEGER NOT NULL,
    trial_setting_id INTEGER REFERENCES trial_settings(trial_setting_id),
    template_name VARCHAR(255) NOT NULL,
    send_days_after INTEGER NOT NULL,
    email_subject VARCHAR(255) NOT NULL,
    email_body TEXT NOT NULL,
    status INTEGER DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- インデックス
CREATE INDEX idx_trial_email_templates_med_no ON trial_email_templates(med_no);
CREATE INDEX idx_trial_email_templates_trial_setting_id ON trial_email_templates(trial_setting_id);
CREATE INDEX idx_trial_email_templates_status ON trial_email_templates(status);
CREATE INDEX idx_trial_email_templates_send_days ON trial_email_templates(send_days_after);

-- コメント
COMMENT ON TABLE trial_email_templates IS 'トライアルフォローメールテンプレート';
COMMENT ON COLUMN trial_email_templates.template_id IS 'テンプレートID（主キー）';
COMMENT ON COLUMN trial_email_templates.med_no IS '代理店番号';
COMMENT ON COLUMN trial_email_templates.trial_setting_id IS 'トライアル設定ID（NULL=全設定共通）';
COMMENT ON COLUMN trial_email_templates.template_name IS 'テンプレート名';
COMMENT ON COLUMN trial_email_templates.send_days_after IS '配布後N日後に送信';
COMMENT ON COLUMN trial_email_templates.email_subject IS '件名';
COMMENT ON COLUMN trial_email_templates.email_body IS '本文';
COMMENT ON COLUMN trial_email_templates.status IS 'ステータス（1:有効, 0:無効）';
COMMENT ON COLUMN trial_email_templates.created_at IS '作成日時';
COMMENT ON COLUMN trial_email_templates.updated_at IS '更新日時';


-- ============================================
-- 4. trial_follow_emails（送信履歴）
-- ============================================
CREATE TABLE trial_follow_emails (
    follow_email_id SERIAL PRIMARY KEY,
    trial_request_id INTEGER NOT NULL REFERENCES trial_requests(trial_request_id),
    template_id INTEGER NOT NULL REFERENCES trial_email_templates(template_id),
    sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status INTEGER DEFAULT 1
);

-- 重複送信防止のためのユニークインデックス
CREATE UNIQUE INDEX idx_trial_follow_emails_unique
ON trial_follow_emails(trial_request_id, template_id);

-- インデックス
CREATE INDEX idx_trial_follow_emails_trial_request_id ON trial_follow_emails(trial_request_id);
CREATE INDEX idx_trial_follow_emails_template_id ON trial_follow_emails(template_id);
CREATE INDEX idx_trial_follow_emails_sent_at ON trial_follow_emails(sent_at);

-- コメント
COMMENT ON TABLE trial_follow_emails IS 'トライアルフォローメール送信履歴';
COMMENT ON COLUMN trial_follow_emails.follow_email_id IS '履歴ID（主キー）';
COMMENT ON COLUMN trial_follow_emails.trial_request_id IS 'リクエストID';
COMMENT ON COLUMN trial_follow_emails.template_id IS 'テンプレートID';
COMMENT ON COLUMN trial_follow_emails.sent_at IS '送信日時';
COMMENT ON COLUMN trial_follow_emails.status IS '送信ステータス（1:成功, 0:失敗）';
