-- 搭乗者傷害危険担保特約条項


-- トリガーの削除
DROP TRIGGER IF EXISTS passenger_injury_coverage_clauses_updated_at ON passenger_injury_coverage_clauses;


-- テーブルの削除
DROP TABLE IF EXISTS passenger_injury_coverage_clauses CASCADE;


-- テーブル作成
CREATE TABLE passenger_injury_coverage_clauses (
    id SERIAL PRIMARY KEY, -- 主キー
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, -- 作成日時
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, -- 更新日時
    last_updater VARCHAR(255),           -- 最終更新者    
    capacity INTEGER NOT NULL, -- 定員
    insured_amount_per_person INTEGER NOT NULL, -- 1名あたりの保険金額
    insured_amount_per_accident INTEGER NOT NULL, -- 1事故あたりの保険金額_限度額
    premium INTEGER NOT NULL, -- 保険料
    year INTEGER -- 年度
);


--関数の作成（update_timestamp）
CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$ 
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;  -- 更新日時を現在時刻に設定
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;


-- トリガーの作成（updated_at の自動更新）
CREATE TRIGGER passenger_injury_coverage_clauses_updated_at
BEFORE UPDATE ON passenger_injury_coverage_clauses
FOR EACH ROW
EXECUTE PROCEDURE update_timestamp();


-- 初期データ挿入
INSERT INTO passenger_injury_coverage_clauses (
    capacity,
    insured_amount_per_person,
    insured_amount_per_accident,
    premium,
    year
) VALUES
(1, 10000000, 10000000, 5900, 2024),
(1, 20000000, 20000000, 11800, 2024),
(1, 30000000, 30000000, 17700, 2024),
(2, 10000000, 20000000, 10000, 2024),
(2, 20000000, 40000000, 20000, 2024),
(2, 30000000, 60000000, 30000, 2024),
(3, 10000000, 30000000, 12900, 2024),
(3, 20000000, 60000000, 25800, 2024),
(3, 30000000, 90000000, 38700, 2024),
(4, 10000000, 40000000, 14900, 2024),
(4, 20000000, 80000000, 29800, 2024),
(4, 30000000, 120000000, 44700, 2024),
(5, 10000000, 50000000, 16300, 2024),
(5, 20000000, 100000000, 32600, 2024),
(5, 30000000, 150000000, 48900, 2024),
(6, 10000000, 60000000, 17300, 2024),
(6, 20000000, 120000000, 34600, 2024),
(6, 30000000, 180000000, 51900, 2024),
(7, 10000000, 70000000, 17800, 2024),
(7, 20000000, 140000000, 35600, 2024),
(7, 30000000, 210000000, 53400, 2024),
(8, 10000000, 80000000, 18300, 2024),
(8, 20000000, 160000000, 36600, 2024),
(8, 30000000, 240000000, 54900, 2024),
(9, 10000000, 90000000, 18800, 2024),
(9, 20000000, 180000000, 37600, 2024),
(9, 30000000, 270000000, 56400, 2024),
(10, 10000000, 100000000, 19300, 2024),
(10, 20000000, 200000000, 38600, 2024),
(10, 30000000, 300000000, 57900, 2024),
(11, 10000000, 110000000, 19800, 2024),
(11, 20000000, 220000000, 39600, 2024),
(11, 30000000, 330000000, 59400, 2024),
(12, 10000000, 120000000, 20300, 2024),
(12, 20000000, 240000000, 40600, 2024),
(12, 30000000, 360000000, 60900, 2024),
(13, 10000000, 130000000, 20800, 2024),
(13, 20000000, 260000000, 41600, 2024),
(13, 30000000, 390000000, 62400, 2024),
(14, 10000000, 140000000, 21300, 2024),
(14, 20000000, 280000000, 42600, 2024),
(14, 30000000, 420000000, 63900, 2024),
(15, 10000000, 150000000, 21800, 2024),
(15, 20000000, 300000000, 43600, 2024),
(15, 30000000, 450000000, 65400, 2024),
(16, 10000000, 160000000, 22300, 2024),
(16, 20000000, 320000000, 44600, 2024),
(16, 30000000, 480000000, 66900, 2024),
(17, 10000000, 170000000, 22800, 2024),
(17, 20000000, 340000000, 45600, 2024),
(17, 30000000, 510000000, 68400, 2024),
(18, 10000000, 180000000, 23300, 2024),
(18, 20000000, 360000000, 46600, 2024),
(18, 30000000, 540000000, 69900, 2024),
(19, 10000000, 190000000, 23800, 2024),
(19, 20000000, 380000000, 47600, 2024),
(19, 30000000, 570000000, 71400, 2024),
(20, 10000000, 200000000, 24300, 2024),
(20, 20000000, 400000000, 48600, 2024),
(20, 30000000, 600000000, 72900, 2024),
(21, 10000000, 210000000, 24800, 2024),
(21, 20000000, 420000000, 49600, 2024),
(21, 30000000, 630000000, 74400, 2024),
(22, 10000000, 220000000, 25300, 2024),
(22, 20000000, 440000000, 50600, 2024),
(22, 30000000, 660000000, 75900, 2024),
(23, 10000000, 230000000, 25800, 2024),
(23, 20000000, 460000000, 51600, 2024),
(23, 30000000, 690000000, 77400, 2024),
(24, 10000000, 240000000, 26300, 2024),
(24, 20000000, 480000000, 52600, 2024),
(24, 30000000, 720000000, 78900, 2024),
(25, 10000000, 250000000, 26800, 2024),
(25, 20000000, 500000000, 53600, 2024),
(25, 30000000, 750000000, 80400, 2024),
(26, 10000000, 260000000, 27300, 2024),
(26, 20000000, 520000000, 54600, 2024),
(26, 30000000, 780000000, 81900, 2024),
(27, 10000000, 270000000, 27800, 2024),
(27, 20000000, 540000000, 55600, 2024),
(27, 30000000, 810000000, 83400, 2024),
(28, 10000000, 280000000, 28300, 2024),
(28, 20000000, 560000000, 56600, 2024),
(28, 30000000, 840000000, 84900, 2024),
(29, 10000000, 290000000, 28800, 2024),
(29, 20000000, 580000000, 57600, 2024),
(29, 30000000, 870000000, 86400, 2024),
(30, 10000000, 300000000, 29300, 2024),
(30, 20000000, 600000000, 58600, 2024),
(30, 30000000, 900000000, 87900, 2024),
(1, 10000000, 10000000, 5900, 2025),
(1, 20000000, 20000000, 11800, 2025),
(1, 30000000, 30000000, 17700, 2025),
(2, 10000000, 20000000, 10000, 2025),
(2, 20000000, 40000000, 20000, 2025),
(2, 30000000, 60000000, 30000, 2025),
(3, 10000000, 30000000, 12900, 2025),
(3, 20000000, 60000000, 25800, 2025),
(3, 30000000, 90000000, 38700, 2025),
(4, 10000000, 40000000, 14900, 2025),
(4, 20000000, 80000000, 29800, 2025),
(4, 30000000, 120000000, 44700, 2025),
(5, 10000000, 50000000, 16300, 2025),
(5, 20000000, 100000000, 32600, 2025),
(5, 30000000, 150000000, 48900, 2025),
(6, 10000000, 60000000, 17300, 2025),
(6, 20000000, 120000000, 34600, 2025),
(6, 30000000, 180000000, 51900, 2025),
(7, 10000000, 70000000, 17800, 2025),
(7, 20000000, 140000000, 35600, 2025),
(7, 30000000, 210000000, 53400, 2025),
(8, 10000000, 80000000, 18300, 2025),
(8, 20000000, 160000000, 36600, 2025),
(8, 30000000, 240000000, 54900, 2025),
(9, 10000000, 90000000, 18800, 2025),
(9, 20000000, 180000000, 37600, 2025),
(9, 30000000, 270000000, 56400, 2025),
(10, 10000000, 100000000, 19300, 2025),
(10, 20000000, 200000000, 38600, 2025),
(10, 30000000, 300000000, 57900, 2025),
(11, 10000000, 110000000, 19800, 2025),
(11, 20000000, 220000000, 39600, 2025),
(11, 30000000, 330000000, 59400, 2025),
(12, 10000000, 120000000, 20300, 2025),
(12, 20000000, 240000000, 40600, 2025),
(12, 30000000, 360000000, 60900, 2025),
(13, 10000000, 130000000, 20800, 2025),
(13, 20000000, 260000000, 41600, 2025),
(13, 30000000, 390000000, 62400, 2025),
(14, 10000000, 140000000, 21300, 2025),
(14, 20000000, 280000000, 42600, 2025),
(14, 30000000, 420000000, 63900, 2025),
(15, 10000000, 150000000, 21800, 2025),
(15, 20000000, 300000000, 43600, 2025),
(15, 30000000, 450000000, 65400, 2025),
(16, 10000000, 160000000, 22300, 2025),
(16, 20000000, 320000000, 44600, 2025),
(16, 30000000, 480000000, 66900, 2025),
(17, 10000000, 170000000, 22800, 2025),
(17, 20000000, 340000000, 45600, 2025),
(17, 30000000, 510000000, 68400, 2025),
(18, 10000000, 180000000, 23300, 2025),
(18, 20000000, 360000000, 46600, 2025),
(18, 30000000, 540000000, 69900, 2025),
(19, 10000000, 190000000, 23800, 2025),
(19, 20000000, 380000000, 47600, 2025),
(19, 30000000, 570000000, 71400, 2025),
(20, 10000000, 200000000, 24300, 2025),
(20, 20000000, 400000000, 48600, 2025),
(20, 30000000, 600000000, 72900, 2025),
(21, 10000000, 210000000, 24800, 2025),
(21, 20000000, 420000000, 49600, 2025),
(21, 30000000, 630000000, 74400, 2025),
(22, 10000000, 220000000, 25300, 2025),
(22, 20000000, 440000000, 50600, 2025),
(22, 30000000, 660000000, 75900, 2025),
(23, 10000000, 230000000, 25800, 2025),
(23, 20000000, 460000000, 51600, 2025),
(23, 30000000, 690000000, 77400, 2025),
(24, 10000000, 240000000, 26300, 2025),
(24, 20000000, 480000000, 52600, 2025),
(24, 30000000, 720000000, 78900, 2025),
(25, 10000000, 250000000, 26800, 2025),
(25, 20000000, 500000000, 53600, 2025),
(25, 30000000, 750000000, 80400, 2025),
(26, 10000000, 260000000, 27300, 2025),
(26, 20000000, 520000000, 54600, 2025),
(26, 30000000, 780000000, 81900, 2025),
(27, 10000000, 270000000, 27800, 2025),
(27, 20000000, 540000000, 55600, 2025),
(27, 30000000, 810000000, 83400, 2025),
(28, 10000000, 280000000, 28300, 2025),
(28, 20000000, 560000000, 56600, 2025),
(28, 30000000, 840000000, 84900, 2025),
(29, 10000000, 290000000, 28800, 2025),
(29, 20000000, 580000000, 57600, 2025),
(29, 30000000, 870000000, 86400, 2025),
(30, 10000000, 300000000, 29300, 2025),
(30, 20000000, 600000000, 58600, 2025),
(30, 30000000, 900000000, 87900, 2025);
